Any Access Gurus?

[memo]

So I have two tables, one with order data and another with part numbers with weights.

i.e.

part number weight
xxxxx 1lb
ssssss 2lb

invoice partnumber qty
1111 xxxxxx 18
2222 sssssss 17

i want access to spit out a collumn that says ok, searching the invoice data table, find part xxxxx in the part weight table and give me the associated weight of the part. any ideas?

ive tried dlookup to no avail, perhaps im using it wrong.

 

[KLin]

are you wanting to see the weight of the ordered quantity for that part number?

Here is a sample of a query that would work:

SELECT tblInvoices.PartNumber, tblPart.Weight, tblPart.Weight*qty As TotalWeight
FROM tblInvoices INNER JOIN tblPart on tblInvoices.PartNumber = tblPart.PartNumber

 

[memo]

Originally posted by: KLin
are you wanting to see the weight of the ordered quantity for that part number?

Here is a sample of a query that would work:

SELECT tblInvoices.PartNumber, tblPart.Weight, tblPart.Weight*qty As TotalWeight
FROM tblInvoices INNER JOIN tblPart on tblInvoices.PartNumber = tblPart.PartNumber

Sorry, I am not so good at creating query code.

How would I input that code into Access?

 

[memo]

Originally posted by: memo

Originally posted by: KLin
are you wanting to see the weight of the ordered quantity for that part number?

Here is a sample of a query that would work:

SELECT tblInvoices.PartNumber, tblPart.Weight, tblPart.Weight*qty As TotalWeight
FROM tblInvoices INNER JOIN tblPart on tblInvoices.PartNumber = tblPart.PartNumber

Sorry, I am not so good at creating query code.

How would I input that code into Access?

nevermind, i found where to input the SQL

 

[memo]

Originally posted by: KLin
are you wanting to see the weight of the ordered quantity for that part number?

Here is a sample of a query that would work:

SELECT tblInvoices.PartNumber, tblPart.Weight, tblPart.Weight*qty As TotalWeight
FROM tblInvoices INNER JOIN tblPart on tblInvoices.PartNumber = tblPart.PartNumber

I think the code that you gave me worked to multiply the weights by the quantity. But, the TotalWeight field is ordered by part number sequentially. an example:

INVOICE TABLE
part number qty
19xxxxxxxx 3
12zzzzzzzzz 1

PART_NUMBER TABLE
part number weight
19xxxxxxxx 2lbs
12zzzzzzzzz 3lbs


TotalWeightField
12zzzzzzzzz 3lbs
19xxxxxxx 6lbs

I would want the Invoice Table to retain its order.

 

[KLin]

Originally posted by: memo

Originally posted by: KLin
are you wanting to see the weight of the ordered quantity for that part number?

Here is a sample of a query that would work:

SELECT tblInvoices.PartNumber, tblPart.Weight, tblPart.Weight*qty As TotalWeight
FROM tblInvoices INNER JOIN tblPart on tblInvoices.PartNumber = tblPart.PartNumber

I think the code that you gave me worked to multiply the weights by the quantity. But, the TotalWeight field is ordered by part number sequentially. an example:

INVOICE TABLE
part number qty
19xxxxxxxx 3
12zzzzzzzzz 1

PART_NUMBER TABLE
part number weight
19xxxxxxxx 2lbs
12zzzzzzzzz 3lbs


TotalWeightField
12zzzzzzzzz 3lbs
19xxxxxxx 6lbs

I would want the Invoice Table to retain its order.

so you want to see the qty also?