Help needed with Query/Report

[Orangearium]

Hi!

I have 2 tables joined with a "one to many" - I'm trying to design a report that returns every record in the primary table, but only the most recent record in the subsidiary table (I have both a date and serial number field that can be used to identify this).

I figure I must need to use a build query on the subsidiary table first, returning the first record for each, but I have no idea how to do this.

I'm all out of ideas - any help would be welcome.

Thanks

 

[MacCaillean]

Hi Orangearium

You need to base your report on a query which contains both of your tables.

The query should contain totals - to do this, click the Totals button (sigma symbol) on the query design toolbar.

From your primary table, add the appropriate unique field, e.g. the serial number or whatever other unique field that you need to show in your report. In the 'Total' row in the QBE grid, select Group By (it will be set to that as default anyway).

From your subsidiary table, add the field that lets you know which record is the most recent, whether that is a date field or a sequential number field. In the 'Total' row for this field, select Max.

Finally, double-click the join between the two tables and make sure you have selected the join type that says "Include ALL records from <YourPrimaryTableName> ...

Run the query and you should get what you're after.

Hope this helps

Mac

 

[PHV]

SELECT A.*, B.*
FROM ([Master table] AS A
INNER JOIN [Child table] AS B ON A.PrimaryKey = B.ForeignKey)
INNER JOIN (SELECT ForeignKey, Max[date field] AS LastDate FROM [Child table] GROUP BY ForeignKey
) AS L ON B.ForeignKey = L.ForeignKey AND B.[date field] = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Orangearium]

Thanks a lot guys -

I remembered that this was possible because I recall doing on an access course I did years ago, but you know how it is if you don't use these things.