Need help with a complex data set

[DawnTreader]

Hello All

I havent posted in a while since the microsoft groups went offline. :
( I really prefered those groups to the microsoft answers forums, but
i am now stuck with getting my help from elsewhere. just like
microsoft to screw up a good thing.

anyways. on to the reason i post.

i have a set of data that is proving a problem to wrangle. i have a
table where i store a customer order number COID and a DateRequested
record. what this table is meant to do is to record when a request was
made to ship an order. this works well. users input the data. not a
problem.

i have another set of data that stores the information around the
actual act of shipping the orders. this data is stored in 2 tables.
packlist and packlist lines.

i have yet another set of data that stores the invoice information.
this data is stored in 2 tables as well. invoice and invoice lines.

there is a third set of data that i dont believe i need to make part
of this query but it relates to the actual customer order. this is
also stored in 2 tables. customer order and customer order lines. at
one point i had the customer orders in there, but in an attempt to get
better results i removed it.

what we are trying to get out of all this information is the amount of
time from a request to ship an order and the time it actually ships.

so here is the current SQL:

SELECT
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID,
dbo_RECEIVABLE_LINE.PACKLIST_ID,
Sum([dbo_RECEIVABLE_LINE]![AMOUNT]*[dbo_RECEIVABLE]![SELL_RATE]) AS
Invoiced_Amount,
Year([INVOICE_DATE]) AS [Year Invoiced],
tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE.INVOICE_DATE,
DateDiff("d",[DateRequested],[SHIPPED_DATE]) AS DaysDiff,
DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE]) AS DaysDiffStI
FROM
((tblOrderRequests
LEFT JOIN ((dbo_RECEIVABLE_LINE LEFT JOIN dbo_RECEIVABLE
ON dbo_RECEIVABLE_LINE.INVOICE_ID = dbo_RECEIVABLE.INVOICE_ID)
LEFT JOIN qryAftermarketSalesPersonnel
ON dbo_RECEIVABLE.SALESREP_ID =
qryAftermarketSalesPersonnel.VisualOrderName)
ON tblOrderRequests.VisualOrderID =
dbo_RECEIVABLE_LINE.CUST_ORDER_ID)
LEFT JOIN dbo_SHIPPER_LINE
ON (dbo_RECEIVABLE_LINE.PACKLIST_ID =
dbo_SHIPPER_LINE.PACKLIST_ID)
AND (dbo_RECEIVABLE_LINE.PACKLIST_LINE_NO =
dbo_SHIPPER_LINE.LINE_NO))
LEFT JOIN dbo_SHIPPER
ON dbo_SHIPPER_LINE.PACKLIST_ID = dbo_SHIPPER.PACKLIST_ID
WHERE
(((Year([SHIPPED_DATE]))=Year(Date()))
AND ((tblOrderRequests.VisualOrderID)="91237"))
OR (((Year([SHIPPED_DATE]))=Year(Date()))
AND ((tblOrderRequests.VisualOrderID)="91237")
AND ((qryAftermarketSalesPersonnel.AftermarketSales)=True))
GROUP BY
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID,
dbo_RECEIVABLE_LINE.PACKLIST_ID,
tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE.INVOICE_DATE,
DateDiff("d",[DateRequested],[SHIPPED_DATE]),
DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE])
ORDER BY
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID;

note that for the moment i am trying to get just one order to turn out
the way i want it, then i would remove the 91237 criteria and run it
on everything.

here is the resulting data:

COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/21 2010/06/15 2010/Jun/15
-6 0
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0
91237 34621 34315 $946.81 2010/06/14 2010/06/22 2010/Jun/22
8 0


i have shorted the field names for clarity in a list format, hopefully
that sticks when i actually post.

notice that because there were 2 shipments and 2 requests per the one
order i get multiples that are untrue. it should look like this:

COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0

is there a way to achieve this? no matter what i do i get multiples
per each request and shipment. i was thinking that if i could use the
request date as criteria i could get a between date critera and use
that to match up the first request with the first shipment and the
second request with the second shipment. but then how do i tell the
criteria to take the first date against the shipped date?

if anyone can help please do.

as always any and all help appreciated.

 

[Jeff Boyce]

If you are JOINing tables (and your SQL says you are), and there are any
one-to-many relationships involved (and it sounds like there are), then I'd
EXPECT to see multiple records (i.e., some portions repeat so that the
record can "pick up" the joined portion of data.

The solution is to not expect a query to behave like a report. Use your
query as the data source for a report. In the report, use GroupBy to add
Group Header (?and Footer) sections and stick the repeating portions at the
Group level.

Another option would be to use the property in the report that hides
duplicate values for fields that contain duplicates.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hello All

I havent posted in a while since the microsoft groups went offline. :
( I really prefered those groups to the microsoft answers forums, but
i am now stuck with getting my help from elsewhere. just like
microsoft to screw up a good thing.

anyways. on to the reason i post.

i have a set of data that is proving a problem to wrangle. i have a
table where i store a customer order number COID and a DateRequested
record. what this table is meant to do is to record when a request was
made to ship an order. this works well. users input the data. not a
problem.

i have another set of data that stores the information around the
actual act of shipping the orders. this data is stored in 2 tables.
packlist and packlist lines.

i have yet another set of data that stores the invoice information.
this data is stored in 2 tables as well. invoice and invoice lines.

there is a third set of data that i dont believe i need to make part
of this query but it relates to the actual customer order. this is
also stored in 2 tables. customer order and customer order lines. at
one point i had the customer orders in there, but in an attempt to get
better results i removed it.

what we are trying to get out of all this information is the amount of
time from a request to ship an order and the time it actually ships.

so here is the current SQL:

SELECT
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID,
dbo_RECEIVABLE_LINE.PACKLIST_ID,
Sum([dbo_RECEIVABLE_LINE]![AMOUNT]*[dbo_RECEIVABLE]![SELL_RATE]) AS
Invoiced_Amount,
Year([INVOICE_DATE]) AS [Year Invoiced],
tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE.INVOICE_DATE,
DateDiff("d",[DateRequested],[SHIPPED_DATE]) AS DaysDiff,
DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE]) AS DaysDiffStI
FROM
((tblOrderRequests
LEFT JOIN ((dbo_RECEIVABLE_LINE LEFT JOIN dbo_RECEIVABLE
ON dbo_RECEIVABLE_LINE.INVOICE_ID = dbo_RECEIVABLE.INVOICE_ID)
LEFT JOIN qryAftermarketSalesPersonnel
ON dbo_RECEIVABLE.SALESREP_ID =
qryAftermarketSalesPersonnel.VisualOrderName)
ON tblOrderRequests.VisualOrderID =
dbo_RECEIVABLE_LINE.CUST_ORDER_ID)
LEFT JOIN dbo_SHIPPER_LINE
ON (dbo_RECEIVABLE_LINE.PACKLIST_ID =
dbo_SHIPPER_LINE.PACKLIST_ID)
AND (dbo_RECEIVABLE_LINE.PACKLIST_LINE_NO =
dbo_SHIPPER_LINE.LINE_NO))
LEFT JOIN dbo_SHIPPER
ON dbo_SHIPPER_LINE.PACKLIST_ID = dbo_SHIPPER.PACKLIST_ID
WHERE
(((Year([SHIPPED_DATE]))=Year(Date()))
AND ((tblOrderRequests.VisualOrderID)="91237"))
OR (((Year([SHIPPED_DATE]))=Year(Date()))
AND ((tblOrderRequests.VisualOrderID)="91237")
AND ((qryAftermarketSalesPersonnel.AftermarketSales)=True))
GROUP BY
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID,
dbo_RECEIVABLE_LINE.PACKLIST_ID,
tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE.INVOICE_DATE,
DateDiff("d",[DateRequested],[SHIPPED_DATE]),
DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE])
ORDER BY
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID;

note that for the moment i am trying to get just one order to turn out
the way i want it, then i would remove the 91237 criteria and run it
on everything.

here is the resulting data:

COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/21 2010/06/15 2010/Jun/15
-6 0
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0
91237 34621 34315 $946.81 2010/06/14 2010/06/22 2010/Jun/22
8 0


i have shorted the field names for clarity in a list format, hopefully
that sticks when i actually post.

notice that because there were 2 shipments and 2 requests per the one
order i get multiples that are untrue. it should look like this:

COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0

is there a way to achieve this? no matter what i do i get multiples
per each request and shipment. i was thinking that if i could use the
request date as criteria i could get a between date critera and use
that to match up the first request with the first shipment and the
second request with the second shipment. but then how do i tell the
criteria to take the first date against the shipped date?

if anyone can help please do.

as always any and all help appreciated.

 

[DawnTreader]

If you are JOINing tables (and your SQL says you are), and there are any
one-to-many relationships involved (and it sounds like there are), then I'd
EXPECT to see multiple records (i.e., some portions repeat so that the
record can "pick up" the joined portion of data.

The solution is to not expect a query to behave like a report.  Use your
query as the data source for a report.  In the report, use GroupBy to add
Group Header (?and Footer) sections and stick the repeating portions at the
Group level.

Another option would be to use the property in the report that hides
duplicate values for fields that contain duplicates.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hello All

I havent posted in a while since the microsoft groups went offline. :
(  I really prefered those groups to the microsoft answers forums, but
i am now stuck with getting my help from elsewhere. just like
microsoft to screw up a good thing.

anyways. on to the reason i post.

i have a set of data that is proving a problem to wrangle. i have a
table where i store a customer order number COID and a DateRequested
record. what this table is meant to do is to record when a request was
made to ship an order. this works well. users input the data. not a
problem.

i have another set of data that stores the information around the
actual act of shipping the orders. this data is stored in 2 tables.
packlist and packlist lines.

i have yet another set of data that stores the invoice information.
this data is stored in 2 tables as well. invoice and invoice lines.

there is a third set of data that i dont believe i need to make part
of this query but it relates to the actual customer order. this is
also stored in 2 tables. customer order and customer order lines. at
one point i had the customer orders in there, but in an attempt to get
better results i removed it.

what we are trying to get out of all this information is the amount of
time from a request to ship an order and the time it actually ships.

so here is the current SQL:

SELECT
  tblOrderRequests.VisualOrderID,
  dbo_RECEIVABLE_LINE.INVOICE_ID,
  dbo_RECEIVABLE_LINE.PACKLIST_ID,
  Sum([dbo_RECEIVABLE_LINE]![AMOUNT]*[dbo_RECEIVABLE]![SELL_RATE]) AS
Invoiced_Amount,
  Year([INVOICE_DATE]) AS [Year Invoiced],
  tblOrderRequests.DateRequested,
  dbo_SHIPPER.SHIPPED_DATE,
  dbo_RECEIVABLE.INVOICE_DATE,
  DateDiff("d",[DateRequested],[SHIPPED_DATE]) AS DaysDiff,
  DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE]) AS DaysDiffStI
FROM
  ((tblOrderRequests
  LEFT JOIN ((dbo_RECEIVABLE_LINE LEFT JOIN dbo_RECEIVABLE
  ON dbo_RECEIVABLE_LINE.INVOICE_ID = dbo_RECEIVABLE.INVOICE_ID)
  LEFT JOIN qryAftermarketSalesPersonnel
  ON dbo_RECEIVABLE.SALESREP_ID =
qryAftermarketSalesPersonnel.VisualOrderName)
  ON tblOrderRequests.VisualOrderID =
dbo_RECEIVABLE_LINE.CUST_ORDER_ID)
  LEFT JOIN dbo_SHIPPER_LINE
  ON (dbo_RECEIVABLE_LINE.PACKLIST_ID =
dbo_SHIPPER_LINE.PACKLIST_ID)
  AND (dbo_RECEIVABLE_LINE.PACKLIST_LINE_NO =
dbo_SHIPPER_LINE.LINE_NO))
  LEFT JOIN dbo_SHIPPER
  ON dbo_SHIPPER_LINE.PACKLIST_ID = dbo_SHIPPER.PACKLIST_ID
WHERE
  (((Year([SHIPPED_DATE]))=Year(Date()))
  AND ((tblOrderRequests.VisualOrderID)="91237"))
  OR (((Year([SHIPPED_DATE]))=Year(Date()))
  AND ((tblOrderRequests.VisualOrderID)="91237")
  AND ((qryAftermarketSalesPersonnel.AftermarketSales)=True))
GROUP BY
  tblOrderRequests.VisualOrderID,
  dbo_RECEIVABLE_LINE.INVOICE_ID,
  dbo_RECEIVABLE_LINE.PACKLIST_ID,
  tblOrderRequests.DateRequested,
  dbo_SHIPPER.SHIPPED_DATE,
  dbo_RECEIVABLE.INVOICE_DATE,
  DateDiff("d",[DateRequested],[SHIPPED_DATE]),
  DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE])
ORDER BY
  tblOrderRequests.VisualOrderID,
  dbo_RECEIVABLE_LINE.INVOICE_ID;

note that for the moment i am trying to get just one order to turn out
the way i want it, then i would remove the 91237 criteria and run it
on everything.

here is the resulting data:

COID InvID PackID Amount DateRequest   ShipDATE InvDATE
DaysDiff   DaysDiffStI
91237 34561 34271 $107.37 2010/06/21      2010/06/15 2010/Jun/15
-6     0
91237 34561 34271 $107.37 2010/06/14      2010/06/15 2010/Jun/15
1     0
91237 34621 34315 $946.81 2010/06/21      2010/06/22 2010/Jun/22
1     0
91237 34621 34315 $946.81 2010/06/14      2010/06/22 2010/Jun/22
8     0

i have shorted the field names for clarity in a list format, hopefully
that sticks when i actually post.

notice that because there were 2 shipments and 2 requests per the one
order i get multiples that are untrue. it should look like this:

COID InvID PackID Amount DateRequest   ShipDATE InvDATE
DaysDiff   DaysDiffStI
91237 34561 34271 $107.37 2010/06/14      2010/06/15 2010/Jun/15
1     0
91237 34621 34315 $946.81 2010/06/21      2010/06/22 2010/Jun/22
1     0

is there a way to achieve this? no matter what i do i get multiples
per each request and shipment. i was thinking that if i could use the
request date as criteria i could get a between date critera and use
that to match up the first request with the first shipment and the
second request with the second shipment. but then how do i tell the
criteria to take the first date against the shipped date?

if anyone can help please do.

as always any and all help appreciated.- Hide quoted text -

- Show quoted text -

Hello Jeff and thanks for posting and trying to help!

the problem is i need to cause it not to duplicate where reality says
otherwise.

if a request is made to ship and order and the order ships partial the
next day then that first request was fullfilled. a few days later
another request is made to ship more of the order, the rest of the
order ships. this creates a situation where i get 4 records instead of
2.

i know why and i understand it. each time an order shows up in the
requested table it releates to all the records in the shipped lines
table. therefore i end up with 2 records per each line in the shipped
lines table. so say i have 2 requests and 3 items in the shipped
lines. 6 results because each request can be fulfilled by each line.
makes total sense. i understand the logic.

i have actually managed to get 1 line to be removed by putting a new
piece of criteria on the query. the requested date must be less than
the shipped date. this manages to get rid of one line because the
second request date is larger than the first ship date. however
because the first request is smaller than both ship dates i still get
3 lines. so... is there something that someone can think of that i can
do to get rid of the first request being put against the second ship
date?

if only there was a way to use a request date once...

is there any SQL anyone knows that might help?

 

[Jeff Boyce]

I guess I didn't understand the underlying business need...

Are you saying that you MUST eliminate the duplication in a query? If so,
why?

What is it that a query does that a report doesn't, in your situation?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

If you are JOINing tables (and your SQL says you are), and there are any
one-to-many relationships involved (and it sounds like there are), then
I'd
EXPECT to see multiple records (i.e., some portions repeat so that the
record can "pick up" the joined portion of data.

The solution is to not expect a query to behave like a report. Use your
query as the data source for a report. In the report, use GroupBy to add
Group Header (?and Footer) sections and stick the repeating portions at
the
Group level.

Another option would be to use the property in the report that hides
duplicate values for fields that contain duplicates.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hello All

I havent posted in a while since the microsoft groups went offline. :
( I really prefered those groups to the microsoft answers forums, but
i am now stuck with getting my help from elsewhere. just like
microsoft to screw up a good thing.

anyways. on to the reason i post.

i have a set of data that is proving a problem to wrangle. i have a
table where i store a customer order number COID and a DateRequested
record. what this table is meant to do is to record when a request was
made to ship an order. this works well. users input the data. not a
problem.

i have another set of data that stores the information around the
actual act of shipping the orders. this data is stored in 2 tables.
packlist and packlist lines.

i have yet another set of data that stores the invoice information.
this data is stored in 2 tables as well. invoice and invoice lines.

there is a third set of data that i dont believe i need to make part
of this query but it relates to the actual customer order. this is
also stored in 2 tables. customer order and customer order lines. at
one point i had the customer orders in there, but in an attempt to get
better results i removed it.

what we are trying to get out of all this information is the amount of
time from a request to ship an order and the time it actually ships.

so here is the current SQL:

SELECT
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID,
dbo_RECEIVABLE_LINE.PACKLIST_ID,
Sum([dbo_RECEIVABLE_LINE]![AMOUNT]*[dbo_RECEIVABLE]![SELL_RATE]) AS
Invoiced_Amount,
Year([INVOICE_DATE]) AS [Year Invoiced],
tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE.INVOICE_DATE,
DateDiff("d",[DateRequested],[SHIPPED_DATE]) AS DaysDiff,
DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE]) AS DaysDiffStI
FROM
((tblOrderRequests
LEFT JOIN ((dbo_RECEIVABLE_LINE LEFT JOIN dbo_RECEIVABLE
ON dbo_RECEIVABLE_LINE.INVOICE_ID = dbo_RECEIVABLE.INVOICE_ID)
LEFT JOIN qryAftermarketSalesPersonnel
ON dbo_RECEIVABLE.SALESREP_ID =
qryAftermarketSalesPersonnel.VisualOrderName)
ON tblOrderRequests.VisualOrderID =
dbo_RECEIVABLE_LINE.CUST_ORDER_ID)
LEFT JOIN dbo_SHIPPER_LINE
ON (dbo_RECEIVABLE_LINE.PACKLIST_ID =
dbo_SHIPPER_LINE.PACKLIST_ID)
AND (dbo_RECEIVABLE_LINE.PACKLIST_LINE_NO =
dbo_SHIPPER_LINE.LINE_NO))
LEFT JOIN dbo_SHIPPER
ON dbo_SHIPPER_LINE.PACKLIST_ID = dbo_SHIPPER.PACKLIST_ID
WHERE
(((Year([SHIPPED_DATE]))=Year(Date()))
AND ((tblOrderRequests.VisualOrderID)="91237"))
OR (((Year([SHIPPED_DATE]))=Year(Date()))
AND ((tblOrderRequests.VisualOrderID)="91237")
AND ((qryAftermarketSalesPersonnel.AftermarketSales)=True))
GROUP BY
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID,
dbo_RECEIVABLE_LINE.PACKLIST_ID,
tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE.INVOICE_DATE,
DateDiff("d",[DateRequested],[SHIPPED_DATE]),
DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE])
ORDER BY
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID;

note that for the moment i am trying to get just one order to turn out
the way i want it, then i would remove the 91237 criteria and run it
on everything.

here is the resulting data:

COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/21 2010/06/15 2010/Jun/15
-6 0
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0
91237 34621 34315 $946.81 2010/06/14 2010/06/22 2010/Jun/22
8 0

i have shorted the field names for clarity in a list format, hopefully
that sticks when i actually post.

notice that because there were 2 shipments and 2 requests per the one
order i get multiples that are untrue. it should look like this:

COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0

is there a way to achieve this? no matter what i do i get multiples
per each request and shipment. i was thinking that if i could use the
request date as criteria i could get a between date critera and use
that to match up the first request with the first shipment and the
second request with the second shipment. but then how do i tell the
criteria to take the first date against the shipped date?

if anyone can help please do.

as always any and all help appreciated.- Hide quoted text -

- Show quoted text -

Hello Jeff and thanks for posting and trying to help!

the problem is i need to cause it not to duplicate where reality says
otherwise.

if a request is made to ship and order and the order ships partial the
next day then that first request was fullfilled. a few days later
another request is made to ship more of the order, the rest of the
order ships. this creates a situation where i get 4 records instead of
2.

i know why and i understand it. each time an order shows up in the
requested table it releates to all the records in the shipped lines
table. therefore i end up with 2 records per each line in the shipped
lines table. so say i have 2 requests and 3 items in the shipped
lines. 6 results because each request can be fulfilled by each line.
makes total sense. i understand the logic.

i have actually managed to get 1 line to be removed by putting a new
piece of criteria on the query. the requested date must be less than
the shipped date. this manages to get rid of one line because the
second request date is larger than the first ship date. however
because the first request is smaller than both ship dates i still get
3 lines. so... is there something that someone can think of that i can
do to get rid of the first request being put against the second ship
date?

if only there was a way to use a request date once...

is there any SQL anyone knows that might help?

 

[Bob Barrows]

the problem is i need to cause it not to duplicate where reality says
otherwise.

if a request is made to ship and order and the order ships partial the
next day then that first request was fullfilled. a few days later
another request is made to ship more of the order, the rest of the
order ships. this creates a situation where i get 4 records instead of
2.

i know why and i understand it. each time an order shows up in the
requested table it releates to all the records in the shipped lines
table. therefore i end up with 2 records per each line in the shipped
lines table. so say i have 2 requests and 3 items in the shipped
lines. 6 results because each request can be fulfilled by each line.
makes total sense. i understand the logic.

i have actually managed to get 1 line to be removed by putting a new
piece of criteria on the query. the requested date must be less than
the shipped date. this manages to get rid of one line because the
second request date is larger than the first ship date. however
because the first request is smaller than both ship dates i still get
3 lines. so... is there something that someone can think of that i can
do to get rid of the first request being put against the second ship
date?

if only there was a way to use a request date once...

is there any SQL anyone knows that might help?

You don't need a report to use GROUP BY.
I would create a saved query that groups the requests so that only a
single record per request is returned. Then use the saved query in your
join instead of the requests table.

 

[DawnTreader]

I guess I didn't understand the underlying business need...

Are you saying that you MUST eliminate the duplication in a query?  If so,
why?

What is it that a query does that a report doesn't, in your situation?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

If you are JOINing tables (and your SQL says you are), and there are any
one-to-many relationships involved (and it sounds like there are), then
I'd
EXPECT to see multiple records (i.e., some portions repeat so that the
record can "pick up" the joined portion of data.

The solution is to not expect a query to behave like a report. Use your
query as the data source for a report. In the report, use GroupBy to add
Group Header (?and Footer) sections and stick the repeating portions at
the
Group level.

Another option would be to use the property in the report that hides
duplicate values for fields that contain duplicates.

Good luck!

Jeff Boyce
Microsoft Access MVP

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

news:[email protected]...

Hello All
I havent posted in a while since the microsoft groups went offline. :
( I really prefered those groups to the microsoft answers forums, but
i am now stuck with getting my help from elsewhere. just like
microsoft to screw up a good thing.
anyways. on to the reason i post.
i have a set of data that is proving a problem to wrangle. i have a
table where i store a customer order number COID and a DateRequested
record. what this table is meant to do is to record when a request was
made to ship an order. this works well. users input the data. not a
problem.
i have another set of data that stores the information around the
actual act of shipping the orders. this data is stored in 2 tables.
packlist and packlist lines.
i have yet another set of data that stores the invoice information.
this data is stored in 2 tables as well. invoice and invoice lines.
there is a third set of data that i dont believe i need to make part
of this query but it relates to the actual customer order. this is
also stored in 2 tables. customer order and customer order lines. at
one point i had the customer orders in there, but in an attempt to get
better results i removed it.
what we are trying to get out of all this information is the amount of
time from a request to ship an order and the time it actually ships.
so here is the current SQL:
SELECT
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID,
dbo_RECEIVABLE_LINE.PACKLIST_ID,
Sum([dbo_RECEIVABLE_LINE]![AMOUNT]*[dbo_RECEIVABLE]![SELL_RATE]) AS
Invoiced_Amount,
Year([INVOICE_DATE]) AS [Year Invoiced],
tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE.INVOICE_DATE,
DateDiff("d",[DateRequested],[SHIPPED_DATE]) AS DaysDiff,
DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE]) AS DaysDiffStI
FROM
((tblOrderRequests
LEFT JOIN ((dbo_RECEIVABLE_LINE LEFT JOIN dbo_RECEIVABLE
ON dbo_RECEIVABLE_LINE.INVOICE_ID = dbo_RECEIVABLE.INVOICE_ID)
LEFT JOIN qryAftermarketSalesPersonnel
ON dbo_RECEIVABLE.SALESREP_ID =
qryAftermarketSalesPersonnel.VisualOrderName)
ON tblOrderRequests.VisualOrderID =
dbo_RECEIVABLE_LINE.CUST_ORDER_ID)
LEFT JOIN dbo_SHIPPER_LINE
ON (dbo_RECEIVABLE_LINE.PACKLIST_ID =
dbo_SHIPPER_LINE.PACKLIST_ID)
AND (dbo_RECEIVABLE_LINE.PACKLIST_LINE_NO =
dbo_SHIPPER_LINE.LINE_NO))
LEFT JOIN dbo_SHIPPER
ON dbo_SHIPPER_LINE.PACKLIST_ID = dbo_SHIPPER.PACKLIST_ID
WHERE
(((Year([SHIPPED_DATE]))=Year(Date()))
AND ((tblOrderRequests.VisualOrderID)="91237"))
OR (((Year([SHIPPED_DATE]))=Year(Date()))
AND ((tblOrderRequests.VisualOrderID)="91237")
AND ((qryAftermarketSalesPersonnel.AftermarketSales)=True))
GROUP BY
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID,
dbo_RECEIVABLE_LINE.PACKLIST_ID,
tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE.INVOICE_DATE,
DateDiff("d",[DateRequested],[SHIPPED_DATE]),
DateDiff("d",[SHIPPED_DATE],[INVOICE_DATE])
ORDER BY
tblOrderRequests.VisualOrderID,
dbo_RECEIVABLE_LINE.INVOICE_ID;
note that for the moment i am trying to get just one order to turn out
the way i want it, then i would remove the 91237 criteria and run it
on everything.
here is the resulting data:
COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/21 2010/06/15 2010/Jun/15
-6 0
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0
91237 34621 34315 $946.81 2010/06/14 2010/06/22 2010/Jun/22
8 0
i have shorted the field names for clarity in a list format, hopefully
that sticks when i actually post.
notice that because there were 2 shipments and 2 requests per the one
order i get multiples that are untrue. it should look like this:
COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0
is there a way to achieve this? no matter what i do i get multiples
per each request and shipment. i was thinking that if i could use the
request date as criteria i could get a between date critera and use
that to match up the first request with the first shipment and the
second request with the second shipment. but then how do i tell the
criteria to take the first date against the shipped date?
if anyone can help please do.
as always any and all help appreciated.- Hide quoted text -

- Show quoted text -

Hello Jeff and thanks for posting and trying to help!

the problem is i need to cause it not to duplicate where reality says
otherwise.

if a request is made to ship and order and the order ships partial the
next day then that first request was fullfilled. a few days later
another request is made to ship more of the order, the rest of the
order ships. this creates a situation where i get 4 records instead of
2.

i know why and i understand it. each time an order shows up in the
requested table it releates to all the records in the shipped lines
table. therefore i end up with 2 records per each line in the shipped
lines table. so say i have 2 requests and 3 items in the shipped
lines. 6 results because each request can be fulfilled by each line.
makes total sense. i understand the logic.

i have actually managed to get 1 line to be removed by putting a new
piece of criteria on the query. the requested date must be less than
the shipped date. this manages to get rid of one line because the
second request date is larger than the first ship date. however
because the first request is smaller than both ship dates i still get
3 lines. so... is there something that someone can think of that i can
do to get rid of the first request being put against the second ship
date?

if only there was a way to use a request date once...

is there any SQL anyone knows that might help?- Hide quoted text -

- Show quoted text -

"Are you saying that you MUST eliminate the duplication in a query?
If so, why? What is it that a query does that a report doesn't, in
your situation?"

yes. the query is a source for an excel spreadsheet with a pivot
table. this wont be turned into a report.

 

[DawnTreader]

You don't need a report to use GROUP BY.
I would create a saved query that groups the requests so that only a
single record per request is returned. Then use the saved query in your
join instead of the requests table.

--
HTH,
Bob Barrows- Hide quoted text -

- Show quoted text -

so you would get a situation where there is 2 requests per an order to
be one line by putting both dates on the same line? i need to have
each request, but i need the first request to be "discarded" by the
query after it has been used to match the first shipment. understand
that i need to see the response time between a request and the first
time a shipment is made after that date. then i need to see the
response time between the second request and the next shipment.

we are trying to determine where bottlenecks occur in our shipping
process. this is part of determining that.

 

[Bob Barrows]

so you would get a situation where there is 2 requests per an order to
be one line by putting both dates on the same line?

Err ... no - grouping does not do that - grouping guarantees that there is
one line per request. All fields not being grouped must be aggregated (sum,
min, max, avg, etc)

i need to have
each request, but i need the first request to be "discarded" by the
query after it has been used to match the first shipment. understand
that i need to see the response time between a request and the first
time a shipment is made after that date. then i need to see the
response time between the second request and the next shipment.

we are trying to determine where bottlenecks occur in our shipping
process. this is part of determining that.

Time to go back to your original post ... you did include sample data and
desired results, I hope ....

No. I really need to see the data in its original form. Without that, all I
can do is guess. One guess would be:
Create a saved query to retrieve the earliest shipment request per customer
order (?).
Create a second saved query to retrieve the las shipment date.
Join the two queries so you can subtract one date from the other.

 

[DawnTreader]

Err ... no - grouping does not do that - grouping guarantees that there is
one line per request. All fields not being grouped must be aggregated (sum,
min, max, avg, etc)



Time to go back to your original post ... you did include sample data and
desired results, I hope ....

No. I really need to see the data in its original form. Without that, allI
can do is guess. One guess would be:
Create a saved query to retrieve the earliest shipment request per customer
order (?).
Create a second saved query to retrieve the las shipment date.
Join the two queries so you can subtract one date from the other.

ok, so i am going to post original unqueried data. this will be a
simple copy paste from each table with only data relating to the same
records involved. i have had to truncate field names.

First is the Request Table:

ID DateRequested VisualOrderID
115 28-May-10 90691
63 14-May-10 90691
19 05-May-10 90691
243 19-Jul-10 90714
220 15-Jul-10 90714
20 05-May-10 90714
49 11-May-10 90733

the Order Table:

ROWID ID ODATE
30915 90733 01-Feb-10
30896 90714 27-Jan-10
30873 90691 21-Jan-10

the Order Lines Table:

ROWID COID LINE PARTID LASTSHIPPEDDATE
112333 90691 1 321127 10-May-10
112334 90691 2 321128 01-Jun-10
112335 90691 3 321149 19-Apr-10
112336 90691 4 321148 19-Apr-10
112337 90691 5 321572 19-Apr-10
112338 90691 6 321571 19-Apr-10
112339 90691 7 305284 19-Apr-10
112340 90691 8 305283 19-Apr-10
112341 90691 9 305286 17-May-10
112342 90691 10 307713 19-Apr-10
112343 90691 11 200825 19-Apr-10
112344 90691 12 201095 19-Apr-10
112345 90691 13 305634 19-Apr-10
112346 90691 14 305623 19-Apr-10
112347 90691 15 317111 19-Apr-10
112348 90691 16 305572 19-Apr-10
112349 90691 17 305407 19-Apr-10
112350 90691 18 317021 19-Apr-10
112351 90691 19 305572 19-Apr-10
112352 90691 20 305552 19-Apr-10
112353 90691 21 305563 19-Apr-10
112354 90691 22 305552 19-Apr-10
112355 90691 23 305407 19-Apr-10
112356 90691 24 305428 19-Apr-10
112357 90691 25 305579 19-Apr-10
112358 90691 26 305552 19-Apr-10
112359 90691 27 305563 19-Apr-10
112360 90691 28 305514 19-Apr-10
112361 90691 29 305663 19-Apr-10
112362 90691 30 305603 19-Apr-10
112363 90691 31 305432 19-Apr-10
112364 90691 32 306657 19-Apr-10
112365 90691 33 324320 19-Apr-10
112366 90691 34 318256 19-Apr-10
112367 90691 35 310983 17-May-10
112368 90691 36 306485 19-Apr-10
112465 90714 1 305235 19-Apr-10
112466 90714 2 319648 19-Apr-10
112467 90714 3 319649 19-Apr-10
112468 90714 4 320861 21-Jul-10
112469 90714 5 305572 19-Apr-10
112470 90714 6 317111 19-Apr-10
112471 90714 7 200987 19-Apr-10
112472 90714 8 200988 19-Apr-10
112473 90714 9 200825 19-Apr-10
112474 90714 10 324055 19-Apr-10
112475 90714 11 324396 19-Apr-10
112476 90714 12 307728 19-Apr-10
112477 90714 13 309034 19-Apr-10
112478 90714 14 307901 19-Apr-10
112479 90714 15 307727 19-Apr-10
112480 90714 16 307880 19-Apr-10
112481 90714 17 307788 19-Apr-10
112482 90714 18 305285 10-May-10
112483 90714 19 305283 19-Apr-10
112484 90714 20 305286 21-Jul-10
112485 90714 21 305597 19-Apr-10
112486 90714 22 305428 10-May-10
112487 90714 23 305560 10-May-10
112488 90714 24 317021 19-Apr-10
112489 90714 25 305634 19-Apr-10
112490 90714 26 305623 19-Apr-10
112491 90714 27 310983 14-Jun-10
112492 90714 28 321555 19-Apr-10
112493 90714 29 321556 19-Apr-10
112494 90714 30 321721 14-Jun-10
112495 90714 31 201095 10-May-10
112496 90714 32 201096 19-Apr-10
112497 90714 33 201268 19-Apr-10
112498 90714 34 306485 19-Apr-10
112499 90714 35 306657 19-Apr-10
112500 90714 36 307904 10-May-10
112501 90714 37 305291 19-Apr-10
112502 90714 38 307776 19-Apr-10
112503 90714 39 307777 19-Apr-10
112504 90714 40 202649 19-Apr-10
112505 90714 41 202650 19-Apr-10
112506 90714 42 310983 19-Apr-10
112507 90714 43 324055 19-Apr-10
112508 90714 44 324396 19-Apr-10
112587 90733 1 320943 20-May-10
112622 90733 2 202203 20-May-10
112601 90733 3 319451 20-May-10
112605 90733 4 312699 20-May-10
112607 90733 5 312037 20-May-10
112606 90733 6 312038 20-May-10
112623 90733 7 201181 20-May-10
112624 90733 8 201179 20-May-10
112627 90733 9 20-May-10

the Packlist Table:

ROWID PACKLISTID COID SHIPPEDDATE
35414 33942 90691 19-Apr-10
35525 34045 90691 10-May-10
35556 34074 90691 17-May-10
35668 34181 90691 01-Jun-10
35415 33943 90714 19-Apr-10
35523 34043 90714 10-May-10
35751 34261 90714 14-Jun-10
35980 34486 90714 21-Jul-10
36064 34559 90714
35582 34101 90733 20-May-10

the Packlist Line Table:

ROWID PACKLISTID LINE COID COLINE
118903 33942 1 90691 1
119670 34045 1 90691 1
118904 33942 2 90691 2
119671 34045 2 90691 2
119944 34074 1 90691 2
120836 34181 1 90691 2
118905 33942 3 90691 3
118906 33942 4 90691 4
118907 33942 5 90691 5
118908 33942 6 90691 6
118909 33942 7 90691 7
118910 33942 8 90691 8
118911 33942 9 90691 9
119672 34045 3 90691 9
119945 34074 2 90691 9
118912 33942 10 90691 10
118913 33942 11 90691 11
118914 33942 12 90691 12
118915 33942 13 90691 13
118916 33942 14 90691 14
118917 33942 15 90691 15
118918 33942 16 90691 16
118919 33942 17 90691 17
118920 33942 18 90691 18
118921 33942 19 90691 19
118922 33942 20 90691 20
118923 33942 21 90691 21
118924 33942 22 90691 22
118925 33942 23 90691 23
118926 33942 24 90691 24
118927 33942 25 90691 25
118928 33942 26 90691 26
118929 33942 27 90691 27
118930 33942 28 90691 28
118931 33942 29 90691 29
118932 33942 30 90691 30
118933 33942 31 90691 31
118934 33942 32 90691 32
118935 33942 33 90691 33
118936 33942 34 90691 34
118937 33942 35 90691 35
119673 34045 4 90691 35
119946 34074 3 90691 35
118938 33942 36 90691 36
118939 33943 1 90714 1
118940 33943 2 90714 2
118941 33943 3 90714 3
118942 33943 4 90714 4
119655 34043 1 90714 4
121219 34261 1 90714 4
122539 34486 1 90714 4
118943 33943 5 90714 5
118944 33943 6 90714 6
118945 33943 7 90714 7
118946 33943 8 90714 8
118947 33943 9 90714 9
118948 33943 10 90714 10
118949 33943 11 90714 11
118950 33943 12 90714 12
118951 33943 13 90714 13
118952 33943 14 90714 14
118953 33943 15 90714 15
118954 33943 16 90714 16
118955 33943 17 90714 17
118956 33943 18 90714 18
119656 34043 2 90714 18
121220 34261 2 90714 18
122540 34486 2 90714 18
118957 33943 19 90714 19
118958 33943 20 90714 20
119657 34043 3 90714 20
121221 34261 3 90714 20
122541 34486 3 90714 20
118959 33943 21 90714 21
118960 33943 22 90714 22
119658 34043 4 90714 22
118961 33943 23 90714 23
119659 34043 5 90714 23
118962 33943 24 90714 24
118963 33943 25 90714 25
118964 33943 26 90714 26
118965 33943 27 90714 27
119660 34043 6 90714 27
121222 34261 4 90714 27
118966 33943 28 90714 28
118967 33943 29 90714 29
118968 33943 30 90714 30
119661 34043 7 90714 30
121223 34261 5 90714 30
118969 33943 31 90714 31
119662 34043 8 90714 31
118970 33943 32 90714 32
118971 33943 33 90714 33
118972 33943 34 90714 34
118973 33943 35 90714 35
118974 33943 36 90714 36
119663 34043 9 90714 36
118975 33943 37 90714 37
119664 34043 10 90714 37
121224 34261 6 90714 37
122542 34486 4 90714 37
118976 33943 38 90714 38
118977 33943 39 90714 39
118978 33943 40 90714 40
118979 33943 41 90714 41
118980 33943 42 90714 42
118981 33943 43 90714 43
118982 33943 44 90714 44
120218 34101 1 90733 1
120219 34101 2 90733 2
120220 34101 3 90733 3
120221 34101 4 90733 4
120222 34101 5 90733 5
120223 34101 6 90733 6
120224 34101 7 90733 7
120225 34101 8 90733 8
120226 34101 9 90733 9

the Invoice table:

ROWID INVOICEID CUSTOMERID INVOICEDATE
36224 34185 29246 19-Apr-10
36225 34186 29246 19-Apr-10
36344 34302 29246 10-May-10
36346 34304 29246 10-May-10
36379 34337 29246 17-May-10
36413 34371 29250 20-May-10
36513 34471 29246 01-Jun-10
36595 34551 29246 14-Jun-10

and finally the Invoice Line Table:

ROWID INVOICEID LINE COID COLINE PACKLISTID PACKLISTLINE
169093 34185 1 90691 1 33942 1
169094 34185 2 90691 2 33942 2
169095 34185 3 90691 3 33942 3
169096 34185 4 90691 4 33942 4
169097 34185 5 90691 5 33942 5
169098 34185 6 90691 6 33942 6
169099 34185 7 90691 7 33942 7
169100 34185 8 90691 8 33942 8
169101 34185 9 90691 9 33942 9
169102 34185 10 90691 10 33942 10
169103 34185 11 90691 11 33942 11
169104 34185 12 90691 12 33942 12
169105 34185 13 90691 13 33942 13
169106 34185 14 90691 14 33942 14
169107 34185 15 90691 15 33942 15
169108 34185 16 90691 16 33942 16
169109 34185 17 90691 17 33942 17
169110 34185 18 90691 18 33942 18
169111 34185 19 90691 19 33942 19
169112 34185 20 90691 20 33942 20
169113 34185 21 90691 21 33942 21
169114 34185 22 90691 22 33942 22
169115 34185 23 90691 23 33942 23
169116 34185 24 90691 24 33942 24
169117 34185 25 90691 25 33942 25
169118 34185 26 90691 26 33942 26
169119 34185 27 90691 27 33942 27
169120 34185 28 90691 28 33942 28
169121 34185 29 90691 29 33942 29
169122 34185 30 90691 30 33942 30
169123 34185 31 90691 31 33942 31
169124 34185 32 90691 32 33942 32
169125 34185 33 90691 33 33942 33
169126 34185 34 90691 34 33942 34
169127 34185 35 90691 35 33942 35
169128 34185 36 90691 36 33942 36
169129 34186 1 90714 1 33943 1
169130 34186 2 90714 2 33943 2
169131 34186 3 90714 3 33943 3
169132 34186 4 90714 4 33943 4
169133 34186 5 90714 5 33943 5
169134 34186 6 90714 6 33943 6
169135 34186 7 90714 7 33943 7
169136 34186 8 90714 8 33943 8
169137 34186 9 90714 9 33943 9
169138 34186 10 90714 10 33943 10
169139 34186 11 90714 11 33943 11
169140 34186 12 90714 12 33943 12
169141 34186 13 90714 13 33943 13
169142 34186 14 90714 14 33943 14
169143 34186 15 90714 15 33943 15
169144 34186 16 90714 16 33943 16
169145 34186 17 90714 17 33943 17
169146 34186 18 90714 18 33943 18
169147 34186 19 90714 19 33943 19
169148 34186 20 90714 20 33943 20
169149 34186 21 90714 21 33943 21
169150 34186 22 90714 22 33943 22
169151 34186 23 90714 23 33943 23
169152 34186 24 90714 24 33943 24
169153 34186 25 90714 25 33943 25
169154 34186 26 90714 26 33943 26
169155 34186 27 90714 27 33943 27
169156 34186 28 90714 28 33943 28
169157 34186 29 90714 29 33943 29
169158 34186 30 90714 30 33943 30
169159 34186 31 90714 31 33943 31
169160 34186 32 90714 32 33943 32
169161 34186 33 90714 33 33943 33
169162 34186 34 90714 34 33943 34
169163 34186 35 90714 35 33943 35
169164 34186 36 90714 36 33943 36
169165 34186 37 90714 37 33943 37
169166 34186 38 90714 38 33943 38
169167 34186 39 90714 39 33943 39
169168 34186 40 90714 40 33943 40
169169 34186 41 90714 41 33943 41
169170 34186 42 90714 42 33943 42
169171 34186 43 90714 43 33943 43
169172 34186 44 90714 44 33943 44
169971 34302 1 90714 4 34043 1
169972 34302 2 90714 18 34043 2
169973 34302 3 90714 20 34043 3
169974 34302 4 90714 22 34043 4
169975 34302 5 90714 23 34043 5
169976 34302 6 90714 27 34043 6
169977 34302 7 90714 30 34043 7
169978 34302 8 90714 31 34043 8
169979 34302 9 90714 36 34043 9
169980 34302 10 90714 37 34043 10
169986 34304 1 90691 1 34045 1
169987 34304 2 90691 2 34045 2
169988 34304 3 90691 9 34045 3
169989 34304 4 90691 35 34045 4
170268 34337 1 90691 2 34074 1
170269 34337 2 90691 9 34074 2
170270 34337 3 90691 35 34074 3
170554 34371 1 90733 1 34101 1
170555 34371 2 90733 2 34101 2
170556 34371 3 90733 3 34101 3
170557 34371 4 90733 4 34101 4
170558 34371 5 90733 5 34101 5
170559 34371 6 90733 6 34101 6
170560 34371 7 90733 7 34101 7
170561 34371 8 90733 8 34101 8
170562 34371 9 90733 9 34101 9
171255 34471 1 90691 2 34181 1
171638 34551 1 90714 4 34261 1
171639 34551 2 90714 18 34261 2
171640 34551 3 90714 20 34261 3
171641 34551 4 90714 27 34261 4
171642 34551 5 90714 30 34261 5
171643 34551 6 90714 37 34261 6

i left out a lot of fields. but you can see from this the "trail" of
data. an order is made, that order when a line ships a qty creates a
packlist. the packlist is then turned into an invoice. the request
table is used to make a note of when we request the shipping
department to ship an order. sometimes we can make more than one
request. when there is more than one request i need to have only the
results that show when after each request an order was shipped. so
back in my first post you can see the results i want, but that isnt
the results i am getting.

i have broken this into multiple queries, tried to layer queries to
manage the data, but nothing i have tried has eliminated the first
requested ship date being used by every time the order was shipped and
vise versa. if there are multiple requests or shipments i end up with
a load of duplicates.

 

[Bob Barrows]

ok, so i am going to post original unqueried data. this will be a
simple copy paste from each table with only data relating to the same
records involved. i have had to truncate field names.

First is the Request Table:

I'm working on importing this data in an Access database. I presume that
this information from your first post represents the desired results so I
will move ahead on that assumption:

COID InvID PackID Amount DateRequest ShipDATE InvDATE
DaysDiff DaysDiffStI
91237 34561 34271 $107.37 2010/06/14 2010/06/15 2010/Jun/15
1 0
91237 34621 34315 $946.81 2010/06/21 2010/06/22 2010/Jun/22
1 0

 

[Bob Barrows]

i left out a lot of fields. but you can see from this the "trail" of
data. an order is made, that order when a line ships a qty creates a
packlist. the packlist is then turned into an invoice. the request
table is used to make a note of when we request the shipping
department to ship an order. sometimes we can make more than one
request. when there is more than one request i need to have only the
results that show when after each request an order was shipped. so
back in my first post you can see the results i want, but that isnt
the results i am getting.

All right, I have the tables imported and I've created the relationships so
I can analyze it visually. You did not include the information for the order
in your original post so I will not be able to be sure of my results.

Do I have this correctly? Packing lists are created without requests being
entered in the tblOrderRequests? Is that why there is no way to relate a
specific Request ID to a Packing List ID?
There is a 1-to-many relationship between orders and packing lists, so that
means multiple packing lists can be created for an order, correct?
Is it only one invoice per packing list?
The order ID dows not appear in tblInvoices, so that means an invoice can
encompass multiple orders, correct?
Neither does packinglist appear in tblInvoices ... does this mean that the
same invoice can be used for multiple packing lists?
I presume the amount shipped appears in the packing list? Oh wait, I just
looked at the query you posted in your first reply and it appears you left
out the Receivables table. I guess it's not relevant...?

I'm going to attempt a solution (ignoring the amount) without getting the
answers to these questions. If my solution turns out to be incorrect, I will
need answers and more details.

So this is my first try (you will not be able to switch this to Design View
because it involves a non-equi join):
SELECT r.DateRequested, r.VisualOrderID, Min(p.SHIPPEDDATE) AS
MinOfSHIPPEDDATE, Max(p.SHIPPEDDATE) AS MaxOfSHIPPEDDATE
FROM tblOrderRequests AS r INNER JOIN tblPacklists AS p ON (r.VisualOrderID
= p.COID) AND (r.DateRequested <= p.SHIPPEDDATE)
WHERE p.SHIPPEDDATE < Nz((SELECT min(DateRequested) FROM tblOrderRequests
WHERE VisualOrderID=r.VisualOrderID AND DateRequested > r.DateRequested),
p.SHIPPEDdate+1)
GROUP BY r.DateRequested, r.VisualOrderID;

These are the results I get:
Query2 DateRequested VisualOrderID MinOfSHIPPEDDATE MaxOfSHIPPEDDATE
5/5/2010 90691 5/10/2010 5/10/2010
5/14/2010 90691 5/17/2010 5/17/2010
5/28/2010 90691 6/1/2010 6/1/2010
5/5/2010 90714 5/10/2010 6/14/2010
7/19/2010 90714 7/21/2010 7/21/2010
5/11/2010 90733 5/20/2010 5/20/2010


Notice that there are multiple shipments for order 90714 after the request
that was made on 5/5/2010 and before the next request that resulted in
shipments: the one that was entered on 7/19 (the request entered on 7/15 is
ignored by my query since no shipments resulted from it).

If you actually want the 7/19 request ignored, and use the 7/15 request as
basis of your datediff calculations, you need to do this:

SELECT Min(q.DateRequested) AS MinRequestDate, q.VisualOrderID,
q.MinOfSHIPPEDDATE, q.MaxOfSHIPPEDDATE
FROM [SELECT r.DateRequested, r.VisualOrderID, Min(p.SHIPPEDDATE) AS
MinOfSHIPPEDDATE, Max(p.SHIPPEDDATE) AS MaxOfSHIPPEDDATE
FROM tblOrderRequests AS r INNER JOIN tblPacklists AS p ON (r.VisualOrderID
= p.COID) AND (r.DateRequested <= p.SHIPPEDDATE)
GROUP BY r.VisualOrderID, r.DateRequested]. AS q
GROUP BY q.VisualOrderID, q.MinOfSHIPPEDDATE, q.MaxOfSHIPPEDDATE
ORDER BY q.VisualOrderID,Min(q.DateRequested);

I believe this should get you started. You will need to decide which of the
shipment dates to use in your datediff calculations: the min or the max
shipment date. You should be able create another query that you can join to
whichever of these is relevant, again using a non-equi join to group the
results by request.

 

[DawnTreader]

i left out a lot of fields. but you can see from this the "trail" of
data. an order is made, that order when a line ships a qty creates a
packlist. the packlist is then turned into an invoice. the request
table is used to make a note of when we request the shipping
department to ship an order. sometimes we can make more than one
request. when there is more than one request i need to have only the
results that show when after each request an order was shipped. so
back in my first post you can see the results i want, but that isnt
the results i am getting.

All right, I have the tables imported and I've created the relationships so
I can analyze it visually. You did not include the information for the order
in your original post so I will not be able to be sure of my results.

Do I have this correctly? Packing lists are created without requests being
entered in the tblOrderRequests? Is that why there is no way to relate a
specific Request ID to a Packing List ID?
There is a 1-to-many relationship between orders and packing lists, so that
means multiple packing lists can be created for an order, correct?
Is it only one invoice per packing list?
The order ID dows not appear in tblInvoices, so that means an invoice can
encompass multiple orders, correct?
Neither does packinglist appear in tblInvoices ... does this mean that the
same invoice can be used for multiple packing lists?
I presume the amount shipped appears in the packing list? Oh wait, I just
looked at the query you posted in your  first reply and it appears you left
out the Receivables table. I guess it's not relevant...?

I'm going to attempt a solution (ignoring the amount) without getting the
answers to these questions. If my solution turns out to be incorrect, I will
need answers and more details.

So  this is my first try (you will not be able to switch this to DesignView
because it involves a non-equi join):
SELECT  r.DateRequested, r.VisualOrderID, Min(p.SHIPPEDDATE) AS
MinOfSHIPPEDDATE, Max(p.SHIPPEDDATE) AS MaxOfSHIPPEDDATE
FROM tblOrderRequests AS r INNER JOIN tblPacklists AS p ON (r.VisualOrderID
= p.COID) AND (r.DateRequested <= p.SHIPPEDDATE)
WHERE  p.SHIPPEDDATE < Nz((SELECT min(DateRequested) FROM tblOrderRequests
WHERE VisualOrderID=r.VisualOrderID AND  DateRequested >  r.DateRequested),
p.SHIPPEDdate+1)
GROUP BY r.DateRequested, r.VisualOrderID;

These are the results I get:
  Query2 DateRequested VisualOrderID MinOfSHIPPEDDATE MaxOfSHIPPEDDATE
      5/5/2010 90691 5/10/2010 5/10/2010
      5/14/2010 90691 5/17/2010 5/17/2010
      5/28/2010 90691 6/1/2010 6/1/2010
      5/5/2010 90714 5/10/2010 6/14/2010
      7/19/2010 90714 7/21/2010 7/21/2010
      5/11/2010 90733 5/20/2010 5/20/2010

Notice that there are multiple shipments for order 90714 after the request
that was made on 5/5/2010 and before the next request that resulted in
shipments: the one that was entered on 7/19 (the request entered on 7/15 is
ignored by my query since no shipments resulted from it).

If you actually want the 7/19 request ignored, and use the 7/15 request as
basis of your datediff calculations, you need to do this:

SELECT Min(q.DateRequested) AS MinRequestDate, q.VisualOrderID,
q.MinOfSHIPPEDDATE, q.MaxOfSHIPPEDDATE
FROM [SELECT  r.DateRequested, r.VisualOrderID, Min(p.SHIPPEDDATE) AS
MinOfSHIPPEDDATE, Max(p.SHIPPEDDATE) AS MaxOfSHIPPEDDATE
FROM tblOrderRequests AS r INNER JOIN tblPacklists AS p ON (r.VisualOrderID
= p.COID) AND (r.DateRequested <= p.SHIPPEDDATE)
GROUP BY r.VisualOrderID, r.DateRequested]. AS q
GROUP BY q.VisualOrderID, q.MinOfSHIPPEDDATE, q.MaxOfSHIPPEDDATE
ORDER BY q.VisualOrderID,Min(q.DateRequested);

I believe this should get you started. You will need to decide which of the
shipment dates to use in your datediff calculations: the min or the max
shipment date. You should be able create another query that you can join to
whichever of these is relevant, again using a non-equi join to group the
results by request.

Sorry, relationships were something i never thought to include.

in the date requested data the VisualOrderID is = to the ID in the
order table. the order table doesnt even need to be showing you the
ROWID field, i should have cut that out.

the COID in the Order Lines Table connects each line to the order it
is part of. so Order.ID = OrderLine.COID. again i could have left out
the ROWID field in the order lines.

the PackList Table's COID matches the Order Table's ID

the PackList Table's PACKLISTID = the Packlist Line tables PACKLISTID,
Packlist.PACKLISTID = PacklistLine.PACKLISTID

the Packlist Line Table has COID and Line that match the OrderLine
COID and Line. so OrderLine.COID = PacklistLine.COID and
OrderLine.LINE = PacklistLine.COLINE

the Invoice Table is matched to the InvoiceLines by INVOICEID. so
Invoice.INVOICEID = InvoiceLine.INVOICEID

InvoiceLine can be matched to COID and COLINE or PACKLISTID and
PACKLISTLINE OR you can do both. so PacklistLine.COID =
InvoiceLine.COID and PacklistLine.COLINE = InvoiceLine.COLINE. OR
PacklistLine.PACKLISTID = InvoiceLine.PACKLISTID and
PacklistLine.PACKLISTLINE = InvoiceLine.PACKLISTLINE. OR
PacklistLine.COID = InvoiceLine.COID and PacklistLine.COLINE =
InvoiceLine.COLINE and PacklistLine.PACKLISTID =
InvoiceLine.PACKLISTID and PacklistLine.PACKLISTLINE =
InvoiceLine.PACKLISTLINE

i have used directional joins as an attempt to calm the data, but i am
still getting bad results.

here is a trimmed SQL that i have tried.

SELECT tblOrderRequests.VisualOrderID, tblOrderRequests.DateRequested,
dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.LINE_NO,
dbo_CUST_ORDER_LINE.LAST_SHIPPED_DATE, dbo_SHIPPER_LINE.PACKLIST_ID,
dbo_SHIPPER_LINE.LINE_NO, dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE_LINE.INVOICE_ID, dbo_RECEIVABLE_LINE.LINE_NO,
dbo_RECEIVABLE.INVOICE_DATE
FROM (((((tblOrderRequests INNER JOIN dbo_CUSTOMER_ORDER ON
tblOrderRequests.VisualOrderID = dbo_CUSTOMER_ORDER.ID) INNER JOIN
dbo_CUST_ORDER_LINE ON dbo_CUSTOMER_ORDER.ID =
dbo_CUST_ORDER_LINE.CUST_ORDER_ID) INNER JOIN dbo_SHIPPER_LINE ON
(dbo_CUST_ORDER_LINE.LINE_NO = dbo_SHIPPER_LINE.CUST_ORDER_LINE_NO)
AND (dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_SHIPPER_LINE.CUST_ORDER_ID)) INNER JOIN dbo_SHIPPER ON
dbo_SHIPPER_LINE.PACKLIST_ID = dbo_SHIPPER.PACKLIST_ID) INNER JOIN
dbo_RECEIVABLE_LINE ON (dbo_SHIPPER_LINE.LINE_NO =
dbo_RECEIVABLE_LINE.PACKLIST_LINE_NO) AND
(dbo_SHIPPER_LINE.PACKLIST_ID = dbo_RECEIVABLE_LINE.PACKLIST_ID) AND
(dbo_SHIPPER_LINE.CUST_ORDER_LINE_NO =
dbo_RECEIVABLE_LINE.CUST_ORDER_LINE_NO) AND
(dbo_SHIPPER_LINE.CUST_ORDER_ID = dbo_RECEIVABLE_LINE.CUST_ORDER_ID))
INNER JOIN dbo_RECEIVABLE ON dbo_RECEIVABLE_LINE.INVOICE_ID =
dbo_RECEIVABLE.INVOICE_ID
WHERE (((tblOrderRequests.VisualOrderID)="90691")) OR
(((tblOrderRequests.VisualOrderID)="90714")) OR
(((tblOrderRequests.VisualOrderID)="90733"));

this is without any directions. if i add those i get:

SELECT tblOrderRequests.VisualOrderID, tblOrderRequests.DateRequested,
dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.LINE_NO,
dbo_CUST_ORDER_LINE.LAST_SHIPPED_DATE, dbo_SHIPPER_LINE.PACKLIST_ID,
dbo_SHIPPER_LINE.LINE_NO, dbo_SHIPPER.SHIPPED_DATE,
dbo_RECEIVABLE_LINE.INVOICE_ID, dbo_RECEIVABLE_LINE.LINE_NO,
dbo_RECEIVABLE.INVOICE_DATE
FROM (((((tblOrderRequests LEFT JOIN dbo_CUSTOMER_ORDER ON
tblOrderRequests.VisualOrderID = dbo_CUSTOMER_ORDER.ID) LEFT JOIN
dbo_CUST_ORDER_LINE ON dbo_CUSTOMER_ORDER.ID =
dbo_CUST_ORDER_LINE.CUST_ORDER_ID) LEFT JOIN dbo_SHIPPER_LINE ON
(dbo_CUST_ORDER_LINE.LINE_NO = dbo_SHIPPER_LINE.CUST_ORDER_LINE_NO)
AND (dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_SHIPPER_LINE.CUST_ORDER_ID)) LEFT JOIN dbo_SHIPPER ON
dbo_SHIPPER_LINE.PACKLIST_ID = dbo_SHIPPER.PACKLIST_ID) LEFT JOIN
dbo_RECEIVABLE_LINE ON (dbo_SHIPPER_LINE.LINE_NO =
dbo_RECEIVABLE_LINE.PACKLIST_LINE_NO) AND
(dbo_SHIPPER_LINE.PACKLIST_ID = dbo_RECEIVABLE_LINE.PACKLIST_ID) AND
(dbo_SHIPPER_LINE.CUST_ORDER_LINE_NO =
dbo_RECEIVABLE_LINE.CUST_ORDER_LINE_NO) AND
(dbo_SHIPPER_LINE.CUST_ORDER_ID = dbo_RECEIVABLE_LINE.CUST_ORDER_ID))
LEFT JOIN dbo_RECEIVABLE ON dbo_RECEIVABLE_LINE.INVOICE_ID =
dbo_RECEIVABLE.INVOICE_ID
WHERE (((tblOrderRequests.VisualOrderID)="90691")) OR
(((tblOrderRequests.VisualOrderID)="90714")) OR
(((tblOrderRequests.VisualOrderID)="90733"));

but i still get the same number of records.

i really dont know what to do at this point.

i dont want any dates ignored. what i want is if a ship date is past
the second request date then it shouldnt even look at the first
request date. i will be playing with this more this week. hopefully
something will come about. i will try your SQL tomorrow sometime as it
is end of day here.

thanks for your help.

 

[DawnTreader]

i left out a lot of fields. but you can see from this the "trail" of
data. an order is made, that order when a line ships a qty creates a
packlist. the packlist is then turned into an invoice. the request
table is used to make a note of when we request the shipping
department to ship an order. sometimes we can make more than one
request. when there is more than one request i need to have only the
results that show when after each request an order was shipped. so
back in my first post you can see the results i want, but that isnt
the results i am getting.

All right, I have the tables imported and I've created the relationships so
I can analyze it visually. You did not include the information for the order
in your original post so I will not be able to be sure of my results.

Do I have this correctly? Packing lists are created without requests being
entered in the tblOrderRequests? Is that why there is no way to relate a
specific Request ID to a Packing List ID?
There is a 1-to-many relationship between orders and packing lists, so that
means multiple packing lists can be created for an order, correct?
Is it only one invoice per packing list?
The order ID dows not appear in tblInvoices, so that means an invoice can
encompass multiple orders, correct?
Neither does packinglist appear in tblInvoices ... does this mean that the
same invoice can be used for multiple packing lists?
I presume the amount shipped appears in the packing list? Oh wait, I just
looked at the query you posted in your  first reply and it appears you left
out the Receivables table. I guess it's not relevant...?

I'm going to attempt a solution (ignoring the amount) without getting the
answers to these questions. If my solution turns out to be incorrect, I will
need answers and more details.

So  this is my first try (you will not be able to switch this to DesignView
because it involves a non-equi join):
SELECT  r.DateRequested, r.VisualOrderID, Min(p.SHIPPEDDATE) AS
MinOfSHIPPEDDATE, Max(p.SHIPPEDDATE) AS MaxOfSHIPPEDDATE
FROM tblOrderRequests AS r INNER JOIN tblPacklists AS p ON (r.VisualOrderID
= p.COID) AND (r.DateRequested <= p.SHIPPEDDATE)
WHERE  p.SHIPPEDDATE < Nz((SELECT min(DateRequested) FROM tblOrderRequests
WHERE VisualOrderID=r.VisualOrderID AND  DateRequested >  r.DateRequested),
p.SHIPPEDdate+1)
GROUP BY r.DateRequested, r.VisualOrderID;

These are the results I get:
  Query2 DateRequested VisualOrderID MinOfSHIPPEDDATE MaxOfSHIPPEDDATE
      5/5/2010 90691 5/10/2010 5/10/2010
      5/14/2010 90691 5/17/2010 5/17/2010
      5/28/2010 90691 6/1/2010 6/1/2010
      5/5/2010 90714 5/10/2010 6/14/2010
      7/19/2010 90714 7/21/2010 7/21/2010
      5/11/2010 90733 5/20/2010 5/20/2010

Notice that there are multiple shipments for order 90714 after the request
that was made on 5/5/2010 and before the next request that resulted in
shipments: the one that was entered on 7/19 (the request entered on 7/15 is
ignored by my query since no shipments resulted from it).

If you actually want the 7/19 request ignored, and use the 7/15 request as
basis of your datediff calculations, you need to do this:

SELECT Min(q.DateRequested) AS MinRequestDate, q.VisualOrderID,
q.MinOfSHIPPEDDATE, q.MaxOfSHIPPEDDATE
FROM [SELECT  r.DateRequested, r.VisualOrderID, Min(p.SHIPPEDDATE) AS
MinOfSHIPPEDDATE, Max(p.SHIPPEDDATE) AS MaxOfSHIPPEDDATE
FROM tblOrderRequests AS r INNER JOIN tblPacklists AS p ON (r.VisualOrderID
= p.COID) AND (r.DateRequested <= p.SHIPPEDDATE)
GROUP BY r.VisualOrderID, r.DateRequested]. AS q
GROUP BY q.VisualOrderID, q.MinOfSHIPPEDDATE, q.MaxOfSHIPPEDDATE
ORDER BY q.VisualOrderID,Min(q.DateRequested);

I believe this should get you started. You will need to decide which of the
shipment dates to use in your datediff calculations: the min or the max
shipment date. You should be able create another query that you can join to
whichever of these is relevant, again using a non-equi join to group the
results by request.

so i looked over the situation and realized that i was trying to hard.
i still need a little help, but the data i am actually looking for is
found by the following SQL

SELECT tblOrderRequests.VisualOrderID, tblOrderRequests.DateRequested,
dbo_SHIPPER.SHIPPED_DATE, DateDiff("d",[DateRequested],[SHIPPED_DATE])
AS TimeToShip, dbo_SHIPPER.PACKLIST_ID
FROM tblOrderRequests LEFT JOIN dbo_SHIPPER ON
tblOrderRequests.VisualOrderID = dbo_SHIPPER.CUST_ORDER_ID
WHERE (((dbo_SHIPPER.SHIPPED_DATE)>=[tblOrderRequests].
[DateRequested])) OR (((dbo_SHIPPER.PACKLIST_ID) Is Null))
GROUP BY tblOrderRequests.VisualOrderID,
tblOrderRequests.DateRequested, dbo_SHIPPER.SHIPPED_DATE, DateDiff("d",
[DateRequested],[SHIPPED_DATE]), dbo_SHIPPER.PACKLIST_ID
ORDER BY tblOrderRequests.VisualOrderID DESC ,
tblOrderRequests.DateRequested DESC , dbo_SHIPPER.SHIPPED_DATE DESC;

the problem still remains that i am getting data like the following:

VisualOrderID DateRequested SHIPPED_DATE TimeToShip PACKLIST_ID
90733 2010/05/11 2010/05/20 9 34101
90714 2010/08/09 2010/08/10 1 34630
90714 2010/07/19 2010/08/10 22 34630
90714 2010/07/19 2010/08/03 15 34559
90714 2010/07/19 2010/07/21 2 34486
90714 2010/07/15 2010/08/10 26 34630
90714 2010/07/15 2010/08/03 19 34559
90714 2010/07/15 2010/07/21 6 34486
90714 2010/05/05 2010/08/10 97 34630
90714 2010/05/05 2010/08/03 90 34559
90714 2010/05/05 2010/07/21 77 34486
90714 2010/05/05 2010/06/14 40 34261
90714 2010/05/05 2010/05/10 5 34043
90691 2010/05/28 2010/06/01 4 34181
90691 2010/05/14 2010/06/01 18 34181
90691 2010/05/14 2010/05/17 3 34074
90691 2010/05/05 2010/06/01 27 34181
90691 2010/05/05 2010/05/17 12 34074
90691 2010/05/05 2010/05/10 5 34045

where what i really want is:

VisualOrderID DateRequested SHIPPED_DATE TimeToShip PACKLIST_ID
90691 28-May-10 01-Jun-10 4 34181
90691 14-May-10 17-May-10 3 34074
90691 05-May-10 10-May-10 5 34045
90714 09-Aug-10 10-Aug-10 1 34630
90714 19-Jul-10 03-Aug-10 15 34559
90714 15-Jul-10 21-Jul-10 6 34486
90714 05-May-10 10-May-10 5 34043
90733 11-May-10 20-May-10 9 34101

i haven't tried implementing the non equi join yet, but now that i
have simpler SQL it is more appealing to try.