Adding Values from different columns in a query.

J

James O

Hello all,

This question is a bit strange hopefully I can explain it properly. I have
a table with 3 columns. One of the columns is named MIO and there are 2
Values NACC and NACW. Then there is a column named volume with numerical
values in it. Then I have a Column named vendorcode. None of the cloumns have
a unique value. The same vendor code appears in NACC and NACW. What I want to
do is add the volume from vendor 1111 NACW to the volume for vendor 1111
NACC. Hopefully this was clear. Thanks very much!


James O
 
D

Dirk Goldgar

James O said:
Hello all,

This question is a bit strange hopefully I can explain it properly.
I have a table with 3 columns. One of the columns is named MIO and
there are 2 Values NACC and NACW. Then there is a column named volume
with numerical values in it. Then I have a Column named vendorcode.
None of the cloumns have a unique value. The same vendor code appears
in NACC and NACW. What I want to do is add the volume from vendor
1111 NACW to the volume for vendor 1111 NACC. Hopefully this was
clear. Thanks very much!
Click to expand...

So you have, for example,

MIO Volume VendorCode
-------- -------- --------------
NACC 10 1111
NACW 20 1111
NACC 50 2222
NACW 60 2222

And you want to get

VendorCode TotalVolume
------------- -------------
1111 30
2222 110

Is that what you had in mind?

You'd use a totals query for this. The SQL of such a query would be

SELECT VendorCode, Sum(Volume) As TotalVolume
FROM YourTable
GROUP BY VendorCode;

If the table might have other MIO values besides NACC and NACW, and you
only want to add up those two, you'd need to add a WHERE clause to the
query:

SELECT VendorCode, Sum(Volume) As TotalVolume
FROM YourTable
WHERE MIO In ('NACC', 'NACW')
GROUP BY VendorCode;
 
T

Tom Wickerath

Hi James,

I think this will work for you. Create a new query. Dismiss the select table
dialog without selecting any tables. Click on View > SQL View in query
design. Copy the following SQL statement and paste it into the SQL window:

SELECT vendorcode, Sum(Volume) AS [Total Volume]
FROM YourTableName
WHERE MIO="NACC" Or MIO="NACW"
GROUP BY vendorcode;

You can switch back to View > Design View, if you wish.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hello all,

This question is a bit strange hopefully I can explain it properly. I have
a table with 3 columns. One of the columns is named MIO and there are 2
Values NACC and NACW. Then there is a column named volume with numerical
values in it. Then I have a Column named vendorcode. None of the cloumns have
a unique value. The same vendor code appears in NACC and NACW. What I want to
do is add the volume from vendor 1111 NACW to the volume for vendor 1111
NACC. Hopefully this was clear. Thanks very much!


James O
 
J

James O

Thanks very much for your responses I have tried to incorporate this and have
some problems. I will clarify. Here is what the table looks like.There is
more than 1 MIO value.

VendorCode Volume MIO
2515 1 NBCC

Ok The vendor codes range from 2000 to 9000. The count can be any number.
There are 7 different MIO's. NACC and NACW are the two that give me
trouble..... here is why. I pull a report showing these fields and I am
trying to accurately import into an access table. I have a Import table that
I put the initial data on. Then I append to the main table adding a date and
timestamp of time/date appended. There can be more than one of the same
vendor code. That is not a problem accept with NACC NACW. On a report that I
pull from this data I count the total number of times a vendor code appears
under one of the MIO's on a given date and a sum of the Volume for each
instance of that VendorCode under the same MIO. So I Import this...

VendorCode Volume MIO
2515 2 NBCC
2515 2 NBCC
2516 2 NACC
2516 2 NACC

And Output this on the report.

VendorCode CountOfVendor Volume MIO
2515 2 4 NBCC
2516 2 4 NACC

On a much larger scale of course. My problem is with NACC and NACW. I need
to add NACW volumes to NACC records of the same vendor codes. But NOT add the
NACW vendor codes to the CountofVendor. AND not have NACW on the report. So
remove NACW from the report but add its volume to the corresponding vendor
codes and not add NACW vendor codes to the CounfOfVendor. So what is better,
do this during the append process or using queries between the table and the
report. I am going to mess around with using multiple queries between the
table and report. I apologize for my previous post it was not detailed
enough. Thanks for all your help and have a great day!


James O'Donnell
 
D

Dirk Goldgar

James O said:
Thanks very much for your responses I have tried to incorporate this
and have some problems. I will clarify. Here is what the table looks
like.There is more than 1 MIO value.

VendorCode Volume MIO
2515 1 NBCC

Ok The vendor codes range from 2000 to 9000. The count can be any
number. There are 7 different MIO's. NACC and NACW are the two that
give me trouble..... here is why. I pull a report showing these
fields and I am trying to accurately import into an access table. I
have a Import table that I put the initial data on. Then I append to
the main table adding a date and timestamp of time/date appended.
There can be more than one of the same vendor code. That is not a
problem accept with NACC NACW. On a report that I pull from this data
I count the total number of times a vendor code appears under one of
the MIO's on a given date and a sum of the Volume for each instance
of that VendorCode under the same MIO. So I Import this...

VendorCode Volume MIO
2515 2 NBCC
2515 2 NBCC
2516 2 NACC
2516 2 NACC

And Output this on the report.

VendorCode CountOfVendor Volume MIO
2515 2 4
NBCC 2516 2 4
NACC

On a much larger scale of course. My problem is with NACC and NACW. I
need to add NACW volumes to NACC records of the same vendor codes.
But NOT add the NACW vendor codes to the CountofVendor. AND not have
NACW on the report. So remove NACW from the report but add its volume
to the corresponding vendor codes and not add NACW vendor codes to
the CounfOfVendor. So what is better, do this during the append
process or using queries between the table and the report. I am going
to mess around with using multiple queries between the table and
report. I apologize for my previous post it was not detailed enough.
Thanks for all your help and have a great day!


James O'Donnell
Click to expand...

If I understand you correctly, you want to add the Volume of NACW
entries to those of NACC entries, but otherwise act as though they
didn't exist -- don't count them, don't show them. If you were willing
to count them, you could just transform NACWs to NACCs on input, but the
requirement not to count them makes it more complicated.

One possible approach would be to do something like this:

SELECT
YourTable.VendorCode,
IIf([YourTable].[MIO]="NACW","NACC",[YourTable].[MIO])
AS MIO,
Sum(IIf(YourTable.MIO="NACW",0,1))
AS CountOfVendor,
Sum(YourTable.Volume) AS TotalVolume
FROM YourTable
GROUP BY
YourTable.VendorCode,
IIf([YourTable].[MIO]="NACW","NACC",[YourTable].[MIO]);
 
Top