Monthly Data Analysis

[Jon]

Hello -

I have been tasked to analyze data (on a monthly basis) and generate a
couple of reports. To save time and energy I'd like to input the data
into Access - run a couple of queries - and have Access spit out the
reports. I’ve tried reasearching how I might acheive the results, but
the SQL I entered in access didn't spit out what I thought it would.

Below is an example of the raw data (Customer's Orders) that I have to
analyze to generate the two reports (Order Summary and Itemized
List). Where Order Summary shows the total of the fruit order each
month. FYI ~ Each month I receive the Customer’s Orders (data) and
have to combine the total fruit and update the Order Summary report –
with the updated information. In the Itemized List – I need to some
how list each fruit order on its one line.

I would GREATLY appreciate anyone’s assistance.

~ Jon

Table (Customer's Orders)
Name CustomerID Oranges Apples
John Doe 1234 21 7
Jane Doe 2341 14 5
James Doe 3412 4 48
Jenny Doe 4123 2 4

Output (Order Summary)
Name CustomerID Nov-08 Dec-08
John Doe 1234 17 28
Jane Doe 2341 5 19
James Doe 3412 24 52
Jenny Doe 4123 50 6

Output (Itemized List)
Name CustomerID Type of Fruit Total
John Doe 1234 Oranges 21
John Doe 1234 Apples 7
Jane Doe 2341 Oranges 14
Jane Doe 2341 Apples 5
James Doe 3412 Oranges 4
James Doe 3412 Apples 48
Jenny Doe 4123 Oranges 2
Jenny Doe 4123 Apples 4

 

[Dale Fye]

Jon,

Need a little more information. Are you trying to tell me that your
Customer Orders table contains a name, customerID, I guess there must ge an
OrderDate in there somewhere, and then a bunch of columns indicating the
number of a particular type of fruit? This is poor database design.
Ideally, you would have a Customer Orders table that tracks the OrderID(PK),
CustomerID, OrderDate, and any fields that are specific to that order. Then
you would have an [Orders Detail] table that contains [OrderDetailID],
OrderID, ProductID, Qty.

To get from [Customer Orders] to the Itemized list, you need to create what
many call a normalizing query, which would look something like:

SELECT OrderName, CustomerID, "Oranges" as [Type of Fruit], [Oranges] as Total
FROM yourTable
UNION ALL
SELECT OrderName, CustomerID, "Apples" as [Type of Fruit], [Apples] as Total
FROM yourTable

To get from [Customer Orders] to [Order Summary] you will need to create a
Crosstab query. Add the CustomerName, CustomerID, OrderDate, and a computed
colum TotalProducts: [Oranges] & [Apples] to your query grid. Change the
query type to Crosstab, then set CustomerName and CustomerID to "GroupBy" and
"RowHeader". Change the properties of the OrderDate column to be "GroupBy"
and "ColHeader". Set the properties of the TotalProducts column to "Sum" and
"Value"

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[NTC]

first point is you need to add a date stamp as you append each record into
your master table.

second is your example data is not normalized; it should be structured:

Table (Customer's Orders)
Name CustomerID Product Amount
John D 1234 Oranges 21
John D 1234 Apples 7
Jane Doe 2341 Oranges 14
Jane Doe 2341 Apples 5
James Doe 3412 Oranges 4

etc.

I see that your Itemized List report example is in a normalized mode.

I don't know if you can control the source; obviously your example is
symbolic; but your key issue is managing your source data if it only
originates as a spread sheet left-to-right non normalized mode - this is a
problem.

If your non-normalized left-to-right columns of fruit types is always the
same count and in the same order, and not an enormous quantity - you can
normalize your data & append the date field thru a sequence of append queries
moving the data into a new master table.

Once normalized, and with a date per record appended; creating your reports
is no problem at all.