How to Combine Multiple Records into a Single Record Adding the Quantity Values of Same Fields

[Ken]

I am creating a database in Access XP and cannot figure out how to combine
records having the same field value from a query into another query. The
SQL below is from the source query,

SELECT OrderDetailsQuery.OrderNumber, OrderDetailsQuery.InvoiceNumber,
OrderDetailsQuery.CustPONumber, OrderDetailsQuery.OrderDate,
OrderDetailsQuery.ProdCode, OrderDetailsQuery.CustCode,
ProductsQuery.ProductBaseCode, OrderDetailsQuery.Label,
OrderDetailsQuery.TagStyleNumber, OrderDetailsQuery.XS, OrderDetailsQuery.S,
OrderDetailsQuery.M, OrderDetailsQuery.L, OrderDetailsQuery.XL,
OrderDetailsQuery.[2XL], OrderDetailsQuery.[3XL], OrderDetailsQuery.[4XL],
OrderDetailsQuery.[5XL], OrderDetailsQuery.[6XL], fGetLabelsRequired([XS])
AS XS_Labels, fGetlabelsrequired() AS S_Labels, fgetlabelsrequired([M])
AS M_Labels, fgetlabelsrequired([L]) AS L_Labels, fgetlabelsrequired([XL])
AS XL_Labels, fgetlabelsrequired([2XL]) AS 2XL_Labels,
fgetlabelsrequired([3XL]) AS 3XL_Labels, fgetlabelsrequired([4XL]) AS
4XL_Labels, fgetlabelsrequired([5XL]) AS 5XL_Labels,
fgetlabelsrequired([6XL]) AS 6XL_Labels
FROM OrderDetailsQuery INNER JOIN ProductsQuery ON
OrderDetailsQuery.ProdCode = ProductsQuery.ProdCode;

Basically, multiple records may share the same InvoiceNumber with each
record representing an item at a different unit price. This will occur when
multiple items are ordered under a single order. When this happens I want
the query to result in a single record that adds the quantities for the same
fields together from all such records. That is, only one "combined" record
will result for any InvoiceNumber.

How does one do this in MS Access? I suspect there must be a way, but I
cannot find it anywhere.

I do not really care whether I create a new query with the desired result or
modify the existing query to only produce the desired result.

 

[Duane Hookom]

Your table looks a bit like a spreadsheet unless OrderDetailsQuery is a
crosstab.
However, if you want to summarize multiple records into a single record with
totals, use a "totals" query that groups by some fields and aggregates
others.

 

[Ken]

Thanks for the response.

What do you mean? How does one do this? Can you show an example? I tried
the "totals" part of what you suggest but how does one do "aggregates"?
Thanks.

Your table looks a bit like a spreadsheet unless OrderDetailsQuery is a
crosstab.
However, if you want to summarize multiple records into a single record
with totals, use a "totals" query that groups by some fields and
aggregates others.

--
Duane Hookom
MS Access MVP

I am creating a database in Access XP and cannot figure out how to combine
records having the same field value from a query into another query. The
SQL below is from the source query,

SELECT OrderDetailsQuery.OrderNumber, OrderDetailsQuery.InvoiceNumber,
OrderDetailsQuery.CustPONumber, OrderDetailsQuery.OrderDate,
OrderDetailsQuery.ProdCode, OrderDetailsQuery.CustCode,
ProductsQuery.ProductBaseCode, OrderDetailsQuery.Label,
OrderDetailsQuery.TagStyleNumber, OrderDetailsQuery.XS,
OrderDetailsQuery.S, OrderDetailsQuery.M, OrderDetailsQuery.L,
OrderDetailsQuery.XL, OrderDetailsQuery.[2XL], OrderDetailsQuery.[3XL],
OrderDetailsQuery.[4XL], OrderDetailsQuery.[5XL],
OrderDetailsQuery.[6XL], fGetLabelsRequired([XS]) AS XS_Labels,
fGetlabelsrequired() AS S_Labels, fgetlabelsrequired([M]) AS M_Labels,
fgetlabelsrequired([L]) AS L_Labels, fgetlabelsrequired([XL]) AS
XL_Labels, fgetlabelsrequired([2XL]) AS 2XL_Labels,
fgetlabelsrequired([3XL]) AS 3XL_Labels, fgetlabelsrequired([4XL]) AS
4XL_Labels, fgetlabelsrequired([5XL]) AS 5XL_Labels,
fgetlabelsrequired([6XL]) AS 6XL_Labels
FROM OrderDetailsQuery INNER JOIN ProductsQuery ON
OrderDetailsQuery.ProdCode = ProductsQuery.ProdCode;

Basically, multiple records may share the same InvoiceNumber with each
record representing an item at a different unit price. This will occur
when multiple items are ordered under a single order. When this happens
I want the query to result in a single record that adds the quantities
for the same fields together from all such records. That is, only one
"combined" record will result for any InvoiceNumber.

How does one do this in MS Access? I suspect there must be a way, but I
cannot find it anywhere.

I do not really care whether I create a new query with the desired result
or modify the existing query to only produce the desired result.

 

[Duane Hookom]

Aggregates in a totals query are created by selecting Count, Sum, Avg, or
similar from the Totals line in the query design grid.

--
Duane Hookom
MS Access MVP

Thanks for the response.

What do you mean? How does one do this? Can you show an example? I
tried the "totals" part of what you suggest but how does one do
"aggregates"? Thanks.

Your table looks a bit like a spreadsheet unless OrderDetailsQuery is a
crosstab.
However, if you want to summarize multiple records into a single record
with totals, use a "totals" query that groups by some fields and
aggregates others.

--
Duane Hookom
MS Access MVP

I am creating a database in Access XP and cannot figure out how to
combine records having the same field value from a query into another
query. The SQL below is from the source query,

SELECT OrderDetailsQuery.OrderNumber, OrderDetailsQuery.InvoiceNumber,
OrderDetailsQuery.CustPONumber, OrderDetailsQuery.OrderDate,
OrderDetailsQuery.ProdCode, OrderDetailsQuery.CustCode,
ProductsQuery.ProductBaseCode, OrderDetailsQuery.Label,
OrderDetailsQuery.TagStyleNumber, OrderDetailsQuery.XS,
OrderDetailsQuery.S, OrderDetailsQuery.M, OrderDetailsQuery.L,
OrderDetailsQuery.XL, OrderDetailsQuery.[2XL], OrderDetailsQuery.[3XL],
OrderDetailsQuery.[4XL], OrderDetailsQuery.[5XL],
OrderDetailsQuery.[6XL], fGetLabelsRequired([XS]) AS XS_Labels,
fGetlabelsrequired() AS S_Labels, fgetlabelsrequired([M]) AS
M_Labels, fgetlabelsrequired([L]) AS L_Labels, fgetlabelsrequired([XL])
AS XL_Labels, fgetlabelsrequired([2XL]) AS 2XL_Labels,
fgetlabelsrequired([3XL]) AS 3XL_Labels, fgetlabelsrequired([4XL]) AS
4XL_Labels, fgetlabelsrequired([5XL]) AS 5XL_Labels,
fgetlabelsrequired([6XL]) AS 6XL_Labels
FROM OrderDetailsQuery INNER JOIN ProductsQuery ON
OrderDetailsQuery.ProdCode = ProductsQuery.ProdCode;

Basically, multiple records may share the same InvoiceNumber with each
record representing an item at a different unit price. This will occur
when multiple items are ordered under a single order. When this happens
I want the query to result in a single record that adds the quantities
for the same fields together from all such records. That is, only one
"combined" record will result for any InvoiceNumber.

How does one do this in MS Access? I suspect there must be a way, but I
cannot find it anywhere.

I do not really care whether I create a new query with the desired
result or modify the existing query to only produce the desired result.

 

[Ken]

Thanks very much. Your direction helped me greatly in solving this problem.

Sincerely,

Ken

Aggregates in a totals query are created by selecting Count, Sum, Avg, or
similar from the Totals line in the query design grid.

--
Duane Hookom
MS Access MVP

Thanks for the response.

What do you mean? How does one do this? Can you show an example? I
tried the "totals" part of what you suggest but how does one do
"aggregates"? Thanks.

Your table looks a bit like a spreadsheet unless OrderDetailsQuery is a
crosstab.
However, if you want to summarize multiple records into a single record
with totals, use a "totals" query that groups by some fields and
aggregates others.

--
Duane Hookom
MS Access MVP

I am creating a database in Access XP and cannot figure out how to
combine records having the same field value from a query into another
query. The SQL below is from the source query,

SELECT OrderDetailsQuery.OrderNumber, OrderDetailsQuery.InvoiceNumber,
OrderDetailsQuery.CustPONumber, OrderDetailsQuery.OrderDate,
OrderDetailsQuery.ProdCode, OrderDetailsQuery.CustCode,
ProductsQuery.ProductBaseCode, OrderDetailsQuery.Label,
OrderDetailsQuery.TagStyleNumber, OrderDetailsQuery.XS,
OrderDetailsQuery.S, OrderDetailsQuery.M, OrderDetailsQuery.L,
OrderDetailsQuery.XL, OrderDetailsQuery.[2XL], OrderDetailsQuery.[3XL],
OrderDetailsQuery.[4XL], OrderDetailsQuery.[5XL],
OrderDetailsQuery.[6XL], fGetLabelsRequired([XS]) AS XS_Labels,
fGetlabelsrequired() AS S_Labels, fgetlabelsrequired([M]) AS
M_Labels, fgetlabelsrequired([L]) AS L_Labels, fgetlabelsrequired([XL])
AS XL_Labels, fgetlabelsrequired([2XL]) AS 2XL_Labels,
fgetlabelsrequired([3XL]) AS 3XL_Labels, fgetlabelsrequired([4XL]) AS
4XL_Labels, fgetlabelsrequired([5XL]) AS 5XL_Labels,
fgetlabelsrequired([6XL]) AS 6XL_Labels
FROM OrderDetailsQuery INNER JOIN ProductsQuery ON
OrderDetailsQuery.ProdCode = ProductsQuery.ProdCode;

Basically, multiple records may share the same InvoiceNumber with each
record representing an item at a different unit price. This will occur
when multiple items are ordered under a single order. When this
happens I want the query to result in a single record that adds the
quantities for the same fields together from all such records. That
is, only one "combined" record will result for any InvoiceNumber.

How does one do this in MS Access? I suspect there must be a way, but
I cannot find it anywhere.

I do not really care whether I create a new query with the desired
result or modify the existing query to only produce the desired result.