query help please

[mhellman]

I have a table with the following columns:
id, weight, date
(no primary key)

Objects are weighed at the start and then at the end of a test.
I need to create a query that will display the start weight and end weight correlated to each id.

Thank you in advanced for your time.

Michael

 

[rockman]

Since you will (by definition) only have 2 dates associated to any one ID, I would suggest you alter your table to be:

ID, StartWeight, StartDate, EndWeight, EndDate

Any reason why this would not work? I do not believe that this violates Normalization rules (and will be alot easier to manage).

HTH,
Jeff

 

[mhellman]

I agree that this should be done. I do not have the privlages to change the table nor do I know if the tests weigh the items multiple times or not. Is there anyway to do this query without changing the table?

 

[Brianwarnock]

quick thoughts are that you will need 5 queries

qr1 ID groupby date min to find start of test (by the way date is a bad field name)
qry2 ID Groupby date max to find end of test

qr3 joins qry1 and the table on ID and date to find start weight
qr4 joins qry2 and the table ditto to find end weight
qr5 joins qry3 qry4 on ID to produce report

Does it work

Brian