Newbie - Relationships

[Ken]

Could someone please help me.

I have constructed a Database with several Tables that contain Names and
Addresses.
The Database consists of several Tables each containing 65000 records.

What I wish to do is to set up a query so that when I search for a surname
the output will
list all the matching surnames in all the Tables.

I think I need to set up the relationships between the tables but although I
have tried the trial
and error method I cannot get it to work.

Any help appreciated

Please reply to group or to (e-mail address removed) (remove
nospam)

Many thanks in advance

Regards

Ken

 

[Albert D. Kallal]

Actually, I don't think relationships will help you in your case.

65,000 records is quite a small table. You don't mention how many of these
tables you have.

If you only got 10 tables (650,000) records, then I would merge all the
tables into one large table.

You are not going to get any better performance. Worse, if you have any code
or query that processes a results, you then have to know, or some how match
the results to the particular table the names reside in. So, for simply
sake, just put all the names into one large table (this is especially so if
you are only going to results in 2 million or so records).

Relationships allow you to "join" data between tables that is related. So,
each name much have purchased a product, and thus you would create a
purchasedProudct table. Relationships will not fix or solve your problem,
and also not solve any performance problems in your case.

If merging data into one large table is not possible, then you could
consider union queries..but with those table sizes...I don't think it would
work well at all.

What road you take really depends on how many tables, and the total number
of records here.

Also, you will/do need a index on the name field you are searching..and this
will make searching very fast.

 

[John Vinson]

Could someone please help me.

I have constructed a Database with several Tables that contain Names and
Addresses.
The Database consists of several Tables each containing 65000 records.

Having several tables with the same kind of data is generally a Very
Bad Idea. It's storing data in tablenames - and table *names* are not
intended or designed to store data! Data should be stored in fields in
a table instead. You may want to consider instead having a *single*
table of names and addresses with one additional field, describing
whatever now distinguishes your table names. 65000 records is too many
for Excel but it's no problem for Access; if you have 6,500,000
records your table's getting big.

What I wish to do is to set up a query so that when I search for a surname
the output will
list all the matching surnames in all the Tables.

Unless you're looking for names or people which are in common between
the tables, you don't want to use relationships. What you need instead
is a UNION query. See UNION in the online help; you'll need to go into
the SQL window rather than the query design grid. Post back if you
need help with the UNION. Note that it will be slower than having the
data all in one properly indexed table, though!

John W. Vinson[MVP]

 

[Ken]

Thank you for your reply.

I have 10 of these tables. I may be missing something here as I tried
combining all the
data into one table but it didn't work and I was told that each table can
only contain
65536 records (hence the number of tables).

The data is being converted from an 1891 Census and in total there are
nearly 400,000 records, each record has a Surname, Forename and some other
details.

Ken

 

[Ken]

Gents

Many thanks to you both for the help. I have constructed a Union Query and
although
I am only searching on 4 of the tables (262,144 records) at the moment it is
working great.
Currently the speed of the search is superb and hopefully it will not
degrade too much when
I add the remaining tables.

Once again very many thanks

Ken

,

 

[Mike Painter]

Thank you for your reply.

I have 10 of these tables. I may be missing something here as I tried
combining all the
data into one table but it didn't work and I was told that each table
can only contain
65536 records (hence the number of tables).

The data is being converted from an 1891 Census and in total there are
nearly 400,000 records, each record has a Surname, Forename and some
other details.

You were told wrong and will be much better served with all the records in
one table.
As long as you don't get past the 2 Gb limit of an Access database you can
have as records in a table as you want.

 

[John Vinson]

Gents

Many thanks to you both for the help. I have constructed a Union Query and
although
I am only searching on 4 of the tables (262,144 records) at the moment it is
working great.
Currently the speed of the search is superb and hopefully it will not
degrade too much when
I add the remaining tables.

I would suggest basing a MakeTable or an Append query on the UNION
table and combining all these into one table.

John W. Vinson[MVP]