Solved

query matches and not duplicates

Posted on 2011-02-11
12
296 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:davetough
  • 7
  • 5
12 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34871676
try this query

SELECT Table2.[serial number] AS SerialNumber, TC.[serial number] AS ScannedSerialNumber
FROM Table2 LEFT JOIN
(Select Distinct [serial number] From TableCopy) TC
 ON Table2.[serial number]=TC.[serial number];
0
 

Author Closing Comment

by:davetough
ID: 34871724
GREAT- thanks-
0
 

Author Comment

by:davetough
ID: 34872115
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
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34872238
did you check if both tables have matching records
0
 

Author Comment

by:davetough
ID: 34872391
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
0
 

Author Comment

by:davetough
ID: 34872463
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34872592
where is the data that you import?
0
 

Author Comment

by:davetough
ID: 34872639
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34872663
where is the excel file?
0
 

Author Comment

by:davetough
ID: 34872746
on network next to database: here is copy
Table1.xls
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34872774
you are importing a blank excel file ?
0
 

Author Comment

by:davetough
ID: 34872838
sorry only numbers in serial numbers field get filled in sheet beforecoming into database
Table1.xls
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question