Adding columns of a record in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a table that has a list of different materials in one column and a
series of columns for different dates, each containing order numbers for each
material.

Material Jan Feb
abc 1 1
def 5 7

I want to know how to create another column that will sum up across the
columns; a sum for abc, for def, etc. I would like to do this in a query.
Please help me! Thanks.
 
Just add a field to the query :
Total: = [Jan] + [Feb] + etc...
If *any* of these fields are *ever* likely to be null:
Total: = Nz([Jan],0) + Nz([Feb],0) + etc..

HTH,
 
Thanks for your answer but I tried that. For each of those columns I have an
expression that is very long, so whenever I try to do that, there is not
enough space. Also, it keeps saying that there is more than one possible
source for the field, even though there is reference to the table. I hope
that is not too confusing. Any other suggestions? Thanks for your help.

George Nicholson said:
Just add a field to the query :
Total: = [Jan] + [Feb] + etc...
If *any* of these fields are *ever* likely to be null:
Total: = Nz([Jan],0) + Nz([Feb],0) + etc..

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Flower10585 said:
Hi, I have a table that has a list of different materials in one column
and a
series of columns for different dates, each containing order numbers for
each
material.

Material Jan Feb
abc 1 1
def 5 7

I want to know how to create another column that will sum up across the
columns; a sum for abc, for def, etc. I would like to do this in a query.
Please help me! Thanks.
Click to expand...
Click to expand...
 
Flower,
Not sure how your tables are set up, but is there a reason you have a
field for each month on each material? If you set up your DB with a couple
of tables, you could run a query and a report and get the information you
need.

set one table up as:
tblMaterial with the following fields:
MaterialID (make this your Primary Key)
Material

Second Table:
tblQty with the following tables:
QtyID
DateOrdered
MaterialID (Foreign Key) link this as a Many in a 1 to Many relationship
with the MaterialID in the tblMaterial

Then if you build a query with both tables added, you can get a report to
count up by Month the number of orders of each type of material.

Not great at this stuff, so all out there, if I said something wrong please
correct me.


Flower10585 said:
Thanks for your answer but I tried that. For each of those columns I have
an
expression that is very long, so whenever I try to do that, there is not
enough space. Also, it keeps saying that there is more than one possible
source for the field, even though there is reference to the table. I hope
that is not too confusing. Any other suggestions? Thanks for your help.

George Nicholson said:
Just add a field to the query :
Total: = [Jan] + [Feb] + etc...
If *any* of these fields are *ever* likely to be null:
Total: = Nz([Jan],0) + Nz([Feb],0) + etc..

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Flower10585 said:
Hi, I have a table that has a list of different materials in one column
and a
series of columns for different dates, each containing order numbers
for
each
material.

Material Jan Feb
abc 1 1
def 5 7

I want to know how to create another column that will sum up across the
columns; a sum for abc, for def, etc. I would like to do this in a
query.
Please help me! Thanks.
Click to expand...
Click to expand...
Click to expand...
 
I'm trying to keep it in a query as opposed to a report for a particular
reason.
Thanks for your help though.

Walter Steadman said:
Flower,
Not sure how your tables are set up, but is there a reason you have a
field for each month on each material? If you set up your DB with a couple
of tables, you could run a query and a report and get the information you
need.

set one table up as:
tblMaterial with the following fields:
MaterialID (make this your Primary Key)
Material

Second Table:
tblQty with the following tables:
QtyID
DateOrdered
MaterialID (Foreign Key) link this as a Many in a 1 to Many relationship
with the MaterialID in the tblMaterial

Then if you build a query with both tables added, you can get a report to
count up by Month the number of orders of each type of material.

Not great at this stuff, so all out there, if I said something wrong please
correct me.


Flower10585 said:
Thanks for your answer but I tried that. For each of those columns I have
an
expression that is very long, so whenever I try to do that, there is not
enough space. Also, it keeps saying that there is more than one possible
source for the field, even though there is reference to the table. I hope
that is not too confusing. Any other suggestions? Thanks for your help.

George Nicholson said:
Just add a field to the query :
Total: = [Jan] + [Feb] + etc...
If *any* of these fields are *ever* likely to be null:
Total: = Nz([Jan],0) + Nz([Feb],0) + etc..

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hi, I have a table that has a list of different materials in one column
and a
series of columns for different dates, each containing order numbers
for
each
material.

Material Jan Feb
abc 1 1
def 5 7

I want to know how to create another column that will sum up across the
columns; a sum for abc, for def, etc. I would like to do this in a
query.
Please help me! Thanks.
Click to expand...
Click to expand...
Click to expand...
 
All is good! Figured it out..Thanks all

Flower10585 said:
I'm trying to keep it in a query as opposed to a report for a particular
reason.
Thanks for your help though.

Walter Steadman said:
Flower,
Not sure how your tables are set up, but is there a reason you have a
field for each month on each material? If you set up your DB with a couple
of tables, you could run a query and a report and get the information you
need.

set one table up as:
tblMaterial with the following fields:
MaterialID (make this your Primary Key)
Material

Second Table:
tblQty with the following tables:
QtyID
DateOrdered
MaterialID (Foreign Key) link this as a Many in a 1 to Many relationship
with the MaterialID in the tblMaterial

Then if you build a query with both tables added, you can get a report to
count up by Month the number of orders of each type of material.

Not great at this stuff, so all out there, if I said something wrong please
correct me.


Flower10585 said:
Thanks for your answer but I tried that. For each of those columns I have
an
expression that is very long, so whenever I try to do that, there is not
enough space. Also, it keeps saying that there is more than one possible
source for the field, even though there is reference to the table. I hope
that is not too confusing. Any other suggestions? Thanks for your help.

:

Just add a field to the query :
Total: = [Jan] + [Feb] + etc...
If *any* of these fields are *ever* likely to be null:
Total: = Nz([Jan],0) + Nz([Feb],0) + etc..

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hi, I have a table that has a list of different materials in one column
and a
series of columns for different dates, each containing order numbers
for
each
material.

Material Jan Feb
abc 1 1
def 5 7

I want to know how to create another column that will sum up across the
columns; a sum for abc, for def, etc. I would like to do this in a
query.
Please help me! Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top