M
Mari
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-
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-