Duplicates in Query but not Table

[july1]

Hi friends,

I was doing an Access project and when I did a query for the Table "Customers" with primary key set to ID, there are duplicate values for my query.
However, there are no duplicate values for my "Customers" table. Tried using Query to find duplicates but to no avail.

How can I delete the duplicate values under queries / tables (where can I even find the duplicates?)?

Besides, sometimes when I add in a record under Table "Customers", the entry will be deleted after I press enter / go to the next entry.
Thus, I reentered the entry but on a different ID number (since it runs on auto number).

Is this a common problem of access?

[alansidman]

Post your SQL statement and what you are attempting to achieve.

[july1]

Hi alansidman,

I want to filter customers with transaction dates before 2001.
But I have duplicate values of ID.
When searched the Table, there are no duplicates. Thus I am confused.

SQL:

SELECT [Customer Details].ID, [Car Details].[Registration Date], [Customer Details].[Vehicle Number], [Car Details].[Car Make], [Car Details].[Car Model], [Car Details].[New/Used], [Customer Details].[First Name], [Customer Details].[Last Name], [Customer Details].Gender, [Customer Details].Address, [Customer Details].[Postal Code], [Customer Details].[Mobile Number], [Customer Details].[Alternate Number], [Customer Details].[NRIC/FIN], [Customer Details].DOB, [Customer Details].[Email Address], [Customer Details].Remarks
FROM [Car Details] INNER JOIN [Customer Details] ON [Car Details].[Vehicle Number] = [Customer Details].[Vehicle Number]
WHERE ((([Car Details].[Registration Date])<#1/1/2003#));

[June7]

Can customer have multiple cars?

If you want to provide db for analysis, follow instructions at bottom of my post.

[alansidman]

In addition to June's question, can a car have more than one owner listed in the customer file. Spouses both listed as separate records? Father/Son? etc.

[orange]

How about telling us about the business related to this database and query?
Using simple English, clearly give us an overview of a day at the office in terms of Cars and Customers.

Here is sql for the query --formatted by PoorSQL

SELECT [Customer Details].ID
	,[Car Details].[Registration Date]
	,[Customer Details].[Vehicle Number]
	,[Car Details].[Car Make]
	,[Car Details].[Car Model]
	,[Car Details].[New/Used]
	,[Customer Details].[First Name]
	,[Customer Details].[Last Name]
	,[Customer Details].Gender
	,[Customer Details].Address
	,[Customer Details].[Postal Code]
	,[Customer Details].[Mobile Number]
	,[Customer Details].[Alternate Number]
	,[Customer Details].[NRIC/FIN]
	,[Customer Details].DOB
	,[Customer Details].[Email Address]
	,[Customer Details].Remarks
FROM [Car Details]
INNER JOIN [Customer Details] ON [Car Details].[Vehicle Number] = [Customer Details].[Vehicle Number]
WHERE ((([Car Details].[Registration Date]) < #1 / 1 / 2003 #));

Many recommend that you do not use names with embedded spaces or special characters ('/'). Using such will cause you syntax errors --sooner or later.

[july1]

Yes, a car can have multiple owners - changed hands
No, spouses will not be listed. only the registered car owner.

[july1]

yes, customer can have multiple cars.
is that for both excel and access?

[july1]

we are in a car dealer business. we buy in new and used cars and of course, sell to other people. i will want to include all the information related to the car and customer.
initially i created 2 tables - car and customers - and linked it up via the vehicle plate.
but as i was keying in the data, and pressed 'delete' by accident or sometimes simply a 'space', the whole row of data will disappear. as such, i will have to reenter the data but my auto number function will continue running (instead of 100, it is now 101). this is my main concern.

i believe that this is related to the problem of data duplication that appeared under queries and not the table. i have solved this by exporting out to excel and importing it back to access but not the former.

'Many recommend that you do not use names with embedded spaces or special characters ('/'). Using such will cause you syntax errors --sooner or later.'

i should just use 'CustomerDetails' instead of 'Customer Details'?
what difference does it make?

[June7]

Names with spaces and/or punctuation/special characters will always require to be enclosed in [ ] when referenced. Believe me, someday you will forget and suffer the consequences.

Access will often try to supply the [ ] (in queries mostly) if you don't but can't distinguish name if it has spaces etc. VBA will not try to supply missing [ ].

I have encountered situation where the [ ] was required regardless and had to be deliberately typed but I can't remember exact circumstances.

Vehicles can have more than one owner named on title.

Do you care about history of car ownership or just the current owner?

An autonumber type field primary key is not supposed to have meaning to users and therefore gaps in sequence should be disregarded. Not really understanding how this would contribute to duplicate ID in query.

[orange]

I'm not sure what your tables and relationships are, but you appear to have a M:N relationship. Also called many to many.
That is,
a Car may have 1 or Many Owners, and
an Owner may own 1 or Many Cars


the Many here means at the same time. This 2016 Chev is owned by John Doe and Patty O'Khair.

In relational database, this is resolved using a junction table, also called linking table or mapping table or associative entity.

Here's a related article and another.