A quick question, I’m hoping some Doper may be able to help me out with:
I’ve set up a Microsoft Access database for assets (“A” - “Z”), and the employees assigned (“1” - “20”) to that asset. I’ve got a form set up to display the asset information, and in a tab within that form, I have a subform to show the employees (and some of their data) assigned to that asset.
What I’m looking for:
A B C D E F . . .
1 2 3 4 5 6
7 8 9 10 11 12
13 14 15
16
What it seems that I’m getting now, is that each asset record is being displayed multiple times by the number of employees it has assigned:
A A A B B C C C C D D E E E F F . . .
1 1 1 2 2 3 3 3 3 4 4 5 5 5 6 6
7 7 7 8 8 9 9 9 9 10 10 11 11 11 12 12
13 13 13 14 14 14 14 15 15 15
16 16 16 16
I’ve looked in the macros and queries, but can’t find out where to tell it “no duplicates” or some such thing. I am CHAIRBORNE, and have a basic mastery of Access and such, but for some reason, this escapes me. . .
Does any Doper know what I’m missing here?
Tripler
Man. . . it took me forever to get that code spacing right.
Well, without seeing the tables & the SQL statements themselves, it’s hard to say exactly what’s going on. But typically when you get multiple records back, the problem is in the JOIN - multiple records in the joined table meet the criteria and are returned.
You fix it by fixing your groupings or the join criteria. I can’t really narrow it down farther than that without seeing your table definition and the SQL statement.
See, this is where my level of expertise runs and buries its head in the sand: where can I find the JOIN criteria, if I go into Design view of the form, to Properties > Form > Data, and open up the query thing?
Tripler
(I’m obliged for your help by the way . . . I’m learning already)
On the query design view, your tables that you pull your data from should be connected by lines, showing the field the tables are related by. It sounds like you have what’s called a cross join, which is graphically displayed in the query designer as no connecting line; you need an inner join, which you can create by clicking and holding on the asset ID in one table and dragging it to the asset ID in the other.
That’s a bit of a guess without seeing your actual query–one thing you can do for us DB people is change your query to SQL view and copy & paste the SQL to a post, then we could probably tell for sure. But that’s the most common cause for this sort of problem, especially for people who don’t know what a join is.
Also, in the query design view, try right clicking in the window that displays the tables and select Options. In the options that pop up check “Unique Values”.
That MAY help - it’s been a while since I’ve done this.
I think I’ve figured it out . . . I made my way to the form’s query setup (in design view), and found the JOIN lines as y’all indicated. I had three tables, joined twice by design: the asset was the main form. The asset was joined to an employee’s subform, as well as an ‘assigned department’ form. I think Access was having transistor-burn that I had two “joins” at the same time.
What I did was start a fresh query, and put that out to the employee subform I was using. I linked the new query to the old one and matched the “master” and “child” fields . . . and that seems to now be working fine.
So, instead of having one query joined twice, I have two queries linked by one data bit.
In other words: I don’t know what the hell I did, but I think it’s now working just fine. The ends justify the means. Remember that part in Old School where Will Ferrel gets up to the debate podium and rambles out an incoherent statement that wows everyone? Yeah, I feel like that now. . .
But y’all walking me through what a “join” statement is/was, and my gumption, I think we done fixed it.
