Match Query 2

[AyJayEL]

I have 2 tables. Volunteer and Vacancy. Both have the same age range lookup. There are 4 criteria, Any, Under 13, 13-15 and 13-19. I have created a match query by first creating the Volunteer Query with VolID, Name, AgeID and a Vacancy Query with VacID, Title, AgeID. I then created another query based on these two queries using AgeID to match by. It works fine if the IDs are the same but what I really want it to do is if the Age Range is Any (AgeID 1) I want it to match with any of the other records (13-15 etc) and not just with the Any. Does this make sense? I guess I need to get into the SQL for the Query but I don't know how or what to do.

Can anyone help please. Learn something new every day *:->*

 

[KevinMoore]

Hi Andy-
I read your post over a couple of times and now think I know what you're looking for. Here's what I'm assuming:

You want a query returning vacancy records and any volunteer records that might qualify for the vacancy. For a vacancy with an AgeID of 1 (meaning any), you want all volunteers to match with it, right?

If so, I'd create a union query combining two subquerys.
Subquery 1: Volunteers matching Vacancies where the Vacancy Age ID <> 1
Subquery 2: An &quot;unjoined&quot; query of Vancancy and Volunteer where Vacancy Age ID = 1

The SQL would look like:
Sub1: SELECT whatever FROM Volunteer INNER JOIN Vacancy ON Volunteer.AgeID = Vacancy.AgeID WHERE Vacancy.AgeID<>1;
Sub2: SELECT exact-same-whatever FROM Volunteer, Vacancy WHERE Vacancy.AgeID=1;
UnionQuery: TABLE Sub1 UNION ALL TABLE Sub2;

I think I've got the syntax right -- I haven't actually checked it out though, so let me know if this doesn't work.

The unjoined query is kind of interesting because it returns all combinations of records from the two tables.
For the union query to be valid both sub-querys must return the same fields by type and order.

Kevin

 

[AyJayEL]

Kevin

Thanks, I think this might be what I need. Not sure if I quite know what to do but I'm on leave now until Thursday next week (lucky me!) so I won't be able to check it out until then. But if it is I will give you a well deserved star.

Regards

Andy (Andrea) Learn something new every day *:->*

 

[AyJayEL]

I'm baaack!

Ok it's not working quite right. It is still only matching if both the volunteer has Any in their details and the Vacancy has Any. I have pasted the SQL for each Query below.
(By the way Any has an ID of 5 not 1 as I posted previously).

SubQuery1
SELECT [QVol Match].[VolID], [QVol Match].[Surname], [QVol Match].[Forename], [QVacancy Match].[VacID], [QVacancy Match].[Title], [QVol Match].[AgeRngID] AS VolunteerAge, [QVacancy Match].[AgeRngID] AS VacancyAge, [QVacancy Match].[AgeRanges]
FROM [QVol Match] INNER JOIN [QVacancy Match] ON [QVol Match].[AgeRngID]=[QVacancy Match].[AgeRngID]
WHERE ((([QVacancy Match].[AgeRngID])<>5));

SubQuery2
SELECT [QVol Match].VolID, [QVol Match].Surname, [QVol Match].Forename, [QVacancy Match].VacID, [QVacancy Match].Title, [QVol Match].AgeRngID AS VolunteerAge, [QVacancy Match].AgeRngID AS VacancyAge, [QVacancy Match].AgeRanges
FROM [QVol Match] LEFT JOIN [QVacancy Match] ON [QVol Match].AgeRngID = [QVacancy Match].AgeRngID
WHERE ((([QVacancy Match].AgeRngID)=5));

UNION QUERY
TABLE Subquery1 UNION ALL TABLE Subquery2;
Learn something new every day *:->*

 

[danvlas]

Try this:

Select Volunteer.*, Vacancy.* From Volunteer, Vacancy Where Volunteer.AgeRange = 5 Or Vacancy.AgeRange = 5;

It will give you alllll possible combinations between the two tables where the AgeRange field is 5.
In other words, it will take &quot;Any&quot; records from Volunteer and combine them with ALL records from Vacancy and the other way round.
I'm sure if you play around a little with this you'll get what you need.
The other query should NOT match the &quot;Any&quot; range, but only the others-that's simple and you have already done it.
A union on the two will do (I think )

Regards,
Dan

 

[AyJayEL]

Thank you to both of you. A star each! Learn something new every day *:->*

 

[AyJayEL]

OK I now have a new challenge for you! In this database we are also trying to match by Experience. Here is the table

ExperienceID Experience
1 None
2 Some experience of working with young people
3 Experience of leading a group
4 Counselling experience

This time what I need is when the vacancy says it needs no experience (None) then all volunteers should match.

If the vacancy says it needs 2 Some experience then it should match with any volunteers with 2 3 or 4

If vacancy says 3 then volunteers should have 3 or 4

And lastly if vacancy says 4 then volunteer should have 4.

This would be in the same query as the previous. (I have added the fields into the SubQueries in preparation).

Cheers chaps. Learn something new every day *:->*

 

[danvlas]

Not tested, but it's worth a try...

Select Volunteer.*, Vacancy.* From Volunteer, Vacancy Where (Volunteer.AgeRange = 5 Or Vacancy.AgeRange = 5) And (Volunteer.Experience >= Vacancy.Required);

Let me know.

Dan

 

[AyJayEL]

Thanks Dan, I have tried that. When I run it and the volunteer has None it is still bringing out all Vacancies. Once it has run and I go back into the SQL it has moved the SQL around a bit so it looks like this. (I'll just show the Where clause)

WHERE ((([QVol Match].ExperienceID)>=[QVacancy Match].[ExperienceID]) AND (([QVacancy Match].AgeRngID)=5)) OR ((([QVol Match].AgeRngID)=5));

instead of

WHERE ((([QVacancy Match].AgeRngID)=5)) OR ((([QVol Match].AgeRngID)=5)) AND ([QVol Match].ExperienceID >=[QVacancy].ExperienceID;

I guess I need to put some more brackets round things to force it to do things in certain order but I get all sorts of messages when I try to do it.

Once more, can you help?

Learn something new every day *:->*

 

[AyJayEL]

Actually now I look at those two clauses I don't think it makes any difference!

I should add that I have tried experimenting with the 'ways round' and the >= etc.

I am also attempting this in SubQuery2 which is the Unjoined Query. Learn something new every day *:->*

 

[danvlas]

One bracket missing...

WHERE ((([QVacancy Match].AgeRngID)=5)) OR ((([QVol Match].AgeRngID)=5)) AND ([QVol Match].ExperienceID >=[QVacancy].ExperienceID);

Gotta go now. See you on Monday.

Have a nice weekend (I will for sure)

 

[AyJayEL]

Hm, yes Dan

That was just a typo on my part whilst typing it here. I'll need to think again.

You have a good weekend too! Learn something new every day *:->*

 

[danvlas]

OK, I made two tables and populated them with dummy data to test this. In my opinion, it worked fine, so try it:

SELECT Vacancies.*, Volunteers.*
FROM Vacancies INNER JOIN Volunteers ON
Vacancies.AgeRange = Volunteers.VolAgeRange
WHERE (((Vacancies.AgeRange)<>5) AND
((Vacancies.SkillReq)<=[volunteers]![volskill]))
UNION SELECT Vacancies.*, Volunteers.*
FROM Vacancies, Volunteers
WHERE (((Vacancies.AgeRange)=5) AND
((Volunteers.VolAgeRange)=5) AND
((Vacancies.SkillReq)<=[volunteers]![volskill])) OR
(((Vacancies.AgeRange)=5) AND
((Volunteers.VolAgeRange)=5) AND
((Volunteers.VolSkill)>=[vacancies]![skillreq]));

My tables:
Vacancies:
AgeRange - number
SkillReq (skill required) - number
whatever other fields

Volunteers:
VolAgeRange (volunteer age range)- number
VolSkill (volunteer's skill level) - number
whatever other fields

Change the table/query name (and field names) and shoot.

Regards,

Dan

 

[AyJayEL]

Thank you Dan, I am sure we are nearly there. It is working fine for the Experience bit. Perfect and lovely! But the age range seems only to be working as an equal join.

This is what I have in my query.

SELECT [QVol Match].*, [QVacancy Match].*
FROM [QVacancy Match] INNER JOIN [QVol Match] ON [QVacancy Match].[AgeRngID]=[QVol Match].[AgeRngID]
WHERE ((([QVacancy Match].[AgeRngID])<>5)) And [QVol Match].[ExperienceID]<=[QVacancy Match].[ExperienceID]

UNION SELECT [QVol Match].*, [QVacancy Match].*
FROM [QVacancy Match], [QVol Match]
WHERE ((([QVacancy Match].[AgeRngID])=5)) And [QVol Match].[AgeRngID] = 5 AND [QVacancy Match].[ExperienceID]<= [QVol Match].[ExperienceID] OR ((([QVacancy Match].[AgeRngID])=5)) AND [QVol Match].[AgeRngID] = 5 AND [QVol Match].[ExperienceID] >= [QVacancy Match].[ExperienceID];

Learn something new every day *:->*

 

[danvlas]

Take a close look at the paranthesis (second Where claus)...you messed them up completely (no offence):

WHERE ((([QVacancy Match].[AgeRngID])=5)) And [QVol Match].[AgeRngID] = 5 AND [QVacancy Match].[ExperienceID]<= [QVol Match].[ExperienceID] OR ((([QVacancy Match].[AgeRngID])=5)) AND [QVol Match].[AgeRngID] = 5 AND [QVol Match].[ExperienceID] >= [QVacancy Match].[ExperienceID];

Practically, ALLLLLLLLL your paranthesis are useless-they just enclose each component, the same result will show up if you remove allll of them.

Here's what you should have:
WHERE ((([QVacancy Match].[AgeRngID])=5) AND
(([QVol Match].[AgeRngID])=5) AND
(([QVacancy Match].[ExperienceID])<=[QVol Match]![ExperienceID])) OR
((([QVacancy Match].[AgeRngID])=5) AND
(([QVol Match].[AgeRngID])=5) AND
(([QVol Match].[ExperienceID])>=[QVacancy Match]![ExperienceID]));

Just follow the colours and you'll see the difference...

HTH,

Dan

 

[AyJayEL]

Dan my hero...

You are going to love me and be grateful for small mercies. I think I have got it working as I want it. I could not have done it without your help. Thank you so much. I reward you with a star. (I would have sent you an orange at Christmas but I don't know where to send it! )

By the way, I didn't put the parenthesis in. Access did it for me. (Mind you I did do a bit of copying and pasting from other queries so it might have happened then.)

Thank you once again!

Andrea Learn something new every day *:->*

 

[danvlas]