Query Design

  • Thread starter Thread starter faxo
  • Start date Start date
F

faxo

Hi,
I have a very basic database with two tables for a sports club. First table
is called 'Swimming Table'. This contains 2 fields 'Name' and 'Address'. The
second table is 'Gym table'. This also contains 2 fields 'Name' and 'Address'.


I want to design a parameter query which will ask for a name. Basically I am
looking to see if the same name appears in both tables.

My parameter query is formed like this: Like [Enter suppliers name please:] &
"*"

How should I link the tables. And what else needs to be done in query design
to accomplish the result.

Thanks.
 
Based on your description, you are working with a ... spreadsheet! While
you might need to use separate worksheets for Swimming and Gym in Excel,
Access is a relational database, and designing your tables this way will
prove counter-productive. As your post points out, you've already found one
of the ways this design doesn't work well.

If you have three tables like:

tblPerson
PersonID
FirstName
LastName
StreetAddress
...

tblActivity
ActivityID
Activity (this is your "Swimming", ...)

trelPersonActivity
PersonID
ActivityID

you could use this to record each person a single time, then show which
Activity/Activities that person is connected to.

Why would you want to do this extra work? Well, you've already found one
reason why, here's another ... right now you have only two activities.

Are you ready to re-design your tables, your queries, your forms, your
reports, your macros, and your code every time a new activity gets added?!
With the three table design I laid out, you could, instead, simply add one
new row to the tblActivity and use it, as needed, in that third table.

(and if you insist on using Access like a spreadsheet, look into UNION
queries ... this would give you a way to combine results from a query
against your Swim table and a second query against your Gym table...)

Good luck!

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff said:
Based on your description, you are working with a ... spreadsheet! While
you might need to use separate worksheets for Swimming and Gym in Excel,
Access is a relational database, and designing your tables this way will
prove counter-productive. As your post points out, you've already found one
of the ways this design doesn't work well.

If you have three tables like:

tblPerson
PersonID
FirstName
LastName
StreetAddress
...

tblActivity
ActivityID
Activity (this is your "Swimming", ...)

trelPersonActivity
PersonID
ActivityID

you could use this to record each person a single time, then show which
Activity/Activities that person is connected to.

Why would you want to do this extra work? Well, you've already found one
reason why, here's another ... right now you have only two activities.

Are you ready to re-design your tables, your queries, your forms, your
reports, your macros, and your code every time a new activity gets added?!
With the three table design I laid out, you could, instead, simply add one
new row to the tblActivity and use it, as needed, in that third table.

(and if you insist on using Access like a spreadsheet, look into UNION
queries ... this would give you a way to combine results from a query
against your Swim table and a second query against your Gym table...)

Good luck!

Jeff Boyce
Microsoft Office/Access MVP
Hi,
I have a very basic database with two tables for a sports club. First
Click to expand...
[quoted text clipped - 17 lines]
Thanks.
Click to expand...
Click to expand...


Thanks for the advice,

I'll try it out.

Thanks again!!
 
Back
Top