Return Field Name from a table when dates match.

[tragatz]

First time question here. please be patient with me. I have a report that has a rolling calendar as column headers. first day of month to end of month left to right. each row represents a unique "Ship". My table rows are also "Ships" (i.e. Ship1, Ship2) which have dates when events occur. The Field Name in the Table represents the event. (i.e. Started, Inspected, Sold). My goal is to create a formula within the report row that returns the Field Name when dates match. For Example, in my report, Ship 1 shows a Sold date of 10/2/14. I would like for my report row for Ship 1 to return the Field Name "Sold" in the column 10/2/14. I hope I have explained this well enough, and followed the rules for the forum. Thank you in advance. Tom

[ranman256]

This sounds like a CROSSTAB query is what you need. Tho I am unclear on your layout...make a query to get the data you need,
then use THAT query to build a crosstab query USING THE WIZARD.
(instead of SUM or AVg for the VALUE part of the wizard, you will want FIRST)

[tragatz]

Thank you. I wondered if I was approaching it incorrectly. I will look at the CROSSTAB now.

[tragatz]

report with dates. each row is a "Ship". The "OTD_PLAN" is an example of what I would like to see. Table. as shown, the header is what I would like to see returned. OTD_PLAN has a 10/2/14 date, so I would want it to show OTD_PLAN on 10/2/14 in the report. I have cut off the ship names for privacy, but it is easy to imagine ship1, ship2, and so on for each row. When an event happens, it is entered in the table as a date in the column that coincides with the event. (OTD_PLAN is an event). I am not sure how to make a crosstab work for this. I would be happy with "It is impossible", then I would quit trying. not sure I would believe it though! Thanks.

[June7]

Your data structure is not normalized. Normalized structure could easily be pivoted with CROSSTAB. However, building a stable report to run perpetually based on CROSSTAB is not easy because of the dynamic nature of CROSSTAB, especially when pivoting on date values. If there are no records for a date then the CROSSTAB would not generate a column for that date and the column names continually change.

There's almost always a way - question is, is it worth the effort? 'Pivoting' this data would probably require VBA code and a 'temp' table.

[tragatz]

I had hoped for something like a vlookup, or dlookup formula in the report that would match the ships, and dates, and return the header name. I have had no luck with our experts here, so I knew it was a long shot. thanks.

[June7]

Aside from a CROSSTAB, field names in Access query object cannot be dynamic.

[tragatz]

Thank you. that is probably the answer I am looking for. I will seek a different route. thank you for taking the time to reply.