Querying multiple records in two tables

Hi, in my database I have tables for users (UserID, UserName),
projects (ProjectID, Project Name), and qualifications (QualID,
QualName). I have join tables for users_qualifications (UserID,
QualID), and projects_qualifications. (ProjectID, QualID).

What I need to do is run a query for a project to show which users
have the exact matching qualificiations.

Users can have many qualifications, projects can require many
qualifications, users may only work on a project if the qualifications
required/held match exactly.

Please help.
0
rmorrison
11/29/2007 11:38:46 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
595 Views

Similar Articles

[PageSpeed] 5

Assuming that ProjID, and QualID are numeric, the following should give you 
the ProjectID and UserID of all those individuals who have the correct quals 
for each project.

SELECT tbl_ProjQuals.ProjID,
               tbl_UserQuals.UserID,
              Count(tbl_ProjQuals.QualID) AS CountOfQualID
FROM tbl_ProjQuals INNER JOIN tbl_UserQuals
ON tbl_ProjQuals.QualID = tbl_UserQuals.QualID
GROUP BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID
HAVING Count(tbl_ProjQuals.QualID)=DCount("QualID","tbl_ProjQuals","[ProjID] 
= " & [tbl_ProjQuals].[ProjID])
ORDER BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID;

Basically, what this does is join the ProjectQuals table and UserQuals 
tables on the QualID field and count the number of matches for each Project, 
UserID combination.  It then filters that for those combinations where the 
Count of matching QualID fields is the same as the number of QualIDs for 
that ProjectID.

HTH
Dale

<rmorrison@davislangdon.com.au> wrote in message 
news:7770f1cd-5588-4968-b512-78ce486fb050@s12g2000prg.googlegroups.com...
> Hi, in my database I have tables for users (UserID, UserName),
> projects (ProjectID, Project Name), and qualifications (QualID,
> QualName). I have join tables for users_qualifications (UserID,
> QualID), and projects_qualifications. (ProjectID, QualID).
>
> What I need to do is run a query for a project to show which users
> have the exact matching qualificiations.
>
> Users can have many qualifications, projects can require many
> qualifications, users may only work on a project if the qualifications
> required/held match exactly.
>
> Please help. 


0
Dale
11/30/2007 2:30:02 AM
On Nov 30, 1:30 pm, "Dale Fye" <dale....@nospam.com> wrote:
> Assuming that ProjID, and QualID are numeric, the following should give you
> the ProjectID and UserID of all those individuals who have the correct quals
> for each project.
>
> SELECT tbl_ProjQuals.ProjID,
>                tbl_UserQuals.UserID,
>               Count(tbl_ProjQuals.QualID) AS CountOfQualID
> FROM tbl_ProjQuals INNER JOIN tbl_UserQuals
> ON tbl_ProjQuals.QualID = tbl_UserQuals.QualID
> GROUP BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID
> HAVING Count(tbl_ProjQuals.QualID)=DCount("QualID","tbl_ProjQuals","[ProjID]
> = " & [tbl_ProjQuals].[ProjID])
> ORDER BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID;
>
> Basically, what this does is join the ProjectQuals table and UserQuals
> tables on the QualID field and count the number of matches for each Project,
> UserID combination.  It then filters that for those combinations where the
> Count of matching QualID fields is the same as the number of QualIDs for
> that ProjectID.
>
> HTH
> Dale
>
> <rmorri...@davislangdon.com.au> wrote in message
>
> news:7770f1cd-5588-4968-b512-78ce486fb050@s12g2000prg.googlegroups.com...
>
>
>
> > Hi, in my database I have tables for users (UserID, UserName),
> > projects (ProjectID, Project Name), and qualifications (QualID,
> > QualName). I have join tables for users_qualifications (UserID,
> > QualID), and projects_qualifications. (ProjectID, QualID).
>
> > What I need to do is run a query for a project to show which users
> > have the exact matching qualificiations.
>
> > Users can have many qualifications, projects can require many
> > qualifications, users may only work on a project if the qualifications
> > required/held match exactly.
>
> > Please help.- Hide quoted text -
>
> - Show quoted text -

Thanks, that's almost what I want. How can I show a list of the users'
names with the appropriate qualifications. For example, if I am in a
project record and want to see who can work on the project, I'd like
to click on a button and bring up a list of the users. I've tried
modifying this query but I can't get it to work.

Hope you can help.
0
rmorrison
11/30/2007 3:39:01 AM
Add your users table to the query grid, Join the Users.UserID to 
UserQuals.UserID, then change tbl_UserQuals.UserID to tbl_Users. UserName in 
the SELECT,  GROUP BY, and ORDER BY clauses. Then modify the where clause so 
that it points to the ProjectID control on your form, so all you get is the 
names of the individuals who are qualified for that project.

HTH

<rmorrison@davislangdon.com.au> wrote in message 
news:7828925e-4561-4695-bbbd-4066f61e2ffc@d27g2000prf.googlegroups.com...
> On Nov 30, 1:30 pm, "Dale Fye" <dale....@nospam.com> wrote:
>> Assuming that ProjID, and QualID are numeric, the following should give 
>> you
>> the ProjectID and UserID of all those individuals who have the correct 
>> quals
>> for each project.
>>
>> SELECT tbl_ProjQuals.ProjID,
>>                tbl_UserQuals.UserID,
>>               Count(tbl_ProjQuals.QualID) AS CountOfQualID
>> FROM tbl_ProjQuals INNER JOIN tbl_UserQuals
>> ON tbl_ProjQuals.QualID = tbl_UserQuals.QualID
>> GROUP BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID
>> HAVING 
>> Count(tbl_ProjQuals.QualID)=DCount("QualID","tbl_ProjQuals","[ProjID]
>> = " & [tbl_ProjQuals].[ProjID])
>> ORDER BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID;
>>
>> Basically, what this does is join the ProjectQuals table and UserQuals
>> tables on the QualID field and count the number of matches for each 
>> Project,
>> UserID combination.  It then filters that for those combinations where 
>> the
>> Count of matching QualID fields is the same as the number of QualIDs for
>> that ProjectID.
>>
>> HTH
>> Dale
>>
>> <rmorri...@davislangdon.com.au> wrote in message
>>
>> news:7770f1cd-5588-4968-b512-78ce486fb050@s12g2000prg.googlegroups.com...
>>
>>
>>
>> > Hi, in my database I have tables for users (UserID, UserName),
>> > projects (ProjectID, Project Name), and qualifications (QualID,
>> > QualName). I have join tables for users_qualifications (UserID,
>> > QualID), and projects_qualifications. (ProjectID, QualID).
>>
>> > What I need to do is run a query for a project to show which users
>> > have the exact matching qualificiations.
>>
>> > Users can have many qualifications, projects can require many
>> > qualifications, users may only work on a project if the qualifications
>> > required/held match exactly.
>>
>> > Please help.- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks, that's almost what I want. How can I show a list of the users'
> names with the appropriate qualifications. For example, if I am in a
> project record and want to see who can work on the project, I'd like
> to click on a button and bring up a list of the users. I've tried
> modifying this query but I can't get it to work.
>
> Hope you can help. 


0
Dale
11/30/2007 11:37:40 AM
On Nov 30, 10:37 pm, "Dale Fye" <dale....@nospam.com> wrote:
> Add your users table to the query grid, Join the Users.UserID to
> UserQuals.UserID, then change tbl_UserQuals.UserID to tbl_Users. UserName in
> the SELECT,  GROUP BY, and ORDER BY clauses. Then modify the where clause so
> that it points to the ProjectID control on your form, so all you get is the
> names of the individuals who are qualified for that project.
>
> HTH
>
> <rmorri...@davislangdon.com.au> wrote in message
>
> news:7828925e-4561-4695-bbbd-4066f61e2ffc@d27g2000prf.googlegroups.com...
>
>
>
> > On Nov 30, 1:30 pm, "Dale Fye" <dale....@nospam.com> wrote:
> >> Assuming that ProjID, and QualID are numeric, the following should give
> >> you
> >> the ProjectID and UserID of all those individuals who have the correct
> >> quals
> >> for each project.
>
> >> SELECT tbl_ProjQuals.ProjID,
> >>                tbl_UserQuals.UserID,
> >>               Count(tbl_ProjQuals.QualID) AS CountOfQualID
> >> FROM tbl_ProjQuals INNER JOIN tbl_UserQuals
> >> ON tbl_ProjQuals.QualID = tbl_UserQuals.QualID
> >> GROUP BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID
> >> HAVING
> >> Count(tbl_ProjQuals.QualID)=DCount("QualID","tbl_ProjQuals","[ProjID]
> >> = " & [tbl_ProjQuals].[ProjID])
> >> ORDER BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID;
>
> >> Basically, what this does is join the ProjectQuals table and UserQuals
> >> tables on the QualID field and count the number of matches for each
> >> Project,
> >> UserID combination.  It then filters that for those combinations where
> >> the
> >> Count of matching QualID fields is the same as the number of QualIDs for
> >> that ProjectID.
>
> >> HTH
> >> Dale
>
> >> <rmorri...@davislangdon.com.au> wrote in message
>
> >>news:7770f1cd-5588-4968-b512-78ce486fb050@s12g2000prg.googlegroups.com...
>
> >> > Hi, in my database I have tables for users (UserID, UserName),
> >> > projects (ProjectID, Project Name), and qualifications (QualID,
> >> > QualName). I have join tables for users_qualifications (UserID,
> >> > QualID), and projects_qualifications. (ProjectID, QualID).
>
> >> > What I need to do is run a query for a project to show which users
> >> > have the exact matching qualificiations.
>
> >> > Users can have many qualifications, projects can require many
> >> > qualifications, users may only work on a project if the qualifications
> >> > required/held match exactly.
>
> >> > Please help.- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks, that's almost what I want. How can I show a list of the users'
> > names with the appropriate qualifications. For example, if I am in a
> > project record and want to see who can work on the project, I'd like
> > to click on a button and bring up a list of the users. I've tried
> > modifying this query but I can't get it to work.
>
> > Hope you can help.- Hide quoted text -
>
> - Show quoted text -

Perfect! Thanks for your help!
0
rmorrison
12/5/2007 11:55:04 PM
Reply:

Similar Artilces:

I want year in one table to be less or equal year in another table
Hi I have some problems writing a query and I hope someone can help me. I have a database with serveal tables. In one table I have this information, Lake ID-number, treatment, year for treatment. In another table I have Lake ID-number, fish species (I am intrested in pike), year when pike is present. I want to find all lakes that have pike present before the treatment was done, I want the year in the second table to me less or equal the year in the first table. Is there a easy way to do this? Thanks Try something like this substituting your table and field names. S...

Controlling multiple TCP connections from Outlook 2003 clients
I've noticed that Outlook 2003 clients can have multiple TCP connections to the mailbox store on Exchange. As the following web link claims: http://www.windowsitpro.com/Article/ArticleID/46319/46319.html "This behavior is by design. Outlook can open multiple connections to parallelize its data communications with the Exchange server. The actual number of connections will vary according to the version of Outlook that you're using and the mode in which you're using it. For Microsoft Office Outlook 2003 in Cached Exchange Mode, you'll see four connections for the mailbox, ...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

"the wizard is unable to open your query in datasheet mode, possi
"the wizard is unable to open your query in datasheet mode, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" what this message mean ,and how can i solve it Hi - Please provide more details as to what you are doing when you get the error, what your database setup is (i.e. split Fe/BE?, multiuser?, version? etc). Without more information, we can only guess. Thanks - John amr wrote: >"the wizard is unable to open your query in datasheet mode, possibly because >another user has a ...

Yet another duplicate record dilemma
I have a table with records where one field are duplicates. I'm able to query to find duplicates and delete them, however what I need to do is find the duplicates, produce a total from another field, delete the duplicates and update the record field with the new total. Use the Find duplicates wizard, the build an Update query and either add to the field: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = MyField + Query1.MyField or just update it: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = Query1.MyField Then delete the duplicate data. -- Ar...

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Publishing Layout and tables
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am trying to copy and paste text from one cell of a table to another cell in the same table. The document is in Publishing Layout. The paste command deletes the text in the destination cell and then places a big empty text box on top of the table. I do dozens of these documents that are primarily tables and graphics. Previously I used Publisher on my old PC. Should I go back, or can this be done in Word for the mac? Hello, On 2010.01.29 8:44 AM, in article 59bb1ce2.-1@webcrossing.JaKIaxP2ac0, "Toni_T@officefor...

How to make a Add key disabled after adding one record unless
Hi, I have a form where I am adding records. There are two boxes where one has to put the lineitemnumber and another box where one has to put the description. Unless the above two boxes are filled in the Add command box need to be disabled. Now, the problem is after adding one record the Add button is enabled and I cannot disable it. I would appreciate any help to solve this. The error message is: The methos is not supported The following is the code: Private Sub cmdadd_Click() On Error GoTo Err_cmdadd_Click If (IsNull(Me.LineItemID) = True) Or (IsNull(Me.Description) = True) Then ...

Add new record through Form view
I have a table which is linked to a form, i would like to have a command button which will add a new record to my table. On the click of command button it should view the last empty row of my table in a form. On Sat, 26 Dec 2009 11:11:01 -0800, Ranjith Kurian <RanjithKurian@discussions.microsoft.com> wrote: >I have a table which is linked to a form, i would like to have a command >button which will add a new record to my table. >On the click of command button it should view the last empty row of my table >in a form. The Click event should show [Event Proced...

How do I convert a word table into an excel document?
I have managed to get the info accross no problem but the formatting is all over the place. For instance - 07/10 meaning July 2010 is appearing as 07/Oct despite me going into format cells custom then enter mm/yy which has always worked previously. Any ideas? You can't use it like that regardless of formatting, you need to put in the whole date or else Excel will always assume the current year so any real date used for calculations needs to be numeric and needs a day, so you can enter (assuming US date format) 07/01/10 and use a custom format of mm/yy or if you don't need it for...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

Two instances of outlook 2002 on the same desktop.
Hi, I have two sets of accounts that I manage with my outlook. I would like to be able to run two copies of outlook, each working on thier own set of accounts. Could any one help me understand how we can do this , please? I would appreciate any help. Thanks much. Raj ...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

Excel pivot table #2
i encountered an error in my pivot table. i created an olap cube using the analysis manager. the cube displays the correct data of my measures but on my pivot report, it displays #N/A.... i need help to fix this one... thanks.... =) ...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

Run series of query -- I don't want any pop-up
I have generated 4 queries (1. empty all, 2. import data, 3. generate information, 4. Make a new table) I've created a button to run these 4 quries sequencially. ------------------------------------------------------------------------------------ Problem: MS ACCESS pops up "confirmation dialog" to ask me whether to delete/modify the table. ------------------------------------------------------------------------------------ Question: I don't want any question to be asked. I just want those queries to be executed once i press my button. -------------------------------...

Tables in 2007
What are the advantages to converting a list to a table in 2007? Why does the table have a name? I did read in help that you can post a 2007 table to sharpoint services. What does that mean? -- Thanks. Confused Hi, Go to the excel help and type Tables then open the one that says Demo: Organize your data by using an Excel table, and then in How to do it, click on Overview of Excel Tables if this helps please click yes, thanks "Confused" wrote: > What are the advantages to converting a list to a table in 2007? Why does > the table have a name? > > I did read...

Deleting a Batch from company DB table SY00500
This is a multi-part message in MIME format. ------=_NextPart_000_002F_01C96F54.F7568BB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there a way to see all the TRX in a Batch saved in SY00500. Also, what if we delete a batch in SY00500 will there be any other place = to look for; besides this? I had a batch sitting here for a long time = and there were few (3) trxs ( Sales Credits) in it as well. I wonder if I could retrieve those one by one and then delete them as = well as these belong to a fiscal period which is closed plus we...

Does table size make a difference in how well Access protects data
I am having a problem that has been going on for several weeks now. I am slowly trying to figure it out. the problem is that Access 2003 keeps deleting records after they are entered. Quick view of database: running Access 2k3 back end and frontends, 12 machines access the DB at any one time, mixture of Win2k Pro and Win XP pro all patches and service packs installed for all OS's as well as Access 2k3. Running Client/server set up controlled by Small Business Server 2k3. database resides on a machine running XP Pro. when new orders are entered into the system, the DB will tell the d...

Email to multiple people but show only the recepient name
Is there a way to send to ten people at once but only have one name in the To: box at a time? So that the entire list of people is not sent. Use the BCC field. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Terry asked: | Is there a way to send to ten people at once but only | have one name in the To: box at a time? So that the | entire list of people is not sen...

How do I get Outlook Today to show appts from multiple calendars?
I want the opening page (Outlook Today) to show the next week's appointments from 2 calendars. I can only get it to show one calendar and it defeats the purpose of having the summary. Ideas? If you provide some basic information like: version of Outlook where the two Calendars are located (eg is one a Public Folder, a shared Calendar, a sub-folder of your mail Calendar) you'll find getting a useful answer here much faster and less laborious. :-) Regards Judy Gleeson MVP Outlook in Canberra, Australia "MORE CALENDAR" <MORE CALENDAR@discussions....