Access Used to Find, Tabulate and Sum Numbers by Monthly Dates

I imported your sheet into Access and then created the following query

Please Login or Register  to view this content.

BTW, I question why you have different products in different tables. The product should be a field in your table and you could run your query all at once grouped on the product.

Try this:

Please Login or Register  to view this content.

or If you don't want to include the PO Number ( I wasn't sure from your description) then try this:

Please Login or Register  to view this content.

Alan,

Never mind this as below: I have found out that writing code in another app, then doing Cut & Paste doesn't work, unless written in NotePad. This cost me a ton of time, but I am learning!

I have found that the Date fields I need to use, to run correctly by month (and year) are not in Date/Time per the Field properties. I didn't create the db, so no surprise there I suppose.
So, from what I've been reading, I need to Sort the records in custom order, so the months will run correctly timewise, vs. alphabetically.
So, looks like to get "January 2013" "February 2013" "March 2013" and "April 2013" to run, I have to set up an Advanced Filter/Sort.
So I have written this code, but I keep getting the all too common "You may have added an operand without an operator" error. I've reviewed character by character, and still can't find my error.

Here's the code line:
IIf([Doc Date By Month] = “January 2013”, 1, IIf([Doc Date By Month] = “February 2013”, 2, IIf([Doc Date By Month] = “March 2013”, 3, IIf([Doc Date By Month] = “April 2013”, 4, 5))))

The Field column I want to correct is called Doc Date By Month. What do I have wrong?
Thanks in advance.
(I am hoping you are out enjoying a perfect golfing day, here in Chicagoland today.)

Regarding the date issue: Use the CDate function in a query to change the text value January 2013 to 1/1/2013. Do this in an expression. Look at this link -->http://www.techonthenet.com/access/f...type/cdate.php

Regarding the parameter issue. You have created a query and have field names that Access cannot find in the tables you have included in your query. this usually happens because of mis spelling of field names or spaces added or not included in field names. This is Access way of telling you that you have an error that needs to be corrected.

For tutorials, I love google. Also, look at the links in my signature (the ones that reference Access), especially the one at DataPigTechnologies. I learned by searching the internet, forums like this and Virginia Anderson's Book on Access. There are many others, but I found hers to be very good.

Crystal will give you the basics. Other stuff will come to you as you try to develop new databases. I am always learning from others in this and other forums.

Ok, help has been awesome. Thank you very much!

Here's an update (and hopefully a dummy db attachment) for the next steps I need to make.
As asked earlier, but with a too soimplified db, here's what I need to do next:

A. How would you write the Query to Total for each month, the PO Numbers that fall into three groups:
1. if it starts with "2013" it is called an "Ad Hoc" Allocation
2. if it is a format of ####R###, it is a "Coord" Allocation;
3. if it is a format of ####S###, it is a "Spec" Allocation.

B. How would you write a Query to total Jan, through April, by the Allocation type, by product? Again, doing one sheet (Table) is what I have supplied in the Sample file, however, I have the task of doing this for about 15 other sheets (Tables). All by a product name and size.

The issue with the parameter request in your query for "Order_Create_Date" is that field does not exist in your table. If is supposed to represent a particular field then you will need to change that. That name was what you used in the original request in your Excel sheet. I'm guessing now that it is probably your Doc Date field.

Here is a SQL statement that will include 2012 in Ad Hoc

Please Login or Register  to view this content.

Thank you Alan. I was close, but not close enough. It works excellently. You've got me learning and trying. I very much appreciate your input, guidance and expertise. Thank you!

You are welcome. Keep at it. It is a rewarding experience.

Again, spectacular results.
Yes, I caught the field change earlier on and made those adjustments. The more I learn from you, and books, and try, the more I catch on and understand. Thank you Alan!

A terrific forum, this is!

Last step now, I need to write the SQL statement to show a sum of all products, on one query, by the month, i.e., totalling all the PO Numbers, per the "Type."

Explained better: query all Types (totalling all the PO Numbers) by the month, Jan-Feb-Mar-Apr. This will entail pulling from current queries and then all 14 Tables. This will be an interesting task.

(removed for duplication) .

I think the first step is to create a union query put all the information in your 14 queries into one query/table. You can then use that to do your aggregate query

http://www.w3schools.com/sql/sql_union.asp