Not sure where this needs to go, but need help with an access project.

[Stuartdls]

Hello, I am trying to create a query in access. Its been a long time since ive done all of this, but maybe someone on here can help.

I have two tables, a "Care Ambassador" Table, with Name, job title, address, contact number, employer name, employer address etc.
On the other table, "events" i have a list of events that those people have been to.

They each have a unique number (auto nuumber) in a "ref" column i have added and I can run a query on people who have been to specific dates, as it queries the events database table, but what I want to do is runa query of people that have not taken part/completed any events.

I understand that it is bringing up no results as there are no people in the "events table" that have done no training as they only get put in there if they have done some.

Is there any way to run a query on both the main table and subdatasheet (events table) of people who have done no event work?

Thanks in advance.

Stuart

 

[georgedwilkinson]

Yes, but you didn't give us the right information to help you.

Look up the SQL syntax for "not in". You want people who are not in events.

Your table names and column names scare me and make me think you have a normalization issue. Might want to check that.

 

[neileg]

A few thoughts to add to George's advice.

It sounds to me like many people can attend many events. You can't model a many to many relationship in Access with two tables, you need a junction table as well. A search in these forums will reveal how to deal with this.

To handle null records in a join situation requires you to consider the join type. The deafault join in Access is an inner join which requires matching records on both sides of the join. You don't get any null records with this. If you use an outer join (type 2 or 3 in the Access join type list) you will be able to pull all the records from one side and the matching records from the other. Where there is no match, you'll get a null record.

 

[Stuartdls]

Thanks for all comments so far.
I am trying to create a junction table, as you were rightin saying that many people can do many events on many different dates.

Can anyone shed any more light on this?
I have created the relationships, but do i need to create a form for all this data now?

Thanks.

Stuart

 

[georgedwilkinson]

When including a junction table on a subform, I only show a query based on the junction table's data (generally speaking), not the other "many" side (though I'll represent the many side data on the subform). I'll also typically have another form/subform with the other many side at top and the junction table in the subform.

Does that make sense?
What you should be showing in the subform is that relationship between the Care Ambassador and the Event, not all the pertinent data about the event itself.

 

[Stuartdls]

can i post it up here somehow and see if someone could take a look...still not really getting this.

Im sorry for being such a beginner, but need to try to sort this out.

Thanks in advance