newbie queries question

  • Thread starter Thread starter patskinny
  • Start date Start date
P

patskinny

hi, I have a table with the following layout:

NameCode Date Shipments

45215 3/7/06 1
45215 3/8/06 18
37515 3/8/06 20
37515 3/9/06 85
45215 3/10/06 55


what I am trying to do is have a query that will produce a report with
the following results based on a time period selected by the user (In
this case, the user selects 3/7/06 - 3/9/06):


Name Code 3/7/06 3/8/06 3/9/06


37515 No shipments shipments shipments
45215 shipments shipments No Shipments



I can select the dates from a calendar I built on a form, can populate
the form column headings with the selected dates but getting the info
from the table isnt working.
I just want it to say "no shipments" if the date is not found for that
for the selected range.

If anyone has any ideas or can point me in the right direction please
let me know!!! I'm not a complete novice but I'm by no means an
expert.

thank you!!!
 
The trick to enable you to include data for dates which does not actually
appear in the table, in your case, namecode/dates with no shipments is to
include a Calendar table in the database. This is simply a table of all
dates over a given period, e.g. 10 years. The easiest way to create one is
to serially fill a column in Excel and import it into Access as a table. In
the following the table is called Calendar and the column in the table is
called calDate.

You can then use a UNION operation to return both the namecode/dates with
shipments and those without:

PARAMETERS[StartDate:] DateTime, [End Date:] DateTime;
SELECT calDate As DateShipped,NameCode,"Shipments" As HasShipped
FROM Calendar LEFT JOIN ShippingTable
ON Calendar.calDate = ShippingTable.Date
WHERE calDate Between [StartDate:] And [End Date:]
UNION
SELECT calDate, NameCode,"No Shipments"
FROM Calendar, ShippingTable
WHERE NameCode NOT IN
(SELECT NameCode
FROM ShippingTable
WHERE [Date] =CalDate)
AND calDate BETWEEN [StartDate:] AND [End Date:];

I've used simple parameters here, but you'd use references to controls on
your form.

You should then be able to create a crosstab query based on this query, but
when I tried it it refused to recognize the calDate column for some reason.
You might have better luck, but what I did was create a table called
ShipmentsLog from the query using a maketable query and then based the
following crosstab query on it to five the desired results:

TRANSFORM First(HasShipped) AS Shipped
SELECT NameCode
FROM ShipmentsLog
GROUP BY NameCode
PIVOT DateShipped;

You could automate this by setting up the blank ShipmentsLog table first and
then emptying and filling it with Delete and Append queries run from your
form.

Ken Sheridan
Stafford, England
 
It occurred to me after sending my last post that the Calendar table is not
actually needed in the first part of the UNION operation , only the second:

PARAMETERS [StartDate:] DateTime, [End Date:] DateTime;
SELECT [Date] As DateShipped,NameCode,"Shipments" As HasShipped
FROM ShippingTable
WHERE [Date] Between [StartDate:] And [End Date:]
UNION
SELECT calDate, NameCode,"No Shipments"
FROM Calendar, ShippingTable
WHERE NameCode NOT IN
(SELECT NameCode
FROM ShippingTable
WHERE [Date] = CalDate)
AND calDate Between [StartDate:] And [End Date:];

Ken Sheridan
Stafford, England
 
Back
Top