Auto join in access 2007

[David Z]

I have checked AutoJoin in the Access Options, however, when i place two
tables w/ identical primary keys, it does not auto join them. It appears to
be not working. Any help will be greatly appreciated.

 

[Jeff Boyce]

David

It would be fairly rare to have "two tables w/ identical primary keys".
This only happens when subtyping.

Wouldn't you want to have that join between a primary key in one table and a
foreign key (field) in a second?

More info, please... (an example would help)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[David Z]

I have around 140 tables w/ the same primary key. Client's choice. Anyway, to
make a short story, they got used to seeing the data in Access 2003 autojoin
the tables when they created a query. We have upgraded to Access 2007 and
that seems to not be working. I know, it is a simple matter to join them on
the fly, but clients......

This is a data file that is updated each time it is opened. A link is
created to a SQL server and the tables are downloaded locally. This enables
them to play with the data without touching the SQL data.

Thanks in advance.

 

[John W. Vinson]

I have around 140 tables w/ the same primary key. Client's choice.

Eeeeeuuuuwwwwwwww....

This is a classic case of The Client Is Not Always Right.

I'd have given them one (concealed if need be) table, with 140 Queries
masquerading as tables, but I know this isn't always possible.

Anyway, to
make a short story, they got used to seeing the data in Access 2003 autojoin
the tables when they created a query. We have upgraded to Access 2007 and
that seems to not be working. I know, it is a simple matter to join them on
the fly, but clients......

This is a data file that is updated each time it is opened. A link is
created to a SQL server and the tables are downloaded locally. This enables
them to play with the data without touching the SQL data.

Well, 140 SQL Views then!!!!!

 

[Jeff Boyce]

I still don't have a sense of what data is being stored in those tables, so
I can't offer ideas about how (or even if) using "the same primary key"
would work.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Bob Barrows]

I have checked AutoJoin in the Access Options, however, when i place
two tables w/ identical primary keys, it does not auto join them. It
appears to be not working. Any help will be greatly appreciated.

I have reservations about the database design as well, but I will try to
answer the question despite not having installed A2007.
In A2003, in Tools>Options, on the Tables/Queries tab, there is a
checkbox labeled Enable AutoJoin. Is there a corresponding option in
A2007 and, if so, do you have it checked?

 

[Bob Barrows]

I have checked AutoJoin in the Access Options, however, when i place
two tables w/ identical primary keys, it does not auto join them. It
appears to be not working. Any help will be greatly appreciated.

Oh duh ... ignore my last reply - I failed to read your post completely.

 

[David Z]

The reason for how/why the data is being stored is completely irrelevant to
the question, which is:

Does the Auto Join feature work in Access 2007? The exact database worked
fine in Access 2003. I found where the AutoJoin option is located in the
Access Options and confirmed that it was turned on.

This is the situation:

The data is housed in a Microsoft SQL server. When the application is
opened, the existing tables are deleted and the current versions of the data
tables are imported.

Each table contains monthly data for a specific accounting category, or
group of categories, by company. The primary key in each table is the company
id. Each table has a record for every company, even if they are zeros.

The client does their own data analysis using this copy of the data.
Historically, when they created their own queries (Access 97, 2002, & 2003),
the tables would auto join themselves by the primary key. Agreed, this is a
minor issue for someone that uses Access all the time, however, these clients
have minimal experience, and are used to this feature working.

I agree that there is other ways to store this data, however, this is the
way the person signing the check wants to see it.

Thanks in advance for all responses.

David

 

[Jeff Boyce]

David

Since you're asking for folks ideas here, don't you think it would make
sense to help THEM understand your situation?

If you have multiple tables containing "monthly data for a specific
accounting category", this describes a spreadsheet.

In a relational database, there would be one table for data, with an extra
field that indicates "specific accounting category".

Hopefully someone else can puzzle out your situation from the clues you've
offered so far. I'm fresh out of ideas...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.