quote:

ORIGINAL: Sean McGrath

Ok, this is a bit of a niche topic.

I use the CT webquery to link to my Access database for custom reporting. I am debating changing my system of notes entry, and am looking for thoughts on Note entry from others who use reporting in Excel or Access for their CT wines.

Currently I use Tasting Notes, Wishlist Wines Notes, Consumed Notes and Private Notes. I concatenate them all in various reports, getting around one-to-many relationship issues by using Last or Max or something to just take one note. Not ideal, of course, as I'd like all of my TNs on the report. However, concatenating multiple TNs in a report that lists wines by Bin is, I think, beyond my SQL skills.

I am thinking of simplifying by copying all notes into the Private Note, a giant field that would mean double entry but eliminate all of my programming and concatenation issues. Before I do so, I am interested whether others have alternative suggestions.

Cheers,
Sean

p.s. and before you tell me that I am overthinking this, you should see my reports. My "Inventory quantity x kind x vintage," "Inventory x kind vs. Target and vs. Order trigger," and "Availability with NVs and Blanks fixed" would have you green with envy.

quote:

ORIGINAL: BenG

Are you concatenating the four columns or all of the rows for a wine? If by column you could create a new column (perhaps "Master Note") and populate it with the concatenation.

If by rows, depending on what the maximum number of rows for a single wine is, you could partition by the wine, assign row numbers to each row for that wine (ordering by date), then explicitly concatenate the rows - "case when RowNumber = 1 then MasterNote else "" end & case when RowNumber = 2 then MasterNote else "" end ..." etc. You might be able to create a stored procedure to loop through the rows, but I wouldn't know how to do that in Access (haven't used it for a few years).

quote:

To each their own, of course.

quote:

ORIGINAL: Blue Shorts

I'd be interested to see some of the Access queries that you use.

quote:

ORIGINAL: RedRedMoreRed

So comforting to know that I'm not the only spreadsheet geek on here.

quote:

Sure, I don't get many chances to share them.

quote:

ORIGINAL: Blue Shorts

quote:

Sure, I don't get many chances to share them.

Nice. Where are you pulling the "kind" category from?

quote:

ORIGINAL: mclancy10006

I find this fascinating, obsessive, cool, and way beyond my level of insanity. I wish you all luck with this level of planning and note keeping. I'll stick to my more chaos engineering approach to acquisition. :)

-Mark

quote:

ORIGINAL: mclancy10006

I find this fascinating, obsessive, cool, and way beyond my level of insanity. I wish you all luck with this level of planning and note keeping. I'll stick to my more chaos engineering approach to acquisition. :)

-Mark

quote:

ORIGINAL: BenG

If by rows, depending on what the maximum number of rows for a single wine is, you could partition by the wine, assign row numbers to each row for that wine (ordering by date), then explicitly concatenate the rows - "case when RowNumber = 1 then MasterNote else "" end & case when RowNumber = 2 then MasterNote else "" end ..." etc. You might be able to create a stored procedure to loop through the rows, but I wouldn't know how to do that in Access (haven't used it for a few years).

SELECT [Tasting Notes].TastingNotes, [Tasting Notes].TastingDate, [Tasting Notes].iWine, DCount("*","Tasting Notes","iWine = " & [iWine] & " And Int(Date()-[TastingDate]) > " & Int(Date()-[TastingDate]))+1 AS NumNotes, [Tasting Notes].Rating, IIf([NumNotes]=1,[TastingNotes],"") AS Note1, IIf([NumNotes]=2,[TastingNotes],"") AS Note2, IIf([NumNotes]=3,[TastingNotes],"") AS Note3, IIf([NumNotes]=4,[TastingNotes],"") AS Note4, IIf([NumNotes]=5,[TastingNotes],"") AS Note5, IIf([NumNotes]=6,[TastingNotes],"") AS Note6, IIf([NumNotes]=1,[Rating],"") AS Rating1, IIf([NumNotes]=2,[Rating],"") AS Rating2, IIf([NumNotes]=3,[Rating],"") AS Rating3, IIf([NumNotes]=4,[Rating],"") AS Rating4, IIf([NumNotes]=5,[Rating],"") AS Rating5, IIf([NumNotes]=6,[Rating],"") AS Rating6, IIf([NumNotes]=1,[TastingDate],"") AS TasteDate1, IIf([NumNotes]=2,[TastingDate],"") AS TasteDate2, IIf([NumNotes]=3,[TastingDate],"") AS TasteDate3, IIf([NumNotes]=4,[TastingDate],"") AS TasteDate4, IIf([NumNotes]=5,[TastingDate],"") AS TasteDate5, IIf([NumNotes]=6,[TastingDate],"") AS TasteDate6
FROM [Tasting Notes]
WHERE ((([Tasting Notes].iWine)>0))
ORDER BY [Tasting Notes].TastingNotes DESC , [Tasting Notes].TastingDate, [Tasting Notes].iWine;

SELECT [My Notes].iWine, Max([My Notes].NumNotes) AS NumNotes, Max([My Notes].Note1) AS Note1, Max([My Notes].Note2) AS Note2, Max([My Notes].Note3) AS Note3, Max([My Notes].Note4) AS Note4, Max([My Notes].Note5) AS Note5, Max([My Notes].Note6) AS Note6, Max([My Notes].Rating1) AS Rating1, Max([My Notes].Rating2) AS Rating2, Max([My Notes].Rating3) AS Rating3, Max([My Notes].Rating4) AS Rating4, Max([My Notes].Rating5) AS Rating5, Max([My Notes].Rating6) AS Rating6, Max([My Notes].TasteDate1) AS TasteDate1, Max([My Notes].TasteDate2) AS TasteDate2, Max([My Notes].TasteDate3) AS TasteDate3, Max([My Notes].TasteDate4) AS TasteDate4, Max([My Notes].TasteDate5) AS TasteDate5, Max([My Notes].TasteDate6) AS TasteDate6, "[" & [TasteDate1] & " " & [Rating1] & " " & [Note1] & "]" & IIf([NumNotes]>=2,"[" & [TasteDate2] & " " & [Rating2] & " " & [Note2] & "]","") & IIf([NumNotes]>=3,"[" & [TasteDate3] & " " & [Rating3] & " " & [Note3] & "]","") & IIf([NumNotes]>=4,"[" & [TasteDate4] & " " & [Rating4] & " " & [Note4] & "]","") & IIf([NumNotes]>=5,"[" & [TasteDate5] & " " & [Rating5] & " " & [Note5] & "]","") & IIf([NumNotes]>=6,"[" & [TasteDate6] & " " & [Rating6] & " " & [Note6] & "]","") AS MyNote
FROM [My Notes]
GROUP BY [My Notes].iWine;