I nees help with Sum/Count Query

L

Lucien

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.
 
K

KARL DEWEY

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.
 
L

Lucien

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?



KARL DEWEY said:
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.

Lucien said:
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.
Click to expand...
Click to expand...
 
K

KARL DEWEY

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];



Lucien said:
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?



KARL DEWEY said:
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.

Lucien said:
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.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

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


KARL DEWEY said:
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];



Lucien said:
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?



KARL DEWEY said:
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.
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Query 1
Help With Parameter Query 2
Help with Query/SQL 4
Help with query for invoice report 2
Summary query sum from different tables 15
Group query - Access XP 1
Help needed to finish a query that display results of a subquerybased upon a count... 1
Two query logistic error problem. 0

Top