Duplicates in query

[jk42]

Hi,

I just ran a query off of a junction table for project design types. When I run the query, it comes back with 6,000 records, even though i only have 600 projects. Why does it come back with so many multiples? what am I doing wrong here?

Thanks!

 

[plog]

A junction table defines a many-to-many relationship. So, when you use it an a regular query (non-aggregate) you get all the records. If you can post some sample data from the relevant tables, including table and field names and then what data you would like returned; I can help you create a query to achieve it.

Use this format for posting data:

TableNameHere
Field1NameHere, Field2NameHere, Field3NameHere
David, 17, 4/6/2008
Sally, 99, 3/18/2003
Tim, 4, 2/2/2000

 

[jk42]

TblProject_Study Design
PK_Project_StudyDesignID, FKProjectID, FKStudyDesignID
1, 11.01,XXXX
2, 11.01,YYYY
3,11.01,ZZZZ
4,11.02,ZZZ
5,11.02,SSSS
6,11.03,SSSS
7,11.03,YYY

So, I want to be able to run a query and determine which projects have the study design of "XXX" AND "YYY" or maybe just "SSS"

The frustrating part of this is I have SO Many junction tables, so I really need to learn how to set these up right. DOes the information I provided make sense? I actually need to change my design and add yet ANOTHER junction table. Can you have too many junction tables? I'm starting to get worried! I have SO manY!!

Thanks for the help!

 

[plog]

Halfway there:

... and then what data you would like returned

 

[jk42]

Sorry, I thought I answered that. Management will ask me to find "Which projects used XXX, and YYY" and then I'll need to give them the projects and it's details.

 

[plog]

You answered it with words, I'm looking for data. Based on the sample data you have already provided, what will the data look like that is returned by the query you want created? No explanations, just data.

 

[jk42]

I think this is what you mean?

Project ID, Study Design,
11.01, XXXX,
11.02, XXXX
12.02,XXXX

Does this make sense? sorry, Im not very access smart!

 

[plog]

Has nothing to do with access. You have a collection of stuff (S), you want to manipulate it some how and end up with a result (R). You gave me S, now what does R look like?

The result data that you provided doesn't make sense. For example, you want the result to contain 11.02, XXXX. However, 11.02, XXXX doesn't exist in your intial data set.

Let's try this. I will provide S and you tell me what R should be:


TblProject_Study Design
PK_Project_StudyDesignID, FKProjectID, FKStudyDesignID
1, 11.01,XXXX
2, 11.01,YYYY
3, 24.11, BBBB
4, 11.01, SSS
5, 36.07, ZZZZ
6, 38.91, AAAA
7, 4.22, YYY

When you feed this data to the yet-to-exist query, what does it return to you? No words, spit back data at me.

 

[jk42]

Seriously, I'm horrid at this stuff... so hopefully I'm answering you right this time..

2, 11.01,YYYY
7, 4.22, YYY

I'm just trying to be able to tell my boss which projects were "yyy" or whatever the criteria may be.

Thanks for the help!

 

[jk42]

Argh! Just ran a completely different query and it's doing the same thing. I'm still so confused!!!!

 

[plog]

First, you need to pick one example and stick with it. In your initial post you mentioned returning records with FKStudyDesignID of "XXX", "YYY" and "SSS". However in your response to my question of what should be returned you included FKStudyDesignID of only "YYYY" and "YYY". Your not being consistent in what you want your query to return. If you keep moving the target I can't help you.

 

[way2bord]

Seriously, I'm horrid at this stuff... so hopefully I'm answering you right this time..

2, 11.01,YYYY
7, 4.22, YYY

I'm just trying to be able to tell my boss which projects were "yyy" or whatever the criteria may be.

Thanks for the help!

Given:

TblProject_Study Design
PK_Project_StudyDesignID, FKProjectID, FKStudyDesignID

create query design > View SQL > paste code below

SELECT a.PK_Project_StudyDesignID, a.FKProjectID, a.FKStudyDesignID 
From [TblProject_Study Design] as a 
Where a.FKStudyDesignID like "*" & [Enter yyy criteria] & "*"

Not elegant, but answers your question exactly as you requested. If you need further help, specify your request.

 

[jk42]

Plog,
So sorry. I'm not trying to be difficult, I think i just don't completely understand what it is I'm not supplying. And it could be that I'm way over thinking. After re-reading it I see that I wasn't very clear. I am going to spend some time in Access re-creating the queries that stumped me and try to post my issues in a more clear manner.