Multi-table multi-option query

[George Bowyer]

I have a database that is used to tell certain people about certain events (tblPeople; tblEvents)

I have divided the country into areas (tblAreas).

Each person wants to know about events in one or more areas. I store this in tblPeopleAreas.

Each Event should be notified to people interested in one or more areas. I keep these in tblEventAreas.

Easy enough, so far.

What I want to do is look at an event and see which people need to be notified, or look at a person and see which events they need to be notified about.

To do this, I need a rather complex query joining the tblPeople table with tblPeopleAreas table, the tbl PeopleAreas table with the tblEventAreas table, and the tblEventAreas table with the tblEvents table.

I have looked at this for hours (over a period of months) and I just cannot work out even where to begin building the various joins and relationships to make a workable query.

Can soimeone please give me a steer in the right direction?

Thanks

 

[ByteMyzer]

Can you attach a copy of the database with the tables you describe? If I could see it, I would be able to solve this one for you.

 

[George Bowyer]

I'll have to copy up a blank version with only a few demo records, otherwise it's going to be too large and also giving away confidential information...

that'll take a me a day or two probably.

Thanks for the offer. I'll be back...

 

[George Bowyer]

Well, I've made a little copy of my database with the three forms and various queries that it needs.

Trouble is, it's massive. Unzipped it's 8 mb and zipped its over 2.

I've only got 100 records in it, so I'm not really sure why it's so large.

What takes up most space in an access db?

 

[Rich]

What takes up most space in an access db?

OLE objects

 

[George Bowyer]

Haven't got any.

Just half a dozen or so each of tables, forms, and queries, a couple of modules and a hundred or so records

I really can't undestand why it's so big...

 

[rbrule]

Haven't got any.

Just half a dozen or so each of tables, forms, and queries, a couple of modules and a hundred or so records

I really can't undestand why it's so big...

Try compacting and repairing the database. Adding and deleting objects increases the size of the database. The compacting process cleans it up and reduces the size.

 

[George Bowyer]

Ah, yes, indeed. I forgot all about that the file is now a much more manageable size. Well its still 500k but that is better than 2MB

For some reason I haven't compacted my db for ages. Used to do it regularly.

It's too big to upload to here, so I've put a link to it Here.

Disclaimer : All persons downloading this file do so at their own risk and are strongly advised to check for viruses before opening.

(Sorry, sounds formal but one should cover ones ass)

 

[KKilfoil]

If you zip the file, it is quite small (<40K).

Anyways, I have attached a MODIFIED version of your database. I stripped out the modules and forms to shrink it a bit, so don't use my version as your new starting point!

What you should look at is the 'relationships window' and at qryDEMO. The key to your problem is permanently defining your relationships, rather than 'on-the-fly' in a query. Enforcing referential integrity is always a good idea.

Once you have these relationships in place, making a query is somewhat trivial. As shown, the query sorts by person, then by area, then by event, and then shows all data. You can easily add parameters/filtering/new sorts to change the data.

Alternatively, you can create forms/subforms or reports/subreports to show the data in another way.

HTH

Ah, yes, indeed. I forgot all about that the file is now a much more manageable size. Well its still 500k but that is better than 2MB

For some reason I haven't compacted my db for ages. Used to do it regularly.

It's too big to upload to here, so I've put a link to it Here.

Disclaimer : All persons downloading this file do so at their own risk and are strongly advised to check for viruses before opening.

(Sorry, sounds formal but one should cover ones ass)

 

[George Bowyer]

This is just what I wanted. Thank you very much. See,ms easy now, but I was struggling for ages with it.

One supplementary question if I may?

If a contact wants to know about events in Areas one and two, and an Event says to notify people interested in Area one or Area two, then the query will throw up two notifications for the same person for the same Event (one because of Area one, another because of Area two).

Is there an easy way (easier than running a find duplicates query) of limiting that to only one return per person per event? (Saves on postage...)

 

[KKilfoil]

Is there an easy way (easier than running a find duplicates query) of limiting that to only one return per person per event? (Saves on postage...)

If your query does not show any fields from the Areas table (just events and contacts), then your example should create two identical rows.

Look up the DISTINCT and DISTINCTROW query modifiers.