Dates Per Item

[Pharaoh]

Hi,

I have a table with 3 fields: Item, Date, Location

Each Item can have multiple locations and dates. I would like to display the data in such a way that for each row, it shows the item in column 1 and the dates associated with that item in each of the following columns.

Please could someone help me to achieve this.

This in a step in a larger plan to show the number of days between each date for each item. So if there is a way to skip the step above please could you let me know.

Thanks

 

[Galaxiom]

Check out Cross tab queries.

 

[Pharaoh]

I had a look and i wanted to show the Item as a Row heading and the Date as the colum heading but i need a value. So then i tried Item as row heading, location as colum heading and date as value but it wouldn't run as i need to make the value field either sum or count. Any other ideas?

 

[Galaxiom]

I just read the last line of the question. Sorry, I didn't realise what you were trying to achieve.

The key is to join the table to an alias of itself on Item and Location then add Item, Location and the date fields from both tables. Then limit the records to those where the one date field is the Max of the dates for that item and location prior to the other date field.

This should give you one record with start and end dates for each of the periods where an item was at a particular location.

Sorry I can be more detailed. It is 1:20AM and I need to sleep.

 

[Pharaoh]

Hi,

Thanks for that i had a go and it has got me further than before. Is there a way to show each date for the item as opposed to a max and min?