Joining tables problem

[Kyriakos]

Hey everyone. I have this situation: I work for a veterinary company and I have a project which includes 4 tables.The first one has a list of all of our clients.The other 3 are : a table for the year 2004, one for 2005 and one for 2006. Not every client visits us every year and I need a query which will make certain action (different sorts of calculations) with the number of animals that we take care of.

To make it more clear:
During 2004 one client with ID 12345 visited us with 3 animals.
He didn't show up in 2005 but he came back in 2006 with 4 animals.

Trying to do so I joined the tables properly (in my opinion one-to many) and I only get the sum if a client has visited us for all the 3 years. If he appears in just two tables or one I don't get a result.

This is the problem. Has anyone got a clue?

Thanks in advance
Kyriakos

 

[pbaldy]

That's not a joining tables problem, that's a too-many-tables problem. You should not have a table for each year; you should have one table with a date field.

 

[gemma-the-husky]

however, having 3 separate tables you need to union them, and then you can see them all in 1 table.

do 3 queries, one from each table giving you the columns you want - these will probably be name, date of visit, etc. All 3 queries HAVE to have exactly the same column structure.

then copy the firstquery to another query and call it queryname_union (or anything else.

Now Design the union query, and in the design window, click on the view type and select SQL

this wil lshow you the SQL for the first years data. what you need to do is UNION all 3 queries together, so now you open the second query, show the sql, and copy and paste it into this first sql, as follows

table1sql (delete the final semi-colon)

UNION

table2sql (delete the final semi-colon)

UNION

table3sql

This will give you a single consolidated query you can use in your forms etc. The Access visual design can't do this, it has to be done in SQL

 

[pbaldy]

That's a workaround for bad design, Gemma. I considered it, but fixing the design should be the priority. It's almost 2007: that means another new table; your UNION has to be changed; forms currently bound to 2006 have to be changed; etc, etc, etc. Bad idea.