Both left and right joins possible?

[Dino]

I have table A with several fields of account info from 2005. Table B has the
same fields with possibly updated info from 2006. I have to keep the info
from both tables and conveniently display them. Right now I have a form that
displays the information I need in tabbed pages, one page for each year. The
form is based on a query that joins Table A and B.

This is fine except for one problem: the account number can change from year
to year. If the number changed from 2005 to 2006, and since the tables are
connected by a LEFT outer join, if I type in the 2005 account number it's
information will come up for 2005 and the 2006 page will have no info. That's
good, but if I type in the new number (2006) all I get is a blank screen as a
result. I need both LEFT and RIGHT joins if that's possible. Can I do this
somehow?

 

[Jeff Boyce]

Dino

?You have a separate table for each years data?! That sounds like a
spreadsheet, not a relational database.

?!You have a separate tab for each year?! Won't you run out of screen space
after you have a few more years' data? Isn't the maintenance of adding new
tabs a bit steep?

Based on your description, I suspect your data structure would benefit from
a bit more normalization. You won't get the best use of Access' features
and functions if you feed it spreadsheet-oriented data. Plus, it makes a
LOT more work for you!

Another way to approach this is to have an Account table, with an unchanging
rowID number, no matter what the user-friendly account number might show.
Include your "account info" fields.

If you need to keep "yearly" data, create a table that includes the rowID
from the Account table, and either a Date/Time field (if you need to keep
date and/or time info, or a text field, if you only need to keep "year" info
(i.e., 2003, 2004, 2005, 2006, ...)

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[Dino]

You know Jeff, you're absolutely right about the problems with the way my
data is set up. I am new to Access, and earlier this year I was assigned the
project of keeping track of, maintaining andupdating my section's data, not
to mention devising ways to make this data conveniently available to the
staff.

It worked fine when I only had one year's worth of data, now I see the
problems down the road. One issue is that I receive all my data for the year
in Excel format from other agencies and I can't change that. I have to
convert the data into Access.

I'll try your suggestion of a "rowID", thanks for responding. I will need
all the luck I can get.

Dino

 

[Jeff Boyce]

Dino

It is quite common for Access to receive "feeds" of Excel data. You are not
limited to storing the data in the same structure, though.

You can import (File | Get External Data ...) Excel data into what serves as
a "temporary" table (you are only using it to get the raw data in). From
there, you use one/more queries to parse the data into a well-normalized
structure, adding the records to your "permanent" Access tables.

Then you base your forms, reports, queries, etc. on the "permanent" tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dino]

OK, I get you. I'm still using the original tables that came from Excel for
my queries, forms and reports. I mean, to the user the resulting information
in the forms look good, but it's hard to maintain. I guess I need to figure
out what I need in the way of "Access-friendly" tables, and how to get there.