Queries with missing values

gpoindexter

New member
Local time
Yesterday, 20:26
Joined
Apr 29, 2010
Messages
4
I am new to access.
I am trying to write a query that will return the values in column "c" for each of 3 tables when column "a" and column "b" match. Access will only return values when there "a" and "b" appear in all tables. How should I write the query so that it will return the value for "a" and "b" when it does not appear in 1 or 2 of the tables but appears in the third?
I would like the output to be columns to be "a", "b", "c1", "c2", "c3", and if possible "sum c". I need the resulting output to contain all information that was included in the previous 3 tables.
Any help you could provide would be greatly appreciated.
 
You could do separate queries.
Are you saying you want col3 where colA & colB match on that table or for all tables?
If for that table then Qry1 would find all records where colA & colB match. and then output data.
Qry2 would do same for another table and qry3 & so on.
Then another qry will combine data from the original queries.

And then another query will sum your result.

If you want to select data where colA matches colA for another table then again the first qry would do this.

What I am saying is break the task down with individual queries and as you progress you may well be able to combine some or all of the queries and get the same result.

VBA could run through each table and accumulate the value for colC where ColA & B match this is beyond me.
 
Gpoindexter,

The specifics of how to go about this will depend on a few things. For example, do any of the 3 tables contain all possible values of a/b? Or do all 3 tables have a/b combinations that are not found in the other tables? Also, do all tables have entries in both a and b for all records, or are there some blanks?
 
Steve,
I do not believe that any of the tables represent a comlpete list all possible A and B combinations, although, I do not know for sure, because there are between 50,000 and 70,000 records in each table. All records have both an A and B.

Thanks for your help
 
PNGBill,
The method that you are discussing will not report the cases where A and B do not match a record in any other table. What I really need is the total of C for each A B pair. Some of the A B pairs, but not all, appear in multiple tables.

Gavin
 
What is A & B Pair? is it fred Smith (A) and Mary Smith (b) (C) is age of said person and you want all occurrences of fred smith and mary smith, even if one of them is in another table and then to sum their combined age?

Putting aside the question above, could you create a temp table of the three tables (just relevant Fields) and then query the larger temp table. Keep some link to the original so you know which "mary smith" you are dealing with when/if you need to refer a result back to an original table.

On the face of it your problem doesn't seem that difficult but I guess that is why it is.
 
Gavin,

I would proceed like this...

1. Make a new table with columns a, b, c1, c2, c3.
2. Make an Append Query based on each of the existing 3 tables, and run these appends to write the data into the new table, such that the c value from table 1 goes into c1 in the new table, c from table 2 goes into c2, etc.
3. Make a Totals Query based on this new table, to give you your totals. The SQL view of such a query will look something like this:
SELECT a, b, Sum([c1]), Sum([c2]), Sum([c3]), Sum(Nz([c1],0)+Nz([c2],0)+Nz(c2,0)) As cTotal
FROM YourNewTable
GROUP BY a, b

If this is a one-off requirement, then so be it. If it is a repeating task, then there are ways you can automate the process, by using VBA procedure or macros to manipulate the data into the new table.

(You could use a Union Query instead of a holding table for the combined data, but I think the table approach makes it easier to understand.)
 
Last edited:
Gavin,

I'm new here :) so my detailed reply ended up in an unintended position in the thread! Hope you find it.
 
G'day, Bob. Hope you are well. Yes, thought I'd pop over and see what happens in this part of the world.
 
Thank you, for your help. But a colliage of mine found a scprit that will preform combine the dbfs.

But in case you were wondering
A= bus route
 

Users who are viewing this thread

Back
Top Bottom