What is wrong with this query?

[Guest]

Hi folks,

Please help, running out of time! I am so close, (yet so far away). My
goal is to end up with a query of all fields of the records with the earliest
delivery date for each buyer of each style, sorted by the earliest date.

Like this

style1
buyerB 11/1
BuyerA 11/28
buyerC 12/1

style3
buyerA 11/20
buyerE 11/24

style2
buyerD 12/1
buyerA 12/5
buyerC 12/20
buyerF 12/21

there are typically several orders per buyer for any given style


I have already set up cascading queries:


The 1st Query is a select to combine 2 tables, joined on style field,
containing the following fields:
qry1a contains these fields:

tbl1.tbl1_style
tbl1.styleApproved
tbl1.comments

tbl2.tbl2_style
tbl2.buyer
tbl2.delivdate
tbl2.poNo
tbl2.poDate
tbl2.color
tbl2.units


The 2nd query is a totals query to find the earliest date of delivery for
each buyer of each style
QRY1b SQL:

SELECT qry1a.tbl1_style, qry1a.buyer, Min(qry1a.delivDate) AS MinOfDelivDate
FROM qry1a
GROUP BY qry1a.tbl1_style, qry1a.buyer;


The 3rd Query
qry1c SQL:

SELECT qry1b.tbl1_style, qry1b.buyer, qry1b.MinOfDelivDate, qry1a.comments,
qry1a.styleApproved, qry1a.poDate, qry1a.poNo, qry1a.buyer, qry1a.color,
qry1a.units
FROM qry1a INNER JOIN qry1b ON (qry1a.tbl1_style = qry1b.tbl1_style) AND
(qry1a.delivDate = qry1b.MinOfDelivDate)
ORDER BY qry1b.tbl1_style, qry1b.MinOfDelivDate;

I simplified these statements for posting, and hopefully didn't botch
anything in the edit. The queries all execute ok.



Here is what i am getting:

1st query returns
379 records (all records)

2nd query returns
152 records, the amount I would like in the final query

3rd query returns
318 records

Again, my goal is to end up with a query of all fields of the records with
the earliest delivery date for each buyer of each style, sorted by the
earliest date.




I think I am almost there.
the 2nd query returns what I want, only it can't return all of the fields
for each row

the 3rd query is currently returning
318 records

Where the 3rd query is going wrong is that I was relying on the delivery
date to weed out duplicate orders for same buyer, same earring. For instance:
buyerD had 5 orders of style2 *on 12/1*. But the orders were for different
colors or units. They are probably on the same PO#, too. So, query3 is
returning all five orders because they were all put in on the same (earliest)
date.


The bottom line is, I only need one of those orders to show in the final
report, it doesn't matter for which color or for how many units.

I tried the following query, but it does not work (there could be several
things wrong with the query, as i'm not well versed in access).

SELECT DISTINCTROW r.tbl1_style, r.qry1b.buyer, r.MinDelivDate, r.comments,
r.poDate, r.poNo, r.xfR, qry1c.r.buyer, r.color, r.units, d.tbl1_style,
d.qry1b.buyer, d.MinDelivDate
FROM qry1c AS r INNER JOIN qry1c AS d ON r.tbl1_style=d.tbl1_style
WHERE (r.tbl1_style=d.tbl1_style) And (r.qry1b.buyer=d.qry1b.buyer) And
(r.MinOfDelivDate=r.MinOfDelivDate);




Any suggestions?
Thank you!

m-

 

[Gary Walter]

SELECT
qry1b.tbl1_style,
qry1b.buyer,
qry1b.MinOfDelivDate,
FIRST(qry1a.comments) As acomment,
FIRST(qry1a.styleApproved) As astyleApproved,
FIRST(qry1a.poDate) As apoDate,
FIRST(qry1a.poNo) As apoNo,
FIRST(qry1a.color) As acolor,
FIRST(qry1a.units) As aunits
FROM
qry1a
INNER JOIN
qry1b
ON
(qry1a.tbl1_style = qry1b.tbl1_style)
AND
(qry1a.delivDate = qry1b.MinOfDelivDate)
AND
(qry1a.buyer = qry1b.buyer)
GROUP BY
qry1b.tbl1_style,
qry1b.buyer,
qry1b.MinOfDelivDate
ORDER BY
qry1b.tbl1_style,
qry1b.MinOfDelivDate;

 

[Guest]

Okay, I forgot the link, sillyme!
:http://www.microsoft.com/office/com...en-us-office&lang=en&cr=US&sloc=en-us&m=1&p=1