primary key issues?

[jaash2000]

PROBLEM: my query has duplicate because my group by itself is not unique... I want it to use BOTH to create unique items. And I need ALL the items in the groups table (even if there isn't s record in the other query)



I am new to access and understand the basics (no programming skills yet). I have a table of "groups". The primary key is TWO fields (group and district). I want to link it with a two queries and bring the data together. The problem is that for the groups that are in two districts, it's doubling the #'s. It doesn't seem to be using BOTH as the primary, only the one join (groups). I can't figure out how to tell it to use BOTH the group and district to select the information. The join properties only let's me select one... I've attached a picture of what I've done.

[ranman256]

This is where autonumber comes in handy. Rather than make multi- keys.

[Bulzie]

Add another Left Join on District to both tables?

Not sure why you have 2 tables TBL_HEE_CY_TOTALS and TBL_HEE_PY_TOTALS with the exact same fields(just different data type?) Seems you could have 1 table but add a field called RecType or something and in there put if it is a "CY" or "PY" type or record.

[John_G]

There's nothing to prevent you from using both group # AND district in the joins used in the query.

Why are you using a LEFT (or OUTER) join?

[jaash2000]

Even when I listed both, it still gave me duplicates.

[jaash2000]

this data came from others, i can't auto# their stuff (I don't think). Like I said, I am new. Not sure how auto #'s helps...

[jaash2000]

This data came from others tables. I am trying to figure out how to combine. Ultimatley what i want is what you said, All the fields and then CY (current year) and PY (prior year). The problem is that some of the groups OVERLAP in some regions so the unique indicator for group is fine UNTIL I get to subtotal the regions, THEN the group shows up TWICE and it should only show up once (under the group and district it was listed with). Am I making sense? I know it's complicated. I also found the database relationships and i was able to create the joins but that hasn't help either.

[John_G]

... THEN the group shows up TWICE and it should only show up once...

If a group is in two regions, then it's bound to show up twice - once for each region. But if you only want it to show up once, how do you know which region to put it in?

If I understand you correctly, you want to combine the data from both the "TOTALS" tables in to one. However, if you do that, the PK of the combined table cannot be just Group# and District, because you will usually have one from each of the CV and PV tables.

Can you explain a bit more about just what you want to accomplish? What is the table tbl_HEE_Groups for? I don't see anything in it that is not is each of the other two.

[jaash2000]

It's ok to show up twice (and actually I want it to) but it's COUNTING (i.e., countofacct) twice is the problem.

So I have group 12345 that has 3 "enrollments" in district 67. I know this based off the Group # and District/region #.
Group 12345 COULD have (it doesn't currently but could) "enrollments" in District/region 89 as well.
When I count the 3 in district 67, it ALSO shows as 3 in 89 (which is incorrect and duplicating).
I need it to look at not just the group but also the district.
Sorry, I am probably making it more complicated than it has to be but I'm coming into all the data late in the game so to speak and I'm certainly not advanced in access.
I appreciate all your insights and help into this!!!!!

[Bulzie]

Maybe try a UNION query on TBL_HEE_CY_TOTALS and TBL_HEE_PY_TOTALS.

1. Create a new query and in SQL design paste the following and save. Run it and make sure it includes records from both tables. This should work as it looks like you have exact same fields in both tables.

Select * From TBL_HEE_CY_TOTALS
UNION ALL
Select * From TBL_HEE_PY_TOTALS
Order By Group, District

2. Create a second query, add the Union query above as the data source, enable Totals button in ribbon, add fields Group and District. Groupby on Group and District, then add another District column and do a Count or whatever you need to do.

[jaash2000]

i am working on some training but don't understand how to use sql yet... is that fairly easy to do?

[Bulzie]

It's Access VBA code, not SQL. They are similar but many differences also. In Access when you create a query, don't select a table. Then in top left click on SQL view. Another tip for learning VBA coding is when using the Query Designer, after you create your query and return the data you want, click on the SQL view to see how the VBA is coded.