combining many queries.

[dynamite9585]

Ok, I have been bashing my head agianst my keys for a while trying to work this out.

i have the following queries that ware working as intended:
JNCOfinder (queries TblPersonalinfo for people who meet the critera for the JNCO course)
SNCOFinder (same as above but for a different course)
Upcomingcourses (queries TblAnnualTrainingPlan and tells me what course of ANY type is coming up)


what i need to do, is be able to run upcomingcourses, choose what course i want to send someone on. run the finder for the selected course, then append the courseID and the Cadetnumber to another table to make a relationship for filling out the report that contains data from personalinfo and TblAnnualTrainingPlan

I am not set on doing it in this exact method, but i hope this gives you an idea of what i am trying to accomplish, will happily try any other method you are happy to suggest.

i am confident i can work out the rest on my own. i have been trying to tink of ways to do it and asking on here for help to make it work the way i came up with. clearly this isn't working and only making people a whole lot more knowlegable than i very annoyed.

 

[Trevor G]

Consider using a Union Query that will look at several tables and join information.

Check out your help facility or check out this link


http://www.fabalou.com/Access/Queries/unionquery.asp

 

[dynamite9585]

had a look at that site, that looks good for the next step of my problem.
at the moment i have a query that returns upcoming courses.

on each line it has a button to find people for that course, it will run the query related to that course checking who meets the critera to attend that course.

i then need to select who i would like to send on that course and append their UniqueID and the CourseID to another table to populate the application form.

 

[DCrake]

JNCOfinder (queries TblPersonalinfo for people who meet the critera for the JNCO course)
SNCOFinder (same as above but for a different course)

Why have you got 2 different queries querying the same source data with only the type of course being different.

In my eyes I would simply have one query and apply filtering to the query to get me the desired subset of data.

If you then want to look for both then just use the IN() command in your condition line.

 

[vbaInet]

Why have you got 2 different queries querying the same source data with only the type of course being different.

In my eyes I would simply have one query and apply filtering to the query to get me the desired subset of data.

If you then want to look for both then just use the IN() command in your condition line.

Then use two listboxes, one for selecting JNCO and/or SNCO and the other that is filtered by this listbox to display the list of people. You will need a multiselect listbox for the People list.

 

[dynamite9585]

the critera for each of the courses is vastly different.
SNCOfinder does the same thing just looking for different critera

 

[vbaInet]

You've got the idea anyway, use listboxes.

Show us a snippet of both criteria.

 

[dynamite9585]

SNCO FINDER

SELECT [Personal Info].Rank, [Personal Info].[Last Name], [Personal Info].[First Name], AttandacetrackerQuery.Paradecount, Age([Personal Info]![DateOfBirth]) AS Age, [Personal Info].Prof, [Personal Info].Basic2, [Personal Info].JNLDR
FROM AttandacetrackerQuery INNER JOIN [Personal Info] ON AttandacetrackerQuery.CDTID=[Personal Info].[Cadet Number]
WHERE ((([Personal Info].Rank)="CDT" Or ([Personal Info].Rank)="LAC" Or ([Personal Info].Rank)="CPL") And ((AttandacetrackerQuery.Paradecount)>1) And ((Age([Personal Info]!DateOfBirth))>1) And (([Personal Info].Prof) Is Not Null) And (([Personal Info].Basic2) Is Not Null) And (([Personal Info].JNLDR) Is Not Null) And (([Personal Info].SNLDR) Is Null) And (([Personal Info].Active)=Yes));

JNCO FINDER

SELECT [Personal Info].Rank, [Personal Info].[Last Name], [Personal Info].[First Name], AttandacetrackerQuery.Paradecount, Age([Personal Info]![DateOfBirth]) AS Age, [Personal Info].Basic2
FROM AttandacetrackerQuery INNER JOIN [Personal Info] ON AttandacetrackerQuery.CDTID=[Personal Info].[Cadet Number]
WHERE ((([Personal Info].Rank)="CDT" Or ([Personal Info].Rank)="LAC") And ((AttandacetrackerQuery.Paradecount)>=1) And ((Age([Personal Info]!DateOfBirth))>=15) And (([Personal Info].Basic2) Is Not Null) And (([Personal Info].Active)=Yes) And (([Personal Info].JNLDR) Is Null));

some of the critera is just test values at this point as i am using test data not actual as yet.

 

[vbaInet]

I see where you're coming from, quite different.

What you can do is to save the WHERE part in the second column of the list box or combo box and in the After Update event append what is in that column to the query and requery the People list box.

 

[dynamite9585]

excuse my ignorance but i'm not seeing how that suggestion is relevent to my original problem.

are you suggesting that on my upcoming courses for i add a list/combo box to display the results of the course finder?

have added a photo to hopefully help explain where i am at