Q: Query???

[Geoff Jones]

Hi

Can anybody help me with the following?

I have table which looks something like this:

Date Quantity Sold

01/01/03 5
02/01/03 2
03/01/03 0

etc.....(for an entire year)

How could I generate another table giving the total quantities sold in a
month i.e.

Month Total Quantity Sold

Jan 53
Feb 34

etc.

Further to this, if the original table was broken down further in terms of
time i.e. one day could be broken down further into every hour of that day,
what would be the easiest was of generating a table with the total sold for
that day? Essentially this is the same problem but I'm interested to know
how it could be done.

Thanks in advance

Geoff

 

[Douglas J. Steele]

Open the query designer and select your table. Drag the Date and Quanity
Sold fields into the grid. Go to the Date field modify it to

SalesMonth: Format([DateField], "mmm, yyyy")

(replace DateField with whatever the name of the field is)

Convert the query to a Totals query (either from the View menu, or by
clicking on the Sigma icon on the tool bar). When you do that, a new line,
labelled Total, will appear, with the word Group By under all fields. Change
that to Sum under your Quantity Sold field and save the query.

If you've got times as well, and you want to summarize by day, do the same,
only modify the date field as:

SalesDate: DateValue([DateField])

(again, replace DateField with whatever the name of the field is)

Just a comment: you call the field Date in your post. I hope that's not the
actual name of your field. Date is a reserved word, and should not be used
as a field name.

 

[Denny G]

Hi Geoff,

May I ask why you want to create a second table that
gives the monthly "Quantity Sold" totals off the first
table? I do not know what you don't know about DBs and
Access, but are you aware that you can do a report off
the table you already have that gives you exactly what
you are looking for. Off the top of my head, I believe
you can set up a query and a report that will display on
the same report every month for every year with the
totals of "Quantity Sold."

Others' opinions . . .

Denny G.