Large Database Query by Date

[BISPO82]

Anyone have a suggestion?

I'm very new to ACCESS.

At work I have a huge Parts Order database.
There are tables for our Part Info, Order Details (which parts to where), and Order Header (how and when).

I need to create a query that shows which parts are ordered more often than the others. I have completed the query using the Part# column of the order details table and used a COUNT TOTAL to show how many parts have been ordered from that same table.

What I have is a list of a single part and a total of how many times it has been ordered.

Unfortunately that query gives me a total of EVERY part ordered since our system has been implemented (3 years).

I need this list to only total the parts ordered since Jan. 1, 2006.

I have run the query using the order date information from the Order Header table. This worked, except instead of listing a single total for each part it lists out every part and date of the order.

Examples of what I mean:
This is what I want:
Part# Total
1122 200
1133 233
1144 45
1155 252

This is what I have:
Part# Date Total
1122 11/22/04 1
1122 01/29/05 1
1122 02/29/06 1
1122 06/29/06 1
1122 07/29/06 1
1133 11/29/04 1
1133 11/29/04 1
1133 11/29/05 1
1144 11/29/06 1
1144 11/29/06 1
1144 11/29/06 1
1155 11/29/04 1
1155 11/29/05 1

I hope you get the idea.

I know this isn't a very good explanation and a confusing question, and it won't be an easy fix.

Just asking for suggestions.

Thanks,

Bispo82

 

[Melagan]

An idea:
In query design view, with the column that is displaying the date, add the following criteria:

>=#01/01/2006#

Otherwise, we'll need you to cut and paste your SQL code so we can see exactly how your tables are named and insure that the joins are properly constructed. Do this by opening your query in design view, then click view on the menu bar and select 'SQL View' - cut/paste that info here.



~Melagan
______
"It's never too late to become what you might have been.

 

[SkipVought]

Hi,

In accordance with Melagan's excellent suggestion, may I add, that when you specify a date as a criteria, you ALWAYS use a 4-digit year, despite the way that your date column is displayed in your example.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[BISPO82]

This is the one that gives me every part every ordered.

SELECT [ORDER DETAILS].PARTNO, PARTS.DESC, Count([ORDER DETAILS].PARTNO) AS CountOfPARTNO
FROM PARTS INNER JOIN [ORDER DETAILS] ON (PARTS.PARTNO = [ORDER DETAILS].PARTNO) AND (PARTS.PARTNO = [ORDER DETAILS].PARTNO) AND (PARTS.PARTNO = [ORDER DETAILS].PARTNO)
GROUP BY [ORDER DETAILS].PARTNO, PARTS.DESC;

This one returns the ones since the right date,

SELECT [ORDER DETAILS].PARTNO, PARTS.DESC, Count([ORDER DETAILS].PARTNO) AS CountOfPARTNO, qryORDERS_PROCESSED_BY_DATE.ORDERDATE
FROM (PARTS INNER JOIN [ORDER DETAILS] ON (PARTS.PARTNO = [ORDER DETAILS].PARTNO) AND (PARTS.PARTNO = [ORDER DETAILS].PARTNO) AND (PARTS.PARTNO = [ORDER DETAILS].PARTNO)) INNER JOIN qryORDERS_PROCESSED_BY_DATE ON PARTS.PARTNO = qryORDERS_PROCESSED_BY_DATE.PARTNO
GROUP BY [ORDER DETAILS].PARTNO, PARTS.DESC, qryORDERS_PROCESSED_BY_DATE.ORDERDATE
HAVING (((qryORDERS_PROCESSED_BY_DATE.ORDERDATE)>#1/1/2006#));

but I don't want it to list every order, just the part number, and how many orders for each part have been made since 1/1/2006.

Any thoughts? I know I might not have explained very well.

 

[lespaul]

Why are you joining on the same table three times on the same field?

I would think that this would work:

SELECT O.PartNo, P.Desc, Count(O.PartNo) As CountOfPartNo
FROM Parts P
INNER JOIN [Order Details] O ON P.PartNo = O.PartNo
INNER JOIN [Order Header] H ON O.Order# = H.Order#
WHERE H.OrderDate > #1/1/2006#

you'll need to check the JOIN from the header table since I had to guess at those field names.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins