Automatic Relationships

[Nineseven]

Hi all,

My company recently upgraded from the Microsoft Office 2003 Suite to 2007. For Five years now I have been using Access 2003, and the change is a little :banghead:

So I maintain an Access Database where I mostly run reports. Every month I upload new spreadsheets and run a query to get the data I need. In Access 2003, when I would run a query, relationships would automatically generate by the primary key. I should note that each spreadsheet has the same primary key. In Access 2007 I find myself going into the relationships database tools and manually creating them in design view, something I never had to do in Access 2003. I have a many spreadsheets, and linking ever single spreadsheet manually is time consuming.

Am I missing something? Is there a way to not have to build relationships in the relationships database tool, and have them automatically generate while running a query?

 

[JHB]

Have you enabled the Auto Join for query design?

File->Options->Object Designers section "Query design" mark "Enable AutoJoin"

Hmm - by me it seems not to make any difference if it marked or not, the join creates automatic.

 

[Nineseven]

Have you enabled the Auto Join for query design?

File->Options->Object Designers section "Query design" mark "Enable AutoJoin"

Hmm - by me it seems not to make any difference if it marked or not, the join creates automatic.

I just checked, it is enabled...any other ideas?

 

[JHB]

Only a stupid question, when you open the database before you import anything, does the relationships window, show the relationships between the tables?

 

[Nineseven]

Only a stupid question, when you open the database before you import anything, does the relationships window, show the relationships between the tables?

not a stupid question at all. When I open the relationships table nothing is there. I never had to create it in Access 2003, and its a pain to manually link all those tables. When I would run a query in 03 the table keys would automatically link up and create a relationship.

 

[jdraw]

When you import data each month, do you delete the existing data and replace with new data? Or do you append to the existing?

Are you creating Relationships in the Database relationships window, or are you creating Joins in query design?

 

[Nineseven]

When you import data each month, do you delete the existing data and replace with new data? Or do you append to the existing?

Are you creating Relationships in the Database relationships window, or are you creating Joins in query design?

I keep the existing data. Many times I have to run queries to compare old and new financials.

I am not creating relationships in the database window. There are far too many tables to do that, it is time consuming, and every month I import more tables, and would have to create the relationships with those. I am not creating joins in query design, that would be way more simpler, but I couldn't find out how to do that

 

[JHB]

To get an idea how to help you, could you describe exactly: What you do, how you do it and why you do it.
Do you using an import specification created by Access, or how do you import?
Do you import into a temp table first?
Are you creating new queries each month, if yes, (how do you create them, by code or?), and why do you create them?
Why do you have so many tables in you database?
You say each month comes more tables into the database, why?

And some more information could be good to get.

 

[Nineseven]

To get an idea how to help you, could you describe exactly: What you do, how you do it and why you do it.
Do you using an import specification created by Access, or how do you import?
Do you import into a temp table first?
Are you creating new queries each month, if yes, (how do you create them, by code or?), and why do you create them?
Why do you have so many tables in you database?
You say each month comes more tables into the database, why?

And some more information could be good to get.

I am a project manager. I look at all sorts of data ranging from traditional financials to business orders, etc
I would link this specific work flow spreadsheet in access 2003, because access would delete and duplicate rows, I now import it because Access 2007 does no do this.
I update tables each month, to find or figure out trends in business or to see cash flows, revenues, etc.
Part of the work I do is to find out any anomalies in business, or to create a presentation to describe the current financial standing of a company, and several other things.


I guess what I am trying to say. When I would run a query in Access 2003 relationships would automatically generate, in design view, by the primary key. When I perform a query now, no table automatically generates in design view. I would do this myself, but everything looks so different in 2007 I just dont know how to do this.

 

[JHB]

I guess what I am trying to say. When I would run a query in Access 2003 relationships would automatically generate, in design view, by the primary key. When I perform a query now, no table automatically generates in design view. I would do this myself, but everything looks so different in 2007 I just dont know how to do this.

Below is a link to how you create relationships in a query, section: "Building a Basic Query"
http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Microsoft-Access-Query.htm

If I were you I would try to automatic the import and query by using some code, (then the whole work could be done by some few clicks/choices.), but it need to be some business rules/ some logic which are repeated each time you import data.

 

[Nineseven]

If I were you I would try to automatic the import and query by using some code, (then the whole work could be done by some few clicks/choices.), but it need to be some business rules/ some logic which are repeated each time you import data.[/QUOTE]

this was extremely helpful thank you

 

[JHB]

this was extremely helpful thank you

You're welcome, but I don't if it really helped you, (it was only thoughts!), good luck.