I nees help with Sum/Count Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lucien - 19 Apr 2006 17:00 GMT

I am a new Access user and I am an in the early learning stages.
I have multiple tables, one of which has customer order information in it.
I will provide the SQL of what I currently have, but this is what I want to
do:
I want to have a seperate field for each weekly count of invoices and sum of
qtys by item id. All of the invoices/units info is in one table and there
is a posting date field. I want to count the # of invoices and sum the # of
units.

Material    Invoices    Units
12234    2    2
12334    2    4
11134    4    12

where I want it to report like this:

Material    Invoices    Units    Invoices    Units    Invoices    Units
12234    2    2
12334    2    4
11134    4    12

...where each group of Invoices/Units is broken up into a weekly bucket. I
do not know how this can be done. If someone could please look at my SQL and
give me a hand or maybe a push in the right direction. Here it is:

SELECT [Fastener Invoice Data].Plant, BranchTable.[Buy Loc], [Fastener
Invoice Data].Material, Count([Fastener Invoice Data].Reference) AS Invoices,
Sum([Fastener Invoice Data].Qty) AS Units, CPIR_MTL_BASE_040406.SalesStatus
FROM [Fastener Invoice Data (field deployed)] INNER JOIN (((([Fastener
Invoice Data] INNER JOIN CPIR_MTL_BASE_040406 ON [Fastener Invoice
Data].Material = CPIR_MTL_BASE_040406.Material) INNER JOIN BranchTable ON
[Fastener Invoice Data].Plant = BranchTable.Plnt) INNER JOIN [Fastener
List_Field] ON [Fastener Invoice Data].Material = [Fastener
List_Field].ItemID) INNER JOIN [Branch/District/FIS/BIRM] ON [Fastener
Invoice Data].Plant = [Branch/District/FIS/BIRM].Branch) ON ([Fastener
Invoice Data (field deployed)].Material = CPIR_MTL_BASE_040406.Material) AND
([Fastener Invoice Data (field deployed)].Plant = BranchTable.Plnt) AND
([Fastener Invoice Data (field deployed)].Material = [Fastener
List_Field].ItemID) AND ([Fastener Invoice Data (field deployed)].Plant =
[Branch/District/FIS/BIRM].Branch)
GROUP BY [Fastener Invoice Data].Plant, BranchTable.[Buy Loc], [Fastener
Invoice Data].Material, CPIR_MTL_BASE_040406.SalesStatus,
[Branch/District/FIS/BIRM].District, CPIR_MTL_BASE_040406.PMCode,
CPIR_MTL_BASE_040406.VendorName, CPIR_MTL_BASE_040406.PurchGrp
HAVING (((Count([Fastener Invoice Data].Reference))>1))
ORDER BY [Fastener Invoice Data].Plant;

Thanks in advance for any help you could provide.

Reply to this Message

KARL DEWEY - 19 Apr 2006 19:28 GMT

In looking over the SQL you posted I did not see any field label that
appeared to be for a date field.

One way to do what you want is to use a totals query with concatented
Count([Invoices]) & Sum([Units]) as a single field.

Then use a crosstab query.

> I am a new Access user and I am an in the early learning stages.
> I have multiple tables, one of which has customer order information in it.
[quoted text clipped - 44 lines]
>
> Thanks in advance for any help you could provide.

Reply to this Message

Lucien - 19 Apr 2006 20:27 GMT

Oh, sorry...the posting date field is in my table but it wasn't added as a
field to the query, yet.
And sorry again, but I am a beginner and I do not know how to concatenate
fields or use a crosstab query.
Is there some reference material on the web that can show me how to do this?

> In looking over the SQL you posted I did not see any field label that
> appeared to be for a date field.
[quoted text clipped - 52 lines]
> >
> > Thanks in advance for any help you could provide.

Reply to this Message

KARL DEWEY - 19 Apr 2006 23:36 GMT

Substitute your table name for "Customer Order" in the query. Open a query
in design view and click on menu VIEW - SQL View. Paste the first SQL
statement in the query. Then save as "Concatenate Totals" to be used in the
crosstab query.

SELECT Format([Posting Date],"w") AS [Week of Entry], [Customer
Order].Material, Count([Invoices]) & " " & Sum([Units]) AS ABC
FROM [Customer Order]
GROUP BY Format([Posting Date],"w"), [Customer Order].Material;

Open another query in design view and click on menu VIEW - SQL View. Paste
the first SQL statement in the query.

TRANSFORM First([Concatenate Totals].ABC) AS FirstOfABC
SELECT [Concatenate Totals].Material
FROM [Concatenate Totals]
GROUP BY [Concatenate Totals].Material
PIVOT [Concatenate Totals].[Week of Entry];

> Oh, sorry...the posting date field is in my table but it wasn't added as a
> field to the query, yet.
[quoted text clipped - 58 lines]
> > >
> > > Thanks in advance for any help you could provide.

Reply to this Message

KARL DEWEY - 20 Apr 2006 00:36 GMT

TYPO --

> Open another query in design view and click on menu VIEW - SQL View. Paste
> the first SQL statement in the query.

CORRECTION--
Open another query in design view and click on menu VIEW - SQL View. Paste
the SECOND SQL statement in the query.

> Substitute your table name for "Customer Order" in the query. Open a query
> in design view and click on menu VIEW - SQL View. Paste the first SQL
[quoted text clipped - 77 lines]
> > > >
> > > > Thanks in advance for any help you could provide.

Reply to this Message