DSUM specific records based on another field

[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?

 

[kingston via AccessMonster.com]

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

 

[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?

 

[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?

 

[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?

 

[ageorgesonster]

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

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?