MS Access Query Problem

  Adam87 19:32 04 Sep 2005

I have run 3 queries on some tables and get the results back, which is fine. When I try to run another query on the first 3 queries it doesnt work properly.

The first 3 queries contain a list of codes. I need to run a query to find out how many of each code there are in each query. Eg The final query should look like:

Query 1 Query 2 Query 3
LX1 2 0 1
MC9 1 3 2
VR4 0 1 2

But what happens is that they are getting multiplied. If i try to run a query on just 1 of the first lot then it works. Id get the result seen above (minus the last 2 columns).
If i try it with the first 2 columns or any more than 1) from above the results become:
Query 1 Query 2
LX1 2 0
MC9 3 3
VR4 0 1

If the cell is empty/0 then it doesnt multiply but if there is something there in both queries it multiplies the result and puts it in both columns (eg. the second row).
Im using the count function in the qury design mode to get these results.

I hope this is clear. Dont really think it is but any help would be very much appreciated.

  52MikeH 06:58 05 Sep 2005

How does your SQL statement read for the querry ?

  Crunchy 09:18 05 Sep 2005

It looks like you may not be linking your queries incorrectly. Make sure Query1 lists a set of unique records and link the other queries to this query, 1 to many using the same field in each query to create the links. You may be linking Query1 to Query2 and Query2 to Query3 at present which will give you the effect you described.

  Adam87 11:54 05 Sep 2005

The SQL Statement is:

SELECT Speciality.[Speciality Code], Count([Jan 23-4-1].[Speciality Code]) AS [CountOfSpeciality Code], Count([Feb 23-4-1].Speciality) AS CountOfSpeciality, Count([Mar 23-4-1].Speciality) AS CountOfSpeciality1
FROM [Mar 23-4-1] RIGHT JOIN ([Feb 23-4-1] RIGHT JOIN (Speciality LEFT JOIN [Jan 23-4-1] ON Speciality.[Speciality Code] = [Jan 23-4-1].[Speciality Code]) ON [Feb 23-4-1].Speciality = Speciality.[Speciality Code]) ON [Mar 23-4-1].Speciality = Speciality.[Speciality Code]
GROUP BY Speciality.[Speciality Code];

I have the 3 queries linked to a table that holds a complete list of all the available codes.

  Rand Al Thor 13:20 05 Sep 2005

Are your dates really in that format?

  Adam87 13:29 05 Sep 2005

They arent dates. The first 3 letters refer to the month and the numbers are a code refering to target that has to be met.

  52MikeH 20:50 05 Sep 2005

Have you checked the links as Crunchy suggests ?
Crunchy is Access super guru !

  BigMoFoT 22:09 05 Sep 2005

agree - it's the way you are joining the tables in the query that is giving you an incorrect set of results.

How many tables do you have and what are the primary keys for each table.

  Adam87 22:12 06 Sep 2005

I have 1 table (a list of codes) and 12 queries (1 for each month). The queries havent got a primary key and the table's primary key is the Code.

Each of the monthly queries contains a list of codes (plus other details which arent needed for the final query). I need the final query to add up how many times each code appears each month.

By the way as far as linking goes I am linking each query to the table. ie. the Code field in each query is linked to the Code field in the table.

It works when I attempt 1 month. But as soon as I even just add the next query (not even linking it or adding any fields to the final query) it starts the multiplying effect I describe above.

  Crunchy 10:09 08 Sep 2005

Your strategy should be:-
1. Create your 12 queries to report totals for each code.
2. Create a report from your codes table to show each code.
3. Add each query to the report with Join Properties option 2 (All records from your table and only those from Query where join join fiels are equal.
4. Your report will show Code from table, CountMonth1, CountMonth2, etc.
Hope this helps to achieve correct result.

  Adam87 14:21 08 Sep 2005

Thanks everyone for all your help. Working now, think I was using the wrong join type. Easy when you know how.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Illustrator Charles Williams on how to create magazines and book covers

iMac Pro review

Les meilleures prises CPL (2018)