Combining records from many tables into one table

[R2D2]

I am working on a database that will allow my users to query financial records. I'm setting up the entire thing to use VBA code and forms. I'm running into an interesting problem that I haven't been able to think of a decent solution for, and I thought someone here might have some ideas. I'll need to explain the organization of our database first. Our financial records are stored by a combination of fund, orgn (organization) and acct (account) numbers. For example, one of my tables would have a record for fund 113002, orgn 5251 and acct 52003, or 113002-5501-52003 or any other combination of these. I have 5 different tables storing what the sum totals are for each combination of fund/orgn/acct, seperated into years (i.e. I have a table that shows this data for 1999, a table for 2000, and ones for 2001, 2002, and 2003). So, I need to make some queries that will allow my users to select any combination of these years, and create a table displaying the fund/orgn/acct combinations and the sum totals for each of the selected years. So, if they selected all 5 years, my table would have fields for fund, orgn, acct, 1999total, 2000total, 2001total, 2002total and 2003total.

The tricky part is that some combinations appear in some years but not in others. I need my table to have all the appropriate records, even if a specific fund/orgn/acct combination showed up in only one or two years. The problem is that doing an ordinary inner join will only pick up records that show up in all years. I know about outer joins, but I don't think it will help in this situation. One solution I've thought of (but don't really want to impliment, for obvious reasons) is to create a series of queries. I'd start with a make table query that found records in all 5 tables, then do a series append query (that would append to the table I just made) that found records in each combination of 4 tables, then do it for each combination of 3 tables, each combination of 2, and each seperate tables. I could do this - but it would need 31 seperate queries in order to accomplish this.

So, I'm bringing this problem up here with the hope that someone might have a solution. Is there someway that I can have each of my 5 tables join to the others in such a way that all the records from all the tables are kept, and anytime records match between tables, they're joined together into one single record?

 

[Jon K]

You can use VBA on a form to create the required table based on the years selected by the user. Since five tables are involved, VBA is more efficient than queries.

If the table names are uniform and contain the respective year, for example tbl1999, tbl2000, tbl2001, etc, you can even use a loop to simplify the code.


I have attached a small DB as a demo. The code is in the On Click event of the command button on the form. When two or more years are selected and the command button is clicked, a table tblCombined will be created.

Hope it helps.


The DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.

 

[R2D2]

Jon - this is EXACTLY what I was looking for. Thank you very much. I can tell you put a lot of effort into creating the example for me - I really appreciate it! I'm pretty experienced in VB/VBA, so all the VB parts make perfect sense to me. But I'm still trying to figure out why the SQL statements that the code creates works. For reference, here is one of the append SQL statements the code made:

"UPDATE tblCombined RIGHT JOIN [tbl2003] ON tblCombined.Acct = [tbl2003].Acct AND tblCombined.Orgn = [tbl2003].Orgn AND tblCombined.Fund = [tbl2003].Fund SET tblCombined.Fund = [tbl2003]!Fund, tblCombined.Orgn = [tbl2003]!Orgn, tblCombined.Acct = [tbl2003]!Acct, tblCombined.[2003Total] = [tbl2003]![Total];"

I haven't seen SET used in an SQL statement before...what exactly does it do? I can see that it works beautifully to add the record from tbl2003 to tblCombined, either appending it as a brand new record if that fund/orgn/acct combination isn't already in the table, or adding the 2003total column to the existing record if there is one...but how does the SET keyword work it's magic? Are there ways to use this in a regular Access query? I find myself having to combine records like this quite often, only it's normally only from 2 tables instead of 5. I've found ways to accomplish my task via 2 queries, a make table one and an unmatched append query....but this way seems so much easier and better.

I also hadn't seen the "Altar Table" part before, but I can see that that will come in handy as well.

Thanks again!

 

[Jon K]

>
...... SET used in an SQL statement ......what exactly does it do?
<


SET, when used in an update query, tells Access which fields to update and with what values. In its simplest form, the SQL statement of an update query is:-
UPDATE TableName SET Field1=aNewValue, Field2=anotherNewValue, ...


We can update and append records in one update query when we join the update query to another table with an outer join (here a right outer join).

 

[R2D2]

Sounds great. Is there a way to use the SET keyword in a regular Access Query (as opposed to doing it through a DoCmd.RunSQL code statement)?