Re: What is wrong with this query?
- From: "Gary Walter" <gary@xxxxxxxxxxx>
- Date: Wed, 6 Dec 2006 03:38:03 -0600
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;
"Mari" wrote:
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-
.
- References:
- What is wrong with this query?
- From: Mari
- What is wrong with this query?
- Prev by Date: Re: Update from one table to another
- Next by Date: Re: Query value between min-max and return another value
- Previous by thread: What is wrong with this query?
- Next by thread: Re: What is wrong with this query?
- Index(es):
Relevant Pages
|
