Aggregate Query Problems - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Aggregate Query Problems

Michael Medici

3 years ago

Hi,

I posted this in the visitor forum, but now I am a member. I've looked at the videos but still have a problem here.
I have 2 tables tblquote & tblqnotes. tblquote is to list quotes/projects by quote number (unique, no dups). tblNote is to create notes, by date, for each quote record, this tbl has the quote number field in it as well.

I've created a query, pulled both tables in, enabled group by and set the notes date to max and there is a relationship between quote numbers in both tables, but when I run the query it either shows me all the quote records for each quote number and not just the latest.

	Kevin Robertson @Reply 3 years ago
You'll need to do this with 2 Queries. In your first query bring in only the Quote Number and the date field. Then aggregate. QuoteNumber (Group By); YourDateField (Max) In your second query bring in the first query and any supporting tables. Join the MaxOfDate from the first query to the date field in the Notes table. Bring in the other fields from the supporting tables.

	Michael Medici OP @Reply 3 years ago

	Michael Medici OP @Reply 3 years ago
Thanks for the reply Kevin, but still something is not right. I created the first query as you indicated and that shows fine, but when I try to create the 2nd query, No matter what I do, it does not work right. Attached is a picture and I made a simple test DB I could send if you would like.

	Kevin Robertson @Reply 3 years ago
Try changing your Join Types. There should be no need to make them Outer Joins. The screenshot below shows how I would set this up.

	Kevin Robertson @Reply 3 years ago

	Michael Medici OP @Reply 3 years ago
Nope, when I set it up like yours (keep in mind that quote number is the key field in the quote table, I get the same information repeated over and over again when I run it.

	Michael Medici OP @Reply 3 years ago

	Kevin Robertson @Reply 3 years ago
You said you built a simple test database. If you want to send it to me I'll take a look. [email protected]

	Michael Medici OP @Reply 3 years ago
Thanks Kevin. On its way

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View