T
Teresa Redmond
Hello all,
I'm new to Access, not to programming or SQL in general. I use VB,
but this is something I can't do that way for installation restriction
purposes.
Anyway, I need my query to be able to put together information from 5
tables, of the following format:
User_Info:
User_PKEY
First_Name
Middle_Initial
Last_Name
VIP
Department
Division
Phone_EXT
NetID
Building
Room
Floor
NMCI_User_Name
Dev_Acct
Seat_ID_Nums:
Seat_PKEY
User_PKEY
Seat_ID
Asset_ID
Machine_Name
CLIN_PKEY
Addl_CLIN1_PKEY
....
Addl_CLIN12_PKEY
Main_Table:
ID
Seat_PKEY
User_PKEY
[Date Scheduled to Deploy]
[Actual Date Accepted]
Notes
Seat_CLIN_Nums:
CLIN_PKEY
Seat_CLIN
Additional_CLIN_Nums:
Add_CLINs_PKEY
Additional_CLINs
One query I need is based on user's Last_Name. I can get all the
information I need, but the problem is the Additional_CLIN#_PKEY to
Add_CLINs_PKEY result. If one user has 4 additional CLINs on the
seat, there are 4 PKEYs in that user's corresponding record in
Seat_ID_Nums. A machine could have up to 12 additional CLINs. When I
run the query, the user who has 4 additional CLINs shows up 4 times, 4
separate records, once for each additional CLIN, and that number is
displayed in all 12 of the possible fields. What I want is one record
with the four additional CLINs (one per field) in each of the first
four additional CLIN fields.
I am working on putting this behind a form where the user can search
by 5 different parameters, but I want to get this query correct before
I go on to that, and that question may go in a different group,
because it deals with combo boxes and requerying, which isn't working
right either.
This is the SQL that I got with forming the query in the wizard and
then editing, changing the AND that the wizard put in with the OR that
I need to be certain to match all possibilities. I don't know that
Additional_CLIN_Nums.Additional_CLINs needs to be in there twelve
times, it just needs to be there if Seat_ID_Nums.Addl_CLIN#.PKEY is
not 0 or null. Any help is *greatly* appreciated!
<begin sql>
SELECT User_Info.Last_Name, User_Info.First_Name,
User_Info.Middle_Initial, User_Info.VIP, User_Info.Department,
User_Info.Division, User_Info.Phone_EXT, User_Info.NetID,
User_Info.Building, User_Info.Room, User_Info.Floor,
User_Info.NMCI_User_Name, User_Info.Dev_Acct, Seat_ID_Nums.Seat_ID,
Seat_ID_Nums.Asset_ID, Seat_ID_Nums.Machine_Name,
Seat_CLIN_Nums.Seat_CLIN, Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs, Main_Table.[Date Scheduled to
Deploy], Main_Table.Notes
FROM Seat_CLIN_Nums INNER JOIN ((Additional_CLIN_Nums INNER JOIN
(User_Info INNER JOIN Seat_ID_Nums ON User_Info.User_PKEY =
Seat_ID_Nums.User_PKEY) ON (Additional_CLIN_Nums.Add_CLINs_PKEY =
Seat_ID_Nums.Addl_CLIN12_PKEY) OR (Additional_CLIN_Nums.Add_CLINs_PKEY
= Seat_ID_Nums.Addl_CLIN11_PKEY) OR
(Additional_CLIN_Nums.Add_CLINs_PKEY = Seat_ID_Nums.Addl_CLIN10_PKEY)
OR (Additional_CLIN_Nums.Add_CLINs_PKEY =
Seat_ID_Nums.Addl_CLIN9_PKEY) OR (Additional_CLIN_Nums.Add_CLINs_PKEY
= Seat_ID_Nums.Addl_CLIN8_PKEY) OR
(Additional_CLIN_Nums.Add_CLINs_PKEY = Seat_ID_Nums.Addl_CLIN7_PKEY)
OR (Additional_CLIN_Nums.Add_CLINs_PKEY =
Seat_ID_Nums.Addl_CLIN6_PKEY) OR (Additional_CLIN_Nums.Add_CLINs_PKEY
= Seat_ID_Nums.Addl_CLIN5_PKEY) OR
(Additional_CLIN_Nums.Add_CLINs_PKEY = Seat_ID_Nums.Addl_CLIN4_PKEY)
OR (Additional_CLIN_Nums.Add_CLINs_PKEY =
Seat_ID_Nums.Addl_CLIN3_PKEY) OR (Additional_CLIN_Nums.Add_CLINs_PKEY
= Seat_ID_Nums.Addl_CLIN2_PKEY) OR
(Additional_CLIN_Nums.Add_CLINs_PKEY = Seat_ID_Nums.Addl_CLIN1_PKEY))
INNER JOIN Main_Table ON (Seat_ID_Nums.Seat_PKEY =
Main_Table.Seat_PKEY) AND (User_Info.User_PKEY =
Main_Table.User_PKEY)) ON Seat_CLIN_Nums.CLIN_PKEY =
Seat_ID_Nums.CLIN_PKEY
ORDER BY User_Info.Last_Name;
</end sql>
--
Teresa Redmond
Anteon Corporation
--
~teresa~
AFH Barwench
^..^ "Never try to outstubborn a cat." Robert A. Heinlein ^..^
http://pixelmeow.com/ http://www.heinleinsociety.org/
http://pixelmeow.com/Book_Exchange/index.htm
http://pixelmeow.com/forum/
aim: pixelmeow msn: [email protected]
I'm new to Access, not to programming or SQL in general. I use VB,
but this is something I can't do that way for installation restriction
purposes.
Anyway, I need my query to be able to put together information from 5
tables, of the following format:
User_Info:
User_PKEY
First_Name
Middle_Initial
Last_Name
VIP
Department
Division
Phone_EXT
NetID
Building
Room
Floor
NMCI_User_Name
Dev_Acct
Seat_ID_Nums:
Seat_PKEY
User_PKEY
Seat_ID
Asset_ID
Machine_Name
CLIN_PKEY
Addl_CLIN1_PKEY
....
Addl_CLIN12_PKEY
Main_Table:
ID
Seat_PKEY
User_PKEY
[Date Scheduled to Deploy]
[Actual Date Accepted]
Notes
Seat_CLIN_Nums:
CLIN_PKEY
Seat_CLIN
Additional_CLIN_Nums:
Add_CLINs_PKEY
Additional_CLINs
One query I need is based on user's Last_Name. I can get all the
information I need, but the problem is the Additional_CLIN#_PKEY to
Add_CLINs_PKEY result. If one user has 4 additional CLINs on the
seat, there are 4 PKEYs in that user's corresponding record in
Seat_ID_Nums. A machine could have up to 12 additional CLINs. When I
run the query, the user who has 4 additional CLINs shows up 4 times, 4
separate records, once for each additional CLIN, and that number is
displayed in all 12 of the possible fields. What I want is one record
with the four additional CLINs (one per field) in each of the first
four additional CLIN fields.
I am working on putting this behind a form where the user can search
by 5 different parameters, but I want to get this query correct before
I go on to that, and that question may go in a different group,
because it deals with combo boxes and requerying, which isn't working
right either.
This is the SQL that I got with forming the query in the wizard and
then editing, changing the AND that the wizard put in with the OR that
I need to be certain to match all possibilities. I don't know that
Additional_CLIN_Nums.Additional_CLINs needs to be in there twelve
times, it just needs to be there if Seat_ID_Nums.Addl_CLIN#.PKEY is
not 0 or null. Any help is *greatly* appreciated!
<begin sql>
SELECT User_Info.Last_Name, User_Info.First_Name,
User_Info.Middle_Initial, User_Info.VIP, User_Info.Department,
User_Info.Division, User_Info.Phone_EXT, User_Info.NetID,
User_Info.Building, User_Info.Room, User_Info.Floor,
User_Info.NMCI_User_Name, User_Info.Dev_Acct, Seat_ID_Nums.Seat_ID,
Seat_ID_Nums.Asset_ID, Seat_ID_Nums.Machine_Name,
Seat_CLIN_Nums.Seat_CLIN, Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs,
Additional_CLIN_Nums.Additional_CLINs, Main_Table.[Date Scheduled to
Deploy], Main_Table.Notes
FROM Seat_CLIN_Nums INNER JOIN ((Additional_CLIN_Nums INNER JOIN
(User_Info INNER JOIN Seat_ID_Nums ON User_Info.User_PKEY =
Seat_ID_Nums.User_PKEY) ON (Additional_CLIN_Nums.Add_CLINs_PKEY =
Seat_ID_Nums.Addl_CLIN12_PKEY) OR (Additional_CLIN_Nums.Add_CLINs_PKEY
= Seat_ID_Nums.Addl_CLIN11_PKEY) OR
(Additional_CLIN_Nums.Add_CLINs_PKEY = Seat_ID_Nums.Addl_CLIN10_PKEY)
OR (Additional_CLIN_Nums.Add_CLINs_PKEY =
Seat_ID_Nums.Addl_CLIN9_PKEY) OR (Additional_CLIN_Nums.Add_CLINs_PKEY
= Seat_ID_Nums.Addl_CLIN8_PKEY) OR
(Additional_CLIN_Nums.Add_CLINs_PKEY = Seat_ID_Nums.Addl_CLIN7_PKEY)
OR (Additional_CLIN_Nums.Add_CLINs_PKEY =
Seat_ID_Nums.Addl_CLIN6_PKEY) OR (Additional_CLIN_Nums.Add_CLINs_PKEY
= Seat_ID_Nums.Addl_CLIN5_PKEY) OR
(Additional_CLIN_Nums.Add_CLINs_PKEY = Seat_ID_Nums.Addl_CLIN4_PKEY)
OR (Additional_CLIN_Nums.Add_CLINs_PKEY =
Seat_ID_Nums.Addl_CLIN3_PKEY) OR (Additional_CLIN_Nums.Add_CLINs_PKEY
= Seat_ID_Nums.Addl_CLIN2_PKEY) OR
(Additional_CLIN_Nums.Add_CLINs_PKEY = Seat_ID_Nums.Addl_CLIN1_PKEY))
INNER JOIN Main_Table ON (Seat_ID_Nums.Seat_PKEY =
Main_Table.Seat_PKEY) AND (User_Info.User_PKEY =
Main_Table.User_PKEY)) ON Seat_CLIN_Nums.CLIN_PKEY =
Seat_ID_Nums.CLIN_PKEY
ORDER BY User_Info.Last_Name;
</end sql>
--
Teresa Redmond
Anteon Corporation
--
~teresa~
AFH Barwench
^..^ "Never try to outstubborn a cat." Robert A. Heinlein ^..^
http://pixelmeow.com/ http://www.heinleinsociety.org/
http://pixelmeow.com/Book_Exchange/index.htm
http://pixelmeow.com/forum/
aim: pixelmeow msn: [email protected]