Combining Records with Sum

[bill1234]

Hi,


I have a list of vendor records that I'm interested in combining (With the amount field summing). I am almost able to get what I want using the query wizard. The issue I'm having is that it appears that I am only getting records combined when the data in every field (other than the amount field, since that is being summed) is identical. There is a particular field (See very basic example attached) where I just want what ever data is in the first record on the combined query record. Is there any way to handle this?

[bill1234]

The image on the left is my desired result, the image on the right is the original table.

[Welshgasman]

Do you have an autonumber on that table?

[Minty]

Your totals query needs to remove the Sales region from its process.
It makes no sense to have it the output as it's meaningless in respect of the total anyway.

If you really have to have the Sales Region listed, you could use a DMax() or DMin() to get the first or last region, but it will slow down the query if there are lots of records.

[Bulzie]

Bill just curious why would you only want it to show the first Sales region but you are summing all the sales regions?

[bill1234]

Do you have an autonumber on that table?

Apologies, I do not know what you mean. I'm kind of an Access beginner.

[bill1234]

Hi, I know that data in the example doesn't make sense. For the data I would actually be using, it would make sense.

[bill1234]

Your totals query needs to remove the Sales region from its process.
It makes no sense to have it the output as it's meaningless in respect of the total anyway.

If you really have to have the Sales Region listed, you could use a DMax() or DMin() to get the first or last region, but it will slow down the query if there are lots of records.

How do I use DMax or Dmin? I'm kind of a beginner.

[Welshgasman]

Try googling for each and pick the microsoft link.

[Minty]

If you open your present query and click on SQL View it will give you the SQL Query text of your current query.
Copy and paste that here and we can probably assist.

[mike60smart]

Hi Bill
Can you upload a copy of the database with no confidential data?

[madpiet]

Here's one way... I did it with two queries. This one gets the first record for each vendor:

SELECT SalesInfo.Vendor, SalesInfo.[Address ], First(SalesInfo.[Sales Region]) AS [FirstOfSales Region]
FROM SalesInfo
GROUP BY SalesInfo.Vendor, SalesInfo.[Address ];

(Beware the blank spaces... I attached to an Excel file)

and the second:

SELECT FirstVendorAddress.Vendor, FirstVendorAddress.[Address ], FirstVendorAddress.[FirstOfSales Region], Sum(SalesInfo.[Amount Paid]) AS [SumOfAmount Paid]
FROM FirstVendorAddress INNER JOIN SalesInfo ON FirstVendorAddress.Vendor = SalesInfo.Vendor
GROUP BY FirstVendorAddress.Vendor, FirstVendorAddress.[Address ], FirstVendorAddress.[FirstOfSales Region];

The second query uses the groupings from Query1 and joins back to the SalesInfo (original) table.

(The more I read your question, the question seems to be a moving target.)

[bill1234]

If you open your present query and click on SQL View it will give you the SQL Query text of your current query.
Copy and paste that here and we can probably assist.

Here is the SQL text. It's "PayType" field that I would like to do this to.

SELECT DISTINCTROW [combine sheet].Cat, [combine sheet].VendorID, [combine sheet].Address1, [combine sheet].Address2, [combine sheet].City, [combine sheet].ST, [combine sheet].Zip, [combine sheet].PayType, [combine sheet].IDName, Sum([combine sheet].Amount) AS [Sum Of Amount]
FROM [combine sheet]
GROUP BY [combine sheet].Cat, [combine sheet].VendorID, [combine sheet].Address1, [combine sheet].Address2, [combine sheet].City, [combine sheet].ST, [combine sheet].Zip, [combine sheet].PayType, [combine sheet].IDName;

[ano]

SELECT  s.[v] as v, sum(s.[p]) as p , first(s.[a]) as a,first( s.[s]) as sFROM s
group by s.[v];

this is the result u ask for but make no sense to me , i expect sales region is also part of sum , then group by s.[v],s.[s]

[madpiet]

Here is the SQL text. It's "PayType" field that I would like to do this to.

SELECT DISTINCTROW [combine sheet].Cat, [combine sheet].VendorID, [combine sheet].Address1, [combine sheet].Address2, [combine sheet].City, [combine sheet].ST, [combine sheet].Zip, [combine sheet].PayType, [combine sheet].IDName, Sum([combine sheet].Amount) AS [Sum Of Amount]
FROM [combine sheet]
GROUP BY [combine sheet].Cat, [combine sheet].VendorID, [combine sheet].Address1, [combine sheet].Address2, [combine sheet].City, [combine sheet].ST, [combine sheet].Zip, [combine sheet].PayType, [combine sheet].IDName;

Did the question change while I was away?

I answered your original question. Maybe you need to explain it better with an example of what you're starting with and what your expected results are.