Consolidating Records

[HeelNGville]

I have a table that contains specific customer transaction data. In this table there are the following fields:

Document
Vendor
Vendor Name
Units
Value

There are instances in which multiple records could exist for one document number. Such as:

Document-----Vendor-------Vendor Name------Units------Value--------
12345678------AAAA--------ABC Co.-------100------200.00
12345678------BBBB---------BBC Co.--------25-------50.00
12345678------CCCC----------CCC Co.--------10-------20.00

What I am attempting to do is, sum the units & value fields and report the data on one line with the vendor record with the > unit count. So, for the example above, the data returned would be:

Document-----Vendor-------Vendor Name------Units------Value--------
12345678------AAAA--------ABC Co.-------135------270.00

Can this be performed in one single query vs multiple queries. If so, any guidance would be welcomed.
Thx

 

[Jon K]

If you are using Access 2K or later versions, you can run a query that joins the table to a subselect statement in brackets like this:-

SELECT ss.Document, [TableName].Vendor, [TableName].[Vendor Name], ss.SumOfUnits AS Units, ss.SumOfValue AS [Value]
FROM (SELECT [TableName].Document, Max([TableName].Units) AS MaxOfUnits, Sum([TableName].Units) AS SumOfUnits, Sum([TableName].Value) AS SumOfValue
FROM [TableName]
GROUP BY [TableName].Document) AS ss INNER JOIN TableName ON (ss.MaxOfUnits = [TableName].Units) AND (ss.Document = [TableName].Document);


But when the query is saved, Access may change the brackets surrounding the subselect to a pair of square brackets with an ending dot.

SELECT ss.Document, [TableName].Vendor, [TableName].[Vendor Name], ss.SumOfUnits AS Units, ss.SumOfValue AS [Value]
FROM [SELECT [TableName].Document, Max([TableName].Units) AS MaxOfUnits, Sum([TableName].Units) AS SumOfUnits, Sum([TableName].Value) AS SumOfValue
FROM [TableName]
GROUP BY [TableName].Document]. AS ss INNER JOIN TableName ON (ss.Document = [TableName].Document) AND (ss.MaxOfUnits = [TableName].Units);

Hence, if you subsequently edit the query, you may need to change [...]. back to (...)


If you use two queries instead of a single query, you don't have such a problem in editing.

I have attached a database in Access 2K, which contains both methods.
.