Forms-ridiculous questions

[cjordan8]

I have 4 tables that must be linked together that all have the same information in them but for differnt competitors. I've created the city name as the primary key and created relationships between the 4 tables, but I would like to be able to enter data into one form with four tabs(1 for each competitor) and have it saved to the 4 different tables. I know it's something simple, but for the life of me can't remember how to do it.

 

[llkhoutx]

Use three append queries with appropriate queries.

 

[Rich]

Why would you have four tables all with the same data in except for competitor? You don't need four tables to do this.

 

[cjordan8]

How do I do it without four tables? I had wanted each city to be each own record with up to 4 competitors listed. any help is appreciated

 

[David R]

You need two tables. One that stores all the 'common' information, and one that stores the Primary Key field from your first table, and the competitor information. (Three tables if you want to store Competitors in their own table, and link them via a middle-man table).

That's the simple thing you're forgetting.

 

[clive2002]

If you do just want to join your four tables use a union select query in SQL, check the Access help.

I've used it for a similar purpose with no problems.

Although ive never based a form on it or tried to run data entry back through it, so that might not work

 

[David R]

UNION queries are non-updateable; trust me, I tried a similar solution once. Database Normalization rules are there for a reason, and they really will make things simpler in the long run, though they can be a hassle to convert non-structured data into for that first conversion.

 

[cjordan8]

Thanks for the suggestions. I ended up just putting it in one table and not worrying about each city being it's own record. Now each competitor is it's own record.

 

[David R]

So you've got:

Company1 Competitor1
Company1 Competitor2
Company1 Competitor3
Company1 Competitor4
Company2 Competitor1
Company2 Competitor2
etc, all in one table?

 

[cjordan8]

No, I've just got City, Competitor, etc. now.
I didn't break it down by competitor numbers. ALthough now I've been told that in report format I need to have the City name, then it's competitors under it, and totals for all the numbers on one page. Oh the joys of figuring out databases!

 

[David R]

Draw out your data structures by hand and make sure you have your tables separated properly. I used to jump right into designing and spent half my time reworking my shoddy tables to get the results I wanted.

"Measure twice, cut once."