Union Query

[aftabn10]

I have ran a union query that combines two tables but i now need to combine the duplicates as 1. The following are the results i get and the issues that i am having:

ID | Name | Pass | Fail
10 | AAA | 10 | 2
10 | AA | 10 | 2
| BBB | | 13
23 | bbb | 89 |
40 | A B | | 2
40 | A | 29 |

i have some duplicate results for the same person but due to some differences this is showing me duplicate results.

Is their anyway I can write an if argument that would check either the ID or Name to see if this matches then to combine the results like the following:

ID | Name | Pass | Fail
10 | AAA | 10 | 2
23 | bbb | 89 | 13
40 | A B | 29 | 2

If somebody could please help. Thanks in advance.

 

[Rik_StHelens]

I have ran a union query that combines two tables but i now need to combine the duplicates as 1. The following are the results i get and the issues that i am having:

ID | Name | Pass | Fail
10 | AAA | 10 | 2
10 | AA | 10 | 2
| BBB | | 13
23 | bbb | 89 |
40 | A B | | 2
40 | A | 29 |

i have some duplicate results for the same person but due to some differences this is showing me duplicate results.

Is their anyway I can write an if argument that would check either the ID or Name to see if this matches then to combine the results like the following:

ID | Name | Pass | Fail
10 | AAA | 10 | 2
23 | bbb | 89 | 13
40 | A B | 29 | 2

If somebody could please help. Thanks in advance.

If there are discrepancies in the names etc that would suggest the names are input manually more than once?

I'd revise the linking of your tables so that names are pulled into a recordset using ther pupils ID number. This way you wont get the discrepancies in your data.

If this isn;t the case though, and i've misread the situation then please say.

 

[namliam]

A normal union will not show duplicates, "Union all" will...

 

[aftabn10]

thanks for your replies Rik_StHelens and namliam.

rik, the problem being is that the data in the two tables are coming from different sources hence the problem of the different conventions of name usage occurs.

namliam, i have used a normal UNION rather than a UNION ALL.

 

[Rik_StHelens]

its probably easier in the long run to remove duplicates before you run the query.

post your sql.

im no expert, but im picking things up every day (mainly off these forums) and i might have a solution for you, but i need to see where your data is coming from

 

[namliam]

10 | AAA | 10 | 2
10 | AA | 10 | 2

is replaced by
10 | AAA | 10 | 2

AA <> AAA, if you make them absolutely to be equal.... Union will clean them up for you...
In effect you are doing a agregate query though.... You need the union first, then use a normal "group by/sum" query to get the totals on one line.

However even with a Aggregate query AA and AAA dont mix...

 

[aftabn10]

namliam, i have ran another query on the back of the union query, but when summing the agent name i get a data type mismatch in criteria expression. So i tried this on the pass and fail columns but it still does not work? where am i going wrong?