Problem with my query

[tforr]

Hi, can anyone help.

I have created a table within my database called stk_takewsk. Within this table are a number of fields. These are:

ID (P.K) - Autonumber
DateStamp - Date/Time
OperatorID - Text
Location - Text
ScanItem - Number
Quantity - Text
Description - Text
DIMUPDATE - Number (Default to zero)

A macro has been created and is used to pull data from a c.s.v file into this table.

I have linked another table that pulls external data accross from another db. The table is called dbo_stk_location. I need certain fields from this table for my qry.

I have created a query called stk_takewslQRY. The two tables from above have been added to this qry. Fields included are:

ID
DateStamp
OperatorID
ScanItem
Quantity
LOC_CODE
LOC_PHYSICAL
Expr1 -> [Quantity] - [LOC_PHYSICAL]
DIMUPDATE
LOC_STOCK_CODE

I have created a link between the two tables within my qry.

location.stk_takewsl => LOC_CODE.dbo_stk_location

i.e. if location.stk_takewsl = 01 and LOC_CODE.dbo_stk_location = 01 then the record will be shown in the qry.

ALSO

scanItem.stk_takewsl => LOC_STOCK_CODE

i.e. if scanItem.stk_takewsl = 1234TF and LOC_STOCK_CODE = 1234TF then the record will be shown.

PROBLEM I HAVE
**************

At the moment my qry displays the following. Please note that only some fields have been included here.

ID|OpID|Location|ScanItem|Quant|LOC_CODE|LOC_PHYSICAL|Exp1
1 TF1 01 1234TF 10 01 0 10
2 TF1 01 9999BA 15 01 0 15
3 TF1 01 1234TF 10 01 0 10


Ok, Let me explain. I have pulled accross three records into my stk_takewsl table using the marcro and opened my qry. The data above is diplayed. As you can see ScanItem 1234TF has been input twice. When I carry this data accross to my other database it see's the quantity as 10 rather then 20. QUESTION - Is there any way that a qry can be created to diplay only two records? like below:

ID|OpID|Location|ScanItem|Quant|LOC_CODE|LOC_PHYSICAL|Exp1
? TF1 01 1234TF 20 01 0 20
? TF1 01 9999BA 15 01 0 15

I am confused about the ID as the two records with the same ScanItem have different ID's

Sorry if I have confused anyone but good luck,

Regards,

Tom

 

[PHV]

I am confused about the ID as the two records with the same ScanItem have different ID's
And what about DateStamp, OperatorID and Location ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dafyddg]

Have you tried using the distinct keyword?

i.e.

SELECT DISTINCT ....

?

 

[lespaul]

Post the SQL for the query you already have, I have a feeling it just needs to be tweaked to SUM the Quant field. Or you can do it yourself by adding the SUM in the query designer.

HTH


Leslie