Link to home
Start Free TrialLog in
Avatar of davetough
davetough

asked on

query matches and not duplicates

code attached
this query displays serial numbers from TableCopy if match serial numbers from Table2-
 
problem: if serial number inputed 2 or more times-
Matches increase the number of records in Table2 and displays duplicate matches

is there a way it only matches one time- if same number inputed again- it does not display?

the data type is text- because- their will be letters in serial numbers-
thank you

SELECT Table2.[serial number] AS SerialNumber, TableCopy.[serial number] AS ScannedSerialNumber
FROM Table2 LEFT JOIN TableCopy ON Table2.[serial number]=TableCopy.[serial number];

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of davetough
davetough

ASKER

GREAT- thanks-
Hello Capricorn,
I wonder why this code works and then when I delete data in table and import new data into table- it tells me no current record when I try to run query
thank yo
did you check if both tables have matching records
yes - am trying to recreate problem in sample database to show you- I create query- works fine- then i have delete all data button and it also closes database. then when I open the database- I have import button to import new data-
Thats when problem of no current record- and something at first about the second 'From' statement-
thank you
i scan in same number to match and it does not help
here is sample of problem- it gives error i first got-
everything is fine untill - I import new data into table
thank you
T-DB.mdb
where is the data that you import?
I have it in an excel sheet on network and i have users enter data there and then hit a button in database to import it in-
Private Sub Command16_Click()
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "Table2", "\\JRSHOP\AAB\Excel\database\Table1.xls", True, "Table1!"
DoCmd.OpenForm "frmImport"
Me.Form.Refresh
Exit_CmdImportExcel_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
End Sub
where is the excel file?
on network next to database: here is copy
Table1.xls
you are importing a blank excel file ?
sorry only numbers in serial numbers field get filled in sheet beforecoming into database
Table1.xls