DSUM specific records based on another field

A

ageorgesonster

Hello All

I am trying to run a query with a DSUM function to sum quantities in
one field based off the value of another field. I have an inventory
quantity that I would like to sum based on part numbers but I am having
trouble with the criteria. It should look something like this:

Autonumber Part# Quantity Sum
1 A 100 100
2 B 500 500
3 C 650 650
4 B 250 750
5 C 300 950
6 A 300 400
7 A 1000 1400

Is there something very simple that I am missing here?
 
K

kingston via AccessMonster.com

Do you mean something like this:
DSum("[Quantity]","[Inventory]","[Part]=' & [Part] & "' And [AutoID]<=" &
[AutoID])
 
A

ageorgesonster

I am pretty sure this is what I am looking for but I keep getting
string and syntax errors. Can someone offer an explanation for what the
parentheses, apostrophes and ampersands are actually doing?

Do you mean something like this:
DSum("[Quantity]","[Inventory]","[Part]=' & [Part] & "' And [AutoID]<=" &
[AutoID])


Hello All

I am trying to run a query with a DSUM function to sum quantities in
one field based off the value of another field. I have an inventory
quantity that I would like to sum based on part numbers but I am having
trouble with the criteria. It should look something like this:

Autonumber Part# Quantity Sum
1 A 100 100
2 B 500 500
3 C 650 650
4 B 250 750
5 C 300 950
6 A 300 400
7 A 1000 1400

Is there something very simple that I am missing here?
 
A

ageorgesonster

This is what I have now but it just gives a total for the entire field
on each line.

DSum("[Qty]","[Order and Production Tracking]","[PartNumber]= ' " &
[PartNumber] & " ' " And "[TrackingID]<=" & [TrackingID])


I am pretty sure this is what I am looking for but I keep getting
string and syntax errors. Can someone offer an explanation for what the
parentheses, apostrophes and ampersands are actually doing?

Do you mean something like this:
DSum("[Quantity]","[Inventory]","[Part]=' & [Part] & "' And [AutoID]<=" &
[AutoID])


Hello All

I am trying to run a query with a DSUM function to sum quantities in
one field based off the value of another field. I have an inventory
quantity that I would like to sum based on part numbers but I am having
trouble with the criteria. It should look something like this:

Autonumber Part# Quantity Sum
1 A 100 100
2 B 500 500
3 C 650 650
4 B 250 750
5 C 300 950
6 A 300 400
7 A 1000 1400

Is there something very simple that I am missing here?
 
D

Duane Hookom

Try this which assume PartNumber is text and TrackingID is numeric:

DSum("[Qty]","[Order and Production Tracking]","[PartNumber]= """ &
[PartNumber] & """ And [TrackingID]<=" & [TrackingID])


--
Duane Hookom
MS Access MVP

This is what I have now but it just gives a total for the entire field
on each line.

DSum("[Qty]","[Order and Production Tracking]","[PartNumber]= ' " &
[PartNumber] & " ' " And "[TrackingID]<=" & [TrackingID])


I am pretty sure this is what I am looking for but I keep getting
string and syntax errors. Can someone offer an explanation for what the
parentheses, apostrophes and ampersands are actually doing?

Do you mean something like this:
DSum("[Quantity]","[Inventory]","[Part]=' & [Part] & "' And [AutoID]<="
&
[AutoID])


(e-mail address removed) wrote:
Hello All

I am trying to run a query with a DSUM function to sum quantities in
one field based off the value of another field. I have an inventory
quantity that I would like to sum based on part numbers but I am
having
trouble with the criteria. It should look something like this:

Autonumber Part# Quantity Sum
1 A 100 100
2 B 500 500
3 C 650 650
4 B 250 750
5 C 300 950
6 A 300 400
7 A 1000 1400

Is there something very simple that I am missing here?
 
A

ageorgesonster

Thats Beautiful, works like a champ. I appreciate the help
Aaron

Duane said:
Try this which assume PartNumber is text and TrackingID is numeric:

DSum("[Qty]","[Order and Production Tracking]","[PartNumber]= """ &
[PartNumber] & """ And [TrackingID]<=" & [TrackingID])


--
Duane Hookom
MS Access MVP

This is what I have now but it just gives a total for the entire field
on each line.

DSum("[Qty]","[Order and Production Tracking]","[PartNumber]= ' " &
[PartNumber] & " ' " And "[TrackingID]<=" & [TrackingID])


I am pretty sure this is what I am looking for but I keep getting
string and syntax errors. Can someone offer an explanation for what the
parentheses, apostrophes and ampersands are actually doing?


kingston via AccessMonster.com wrote:
Do you mean something like this:
DSum("[Quantity]","[Inventory]","[Part]=' & [Part] & "' And [AutoID]<="
&
[AutoID])


(e-mail address removed) wrote:
Hello All

I am trying to run a query with a DSUM function to sum quantities in
one field based off the value of another field. I have an inventory
quantity that I would like to sum based on part numbers but I am
having
trouble with the criteria. It should look something like this:

Autonumber Part# Quantity Sum
1 A 100 100
2 B 500 500
3 C 650 650
4 B 250 750
5 C 300 950
6 A 300 400
7 A 1000 1400

Is there something very simple that I am missing here?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top