Combining select queries produces dupes

[Yoni Hasid]

I am new to MS Access and need help. I have built multiple select quieries and when I am joining them into one query I get dupes. Question: how do I eliminate dupes on joint?

Here is a sample of one of the select query:

SVP Account Name DOB CountOfEmpID
Name Account P¥FFN-N 13
Name Account P¥FFN-Y 19354


Another select query:

SVP Account Name PFName CountOfEmpID
Name Account P¥FSN-Y 3482
Name Account P¥FSN-N 15885


Here is what happenes when I join them together:

SVP Account DOB CountDOB PFName CountofEmpID
Name Account P¥DOB-N 19354 P¥FFN-N 15885
Name Account P¥DOB-Y 13 P¥FFN-N 15885
Name Account P¥DOB-N 19354 P¥FFN-Y 3482
Name Account P¥DOB-Y 13 P¥FFN-Y 3482

[Stewart Ross]

If you could post the SQL you are actually for both SELECT queries and the one you are trying to get to JOIN it would help us to see what is actually going on.

Normally, if you are joining two select queries and duplicates result you have a missing join in your query.

I can't tell from what you've posted as the lines you list do not contain test values within them - Name and Account are repeated on each line, which does not help us to know what they represent, and what might be a date of birth according to the heading (DOB) is not recognisable in the way you've listed it (P¥DOB-Y).

-Stewart

[Yoni Hasid]

Thank you. Here is the SQL for the PDOB query:

SELECT [AccountName Query].[Ops SVP], [AccountName Query].Account, [AccountName Query].PDOB, Count([AccountName Query].EmpID) AS CountOfEmpID
FROM [AccountName Query]
GROUP BY [AccountName Query].[Ops SVP], [AccountName Query].Account, [AccountName Query].PDOB;

Here is the SQL for PFirstName query:

SELECT [AccountName Query].[Ops SVP], [AccountName Query].Account, [AccountName Query].PFirstName, Count([AccountName Query].EmpID) AS CountOfEmpID
FROM [AccountName Query]
GROUP BY [AccountName Query].[Ops SVP], [AccountName Query].Account, [AccountName Query].PFirstName;

Here is the SQL for the joint query:

SELECT [P-DOB].[Ops SVP], [P-DOB].Account, [P-DOB].PDOB, [P-DOB].CountOfEmpID, [P-FirstName].PFirstName, [P-FirstName].CountOfEmpID
FROM [P-DOB], [P-FirstName], [P-LastName], [P-MiddleName];

[Yoni Hasid]

Please see the response above. Sorry for confusion

[Stewart Ross]

Thanks for posting the SQL. As I suspected, there are no actual JOIN statements in in the final query. As there are four queries or tables in the FROM statement you will be combining all rows from all four tables together, effectively multiplying the number of rows accordingly (so if each table has 3 rows, say, then the number of rows in the output would be 3 * 3 * 3 * 3 = 3^4 = 81 rows).

In the Access query editor you need to drag a join from one table/query to the next so that all four queries are inner joined on the primary key field, which is the Account one I would guess.

In SQL it is the equivalent of using

SELECT { list of your fields as before }
FROM [P-DOB] INNER JOIN [P-FirstName] ON [P-DOB].Account = [AccountName Query].Account [P-LastName] (INNER JOIN ... {and so on}

Access will do the join SQL for you if you simply drag from each Account field to the next in the query editor window.

-Stewart

[Yoni Hasid]

Unfortunately, this did not help. I still have dupes in a query. They were just rearranged. Is there another way to troubleshoot my query?

[Yoni Hasid]

May be this will help. I have a query with different fields representing Employiee ID, Account Name, Date of Birth, First Name, Last Name etc. In certain fields I have data that shows format of input that came from the specific application.So, DOB will say something like P¥DOB-Y, and first name will say P¥FFN-N. Also, Y and N at the end of each such code means if the client filled in or not this data. What I am trying to accomplish, to count in each column(field) totals of each Y and N entries and then generate a report. I was not able to get total counts in one query that is why I separated each field into separate query and I did get the totals that way. However, the challenge is to connect this select queries with each total and then create a report.

Here is SQL of the original query with all; entries. Perhaps there is a simpler way to get what I am trying to achieve?

SELECT AccountName.ID, AccountName.PCC, AccountName.Account, AccountName.EmpID, AccountName.PFirstName, Count(AccountName.PFirstName) AS CountOfPFirstName, AccountName.PMiddleName, AccountName.PLastName, AccountName.PDOB, AccountName.PGender, AccountName.[R-Field First Name], AccountName.[R-Filed Middle Name], AccountName.[R-Filed Last Name], AccountName.[R-Filed DOB], AccountName.[R-Filed Gender], [Master - Consolidated].[Ops SVP], [Master - Consolidated].[AM SVP (Global)]
FROM AccountName LEFT JOIN [Master - Consolidated] ON AccountName.Account=[Master - Consolidated].[Scode Description]
GROUP BY AccountName.ID, AccountName.PCC, AccountName.Account, AccountName.EmpID, AccountName.PFirstName, AccountName.PMiddleName, AccountName.PLastName, AccountName.PDOB, AccountName.PGender, AccountName.[R-Field First Name], AccountName.[R-Filed Middle Name], AccountName.[R-Filed Last Name], AccountName.[R-Filed DOB], AccountName.[R-Filed Gender], [Master - Consolidated].[Ops SVP], [Master - Consolidated].[AM SVP (Global)];

[Stewart Ross]

Hi. You say that you still have duplicate rows; this in turn is telling me that your totals and select queries are not returning single rows for each unique identifier, which they MUST do if you hope to join them on that single identifier.

I have no idea why you need to have separate queries to extract names, DOBs etc for your accounts - this is not an approach I can recognise as valid, but then I have no idea how your application's tables are structured.

If you could post data that is more representative of actual data from your tables - changing names and IDs as appropriate - this would help me to see the structure of what you are recording. You need to retain the parts that are different for each row - don't make them all show as 'Name' and 'Account' as in post 1, which just obscures how your rows are structured.

Other than that I have no suggestions to offer at present, as I think the approach of having separate queries to return fundamental items such as the account holder's name and DOB is fundamentally incorrect and is so far off normal practice as to make it very difficult for me to suggest a way forward for you.

-Stewart

[Yoni Hasid]

Thanks for your time. I attached a file with my query. Essentially, this file will contain over 100 diff accounts indicating similar data. What I am trying to accomplish, to total entries in coded fields to show how many records have data with Y and N ( at the end of code) and built a summary report that will show SVP,Account Name, and summary totals of those fields. Please let me know if you need additional details to understand this.

[Stewart Ross]

The example below is one way to return totals for each grouping (in this case grouped by two fields, [Ops SVP PCC] and [Account EmpID] which I take it are forename and surname.

All that is being done is that for each grouping we test whether or not the current field has a "Y" or an "N" at the end of it. This is the bit inside the IIF statement, which is then returning a 1 if there is or a 0 if there isn't. We then SUM these over the whole group to produce the total number of "Y" or "N" characters ending each field:

SELECT X.[Ops SVP PCC], X.[Account EmpID], 
  Count(*) as N,
  Sum(IIf(Right([R-FIeld],1)="Y",1,0)) AS [RF-Y Total], 
  Sum(IIf(Right([R-FIeld],1)="N",1,0)) AS [RF-N Total], 
  Sum(IIf(Right([PGender],1)="Y",1,0)) AS [PG-Y Total], 
  Sum(IIf(Right([PGender],1)="N",1,0)) AS [PG-N Total], 
  Sum(IIf(Right([FirsRT],1)="Y",1,0)) AS [FR-Y Total], 
  Sum(IIf(Right([FirsRT],1)="N",1,0)) AS [FR-N Total], 
  Sum(IIf(Right([N-Failmede],1)="Y",1,0)) AS [NF-Y Total], 
  Sum(IIf(Right([N-Failmede],1)="N",1,0)) AS [NF-N Total], 
  Sum(IIf(Right([MidRdl-eF],1)="Y",1,0)) AS [MR-Y Total], 
  Sum(IIf(Right([MidRdl-eF],1)="N",1,0)) AS [MR-N Total], 
  Sum(IIf(Right([NileadmeLasRt],1)="Y",1,0)) AS [NL-Y Total], 
  Sum(IIf(Right([NileadmeLasRt],1)="N",1,0)) AS [NL-N Total], 
  Sum(IIf(Right([DOBR-Filed Gender],1)="Y",1,0)) AS [DOB-Y Total], 
  Sum(IIf(Right([DOBR-Filed Gender],1)="N",1,0)) AS [DOB-N Total]
FROM [YourQuery] AS X
GROUP BY X.[Ops SVP PCC], X.[Account EmpID];

The FROM clause usess the name [YourQuery] as a placeholder; you would need to replace this by the actual name you have given to the left-joined query you posted in post 7 which lists all entries. I have aliased the YourQuery entries as X for brevity above.

This produces the following results on the test data you supplied:

Ops SVP PCC  Account EmpID  N  RF-Y Total  RF-N Total  PG-Y Total  PG-N Total  FR-Y Total  FR-N Total  NF-Y Total  NF-N Total  MR-Y Total  MR-N Total  NL-Y Total  NL-N Total  DOB-Y Total  DOB-N Total
George       Smyit7hyd      8           0           8           8           0           0           8           0           0           0            0          0           0            0            0
Marty        Tip            8           8           0           7           1           0           8           0           8           0            0          0           0            0            8
Mary         Jane           6           3           3           2           4           0           5           0           3           0            0          0           1            0            3
 

It may give you some ideas for how you can proceed in a slightly simpler way with what you want to do.

-Stewart

[Yoni Hasid]

Thank you so much. I just wanted to ask for clarification. What you proposed is an additional query that I will use with my mane query in response 7? I am a little not clear on what I need to replace [YourQuery] in your SQL above. Also, would you please clarify what X is alied to? Thanks again !

[Stewart Ross]

The use of "X" is internal to the query - you can leave this just as it is. All it does is to rename a longer name with a shorter one to make reading the query and referring to fields within that query easier.

You need to replace the one occurrence of the text "[YourQuery]" with the actual name of the query that you posted in response 7 - in other words, the name of the query as you see it when you look in the Queries tab within Access, enclosed in the [square brackets] if there is a space between parts of the name.

-Stewart

[Yoni Hasid]

I think I am close to what I need. I was able to recreate your SQL in my query, however when I generated a new one and coded with all my fields, I do not get any result. I only receive error that my SELECT STATEMENT is incorrect or missing something. I checked over and over and cannot determine what I miss....I am not giving up yet...
Here is my latest SQL Statement based on your suggestion:

[Yoni Hasid]

Apologies. Here is the correct SQL that I wanted to post:

SELECT AccountName_Query.[Ops SVP PCC], AccountName_Query.[Account EmpID],
Count(*) as N, 
  Sum(IIf(Right([PFirstName],1)="Y",1,0)) AS [PFirstName-Y Total],  
  Sum(IIf(Right([PFirstName],1)="N",1,0)) AS [PFirstName-N Total],  
  Sum(IIf(Right([PMiddleName],1)="Y",1,0)) AS [PMiddleName-Y Total],  
  Sum(IIf(Right([PMiddleName],1)="N",1,0)) AS [PMiddleName-N Total], 
  Sum(IIf(Right([PLastName],1)="Y",1,0)) AS [PLastName-Y Total],  
  Sum(IIf(Right([PLastName],1)="N",1,0)) AS [PLastName-N Total],
  Sum(IIf(Right([PDOB],1)="Y",1,0)) AS [PDOB-Y Total],  
  Sum(IIf(Right([PDOB],1)="N",1,0)) AS [PDOB-N Total],
  Sum(IIf(Right([PGender],1)="Y",1,0)) AS [PGender-Y Total],  
  Sum(IIf(Right([PGender],1)="N",1,0)) AS [PGender-N Total],  
  Sum(IIf(Right([RFirstName],1)="Y",1,0)) AS [RFirstName-Y Total],  
  Sum(IIf(Right([RFirstName],1)="N",1,0)) AS [RFirstName-N Total],  
  Sum(IIf(Right([RMiddleName],1)="Y",1,0)) AS [RMiddleName-Y Total],  
  Sum(IIf(Right([RMiddleName],1)="N",1,0)) AS [RMiddleName-N Total],
  Sum(IIf(Right([RLastName],1)="Y",1,0)) AS [RLastName-Y Total],  
  Sum(IIf(Right([RLastName],1)="N",1,0)) AS [RLastName-N Total],   
  Sum(IIf(Right([RDOB],1)="Y",1,0)) AS [RDOB-Y Total],  
  Sum(IIf(Right([RDOB],1)="N",1,0)) AS [RDOB-N Total],  
 Sum(IIf(Right([RGender],1)="Y",1,0)) AS [RGender-Y Total],  
 Sum(IIf(Right([RGender],1)="N",1,0)) AS [RGender-N Total],    
FROM [AccountName_Query] AS AccountName_Query
GROUP BY AccountName_Query.[Ops SVP PCC],AccountName_Query.[Account EmpID];

[Stewart Ross]

You have taken out the alias "X" which I put in and replaced it with the same name as the query itself:

FROM [AccountName_Query] AS AccountName_Query

You can't use an alias of the same name as the underlying query. Just remove the AS part altogether:

FROM [AccountName_Query]

-Stewart

[Yoni Hasid]

I tried both ways with your suggestion for X alias and then without and both ways I get the following error: "The SELECT Statement includes a reserved word or an argument name that is mispelled..."

Never the less, I think it worked after I checked all the field names and syntax! You are brilliant!!!

I am going to spot check the date to make sure it captured all and then I will move onto building a report. Hopefully, it will be easier.. Thanks again!