I'm using Access 2007 on windows vista. Intel T7400 with 2.16 GHz and 2 gigs of ram. 64 bit os.
If I run a query that goes across multiple tables, I get sensible results. However, if I run a query in only one table, I get duplicate results. In fact I get over 300 repetitions of each result. This only occurs when I use multiple fields. The database I'm using is a large collection of info on boating accidents. I was trying to get a list of all prop injuries and their ages so I used field1=accident report number, field 2=prop injury with criteria="yes" and field 3 as age in my query. I got over 300 repetitions of each accident(duplic ates of the accident report number) in my results. 368 to be exact
SELECT DECSEDCGAll.[Accident Report No#], DECSEDCGAll_1.[Prop Injury], DECSEDCGAll_2.[Age of Victim]
FROM DECSEDCGAll, DECSEDCGAll AS DECSEDCGAll_1, DECSEDCGAll AS DECSEDCGAll_2
WHERE (((DECSEDCGAll_ 1.[Prop Injury])="yes"));
I really have no idea why this happens.
As I said, this doesn't seem to be a problem if I query accross tables. For example if I use field 1) prop injuries with criteria=-1 from the deceased table and field 2) vessel length from the vessel table, I get a nice table of all the different vessel lengths with no duplicates!
help?
Stephen
Help?
Thanks,
Stephen
3 2019 MMcCarthy 14,534
Recognized Expert Moderator MVP
Hi Stephen,
You are getting multiple results because you haven't created any join relationship between your instances of the table. Therefore you are getting every combination of possible results.
Why do you need three instances of the table?
Does this not give you the result you want, if not why? -
-
SELECT [Accident Report No#], [Prop Injury], [Age of Victim]
-
FROM DECSEDCGAll
-
WHERE ((([Prop Injury])="yes"));
Thanks, I realized my error after a couple decent nights of sleep!
MMcCarthy 14,534
Recognized Expert Moderator MVP
Thanks, I realized my error after a couple decent nights of sleep!
Isn't that always the way :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Giloosh |
last post by:
Hello, i need some help if possible...
i have a payments table with over 500 records
i want to run a query that searches through the table spotting out any
duplicate ID#'s and Dates.
So basically it will run a search spotting out duplicate ID#'s. Than
with in those ID#'s spot out duplicate dates.
I could do this by eye and spend hours...
|
by: medhanush |
last post by:
I have data in table as follows,
Num Category ProductName
---- -------- -----------
100 A Product1
100 B Product1
101 A Product2
101 B Product2
101 A Product2
|
by: ms |
last post by:
Access 2000:
I am trying to delete duplicate records imported to a staging table leaving one
of the duplicates to be imported into the live table. A unique record is based
on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and
BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since
records to be...
|
by: Jermin |
last post by:
Hi,
I have a database composed of a single table composed of 2 columns, an
auto-numbered ID column and a column which contains 30 million random
numbers. All I want to Access to do is check the numbers and let me
know if there are any duplicates. Ideally I'd like Access to flag the
duplicates if they exist. The easy approach that I thought...
|
by: sara |
last post by:
I have a procedure to automate bringing several Excel files into our
Access tables, on a daily basis.
The problem is that if the user has a problem, and tries to run the
import again (maybe 3 files imported then there was a data problem and
they want to re-import after fixing the problem), I can't get the Error
handling to fire if the user...
| |
by: skennd |
last post by:
Here's my problem in exact replication:
I have used the find duplicate query in Access, and the query
determined the following duplicate values by the following query:
In (SELECT FROM As Tmp GROUP BY HAVING
Count(*)>1 )
|
by: Manish |
last post by:
The topic is related to MySQL database.
Suppose a table "address" contains the following records
-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane |...
|
by: Iona |
last post by:
Hi Allan,
I'm using a nifty piece of code you put on here some time back to do a
duplicate entry check as below. I'm using to check for duplicate names.
However I am getting an error message on this line: Set rs =
db.OpenRecordset("SELECT ID FROM Contacts WHERE (" & sWhere & ");")
Contacts being the main table. I am using access 2003...
|
by: ramdil |
last post by:
Hi All
I have table and it have around 90000 records.Its primary key is autonumber field and it has also have date column and name, then some other columns
Now i have problem with the table,as my table contains duplicate entries for a particular date.How can i delete the duplicate entries from the table for that particular column,Now i am...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |