Cross Table reference query

[wsattar]

All,

I'm currently working on clients database - MS access 2003:

Th purpose of the database is too hold personal records for those signed up to a series ongoing on events and then send out mail merges (invites)

okay so the setup:

using the DDF i've managed to extract the schema and see how it's set up:

so Table1:

The first table is used for keeping personal contacts/records such as name, address, interests etc etc..all very simple and normal.

it was also used to store events, so each event had its own field name and was linked to the personal records by an indentifier.

that table ran out of space

so they created table 2:

table 2 is list of events (each with its own field) and linked to table 1 via a key (ID) to allow querying.

Table 2 filled up...and so they created table 3:

table 3 is a new tabe for events....same set up as table 2

so what they would like is to query an even in table 3, like...World Dominiation Expo 2009 and obtain a list of invitiees or people who attended World Domination Expo 2008 (in table 2) and World Domination Expo 2007 ( in table 1) without going through a series of laberous queries and additions...

so discussion, queries and advice would be grateful!

Thanks again!

 

[Dennisk]

how can you fill up a table? Access has a 2 gb limit on the size of a database

 

[neileg]

You need to normalise your structure. Instead of one field per event, you need one record per event. Then, because you have a many to many relationship between people and events, you need a third table as a junction table to join them together.

So you need to read up on normalisation and junction tables.

 

[wsattar]

okay interesting - It may be the way the tables were set up previously by an independant contractor ( as I was told..)

2GIG is the database maximum exluding some system info/data but there is a limit to the number of records you can have.

what is happining is that records for each ID are being created to show which events they are going too

 

[neileg]

okay interesting - It may be the way the tables were set up previously by an independant contractor ( as I was told..)

2GIG is the database maximum exluding some system info/data but there is a limit to the number of records you can have.

what is happining is that records for each ID are being created to show which events they are going too

No, there's no limit to the number of records you can have so long as you don't exceed 2gb. There is a limit on the number of fields you can have in a table, 255.

So by having a record for each event and not a field for each event, you simply don't have a problem.