A subquery! of course. :)
Thanks!...
JRBower
Last Activity: Mar 20 '08, 06:40 AM
Joined: Sep 5 '07
Location:
-
CK,
As a follow up to my initial enquiry I should say that my Countries table is basically layed out like this:
etcCode:[U]:::CountryID || Country || RegionFilter[/U] [U]__1 _________|| America || Americas[/U] [U]__2_________ || Canada || Americas[/U] [U]__3_________ || England || Europa[/U] [U]__4________._|| France. || Europa[/U]
And of course I can filter FK tables like this:
...Code:WHERE dbo.Users.CountryID
Leave a comment:
-
Hi CK,
Thanks again for your time. Perhaps I should have used a different table for my example. The suggestion you gave works as I would have expected, however, the contacts table is a special case where I need to sort on the country that is associated with the UserID for the Users table (dbo.Users on dbo.Users.UserI D = dbo.Contacts.Us erID) and not the ContactID (Contacts.Conta ctID) of the UserContact table. The reason in this case is that...Leave a comment:
-
How do you apply PK filters to FK tables?
In my main users table I filter job apps to one of four regions. Here's the code for one region:
...Code:SELECT Users.UserID, Users.FirstName, Users.LastName, Users.Age, Nationalities.Nationality, Genders.Gender, NativeLanguages.NativeLanguage, Users.City, States.State, Countries.Country, Users.ApplicationDate FROM dbo.Users left join dbo.Nationalities on dbo.Users.NationalityID = dbo.Nationalities.NationalityID
-
Bingo!
Now I see that it is necessary to actually write out the SQL code rather than rely on the Access Upsize Wizard to write it to the SQL database. It seems that some table relationships are transferred via the Wizard where referential integrity is involved but left join choices aparently miss the boat. Your code is bang on and very instructive. Thank you!
Regards,
JRBowerLeave a comment:
-
Hi CK,
Thanks for your effort. I've tried 3 join types and I'm still getting the same results. I suspect I might not be explaining the problem correctly or using the language (db jargon) accurately since your responses aren't moving me closer to a solution. Basically, the auto created record won't appear in the admin-side datagrid unless a State and Country has been inserted during the auto-creation process. One solution, which I don't like,...Leave a comment:
-
CK,
Thanks for your reply. You are correct that I am referring to the "value" which I guess acts like a placeholder in an SQL record until a literal value is inserted. The SQL 2005 database I'm working with was upsized from Access 2007 and the table join was case #3: Include ALL records from 'Users' and only those records from 'Countries' where the joined fields are equal.
Usually I don't have any problem with this...Leave a comment:
-
Table records will not appear...if
I'm experiencing an odd problem in which table records will not appear in a Visual Query Builder or in a datagrid UI unless certain dropdown data is inserted. For example I have two comboboxes (State, Country) in the following code:
...Code:SELECT OverseasEvaluations.OverseasEvaluationID, OverseasEvaluations.UserID, Users.FirstName, Users.LastName, OverseasEvaluations.PhoneInterviewDate, OverseasEvaluations.PointTotal, OverseasEvaluations.City,
-
Hi CK,
Thanks for your attention. I decided to just make another lookup table which solved the problem.
Cheers,
James...Leave a comment:
-
Thanks for your assistance CK. Actually my original code was as you suggested (Countries.Nati onality) but for some reason it's not producing the right nationality. When I changed it to Recruiters.Nati onality the correct row was selected but only the ID number would dislplay. What seems intuitive to me (and follows the same logic asis to add:Code:AND dbo.Recruiters.CountryID = dbo.Countries.CountryID
...Code:AND dbo.Recruiters.Nationality
Leave a comment:
-
correct row ID but not the text
I'm having trouble displaying text for 'Nationality' in a table. I can display the correct row ID (using the below SQL) but not the text. I'm sure I'm overlooking something simple:
Here's my SQL code:
...Code:SELECT Recruiters.RecruiterID, Recruiters.FirstName, Recruiters.LastName, JobTitles.JobTitle, Recruiters.City, States.State, Countries.Country, Recruiters.Nationality FROM dbo.Recruiters, dbo.JobTitles, dbo.States,
-
Yes, indeed it does work fine! :) I realized that what was causing the error before was in the first select we had Branches.Branch es but in the second select it was 12 as BranchID. Of course your original suggestion was correct in its scripting (without the table defs) however I needed to change 12 AS Branches (your script) to 12 AS BranchID. Now that I have 'San Diego' AS Branches corresponding to Branches.Branch es the UNION works like a charm...Leave a comment:
-
Thanks for your help. I tried to execute the query but I get an error related to the UNION statement. If I run the select statements separately they work fine.
I made a couple small corrections.
...Code:SELECT COUNT(Employees.EmployeeID) AS Count_EmployeeID, 12 AS [B]BranchID[/B], UserInRole.UserRoleID FROM dbo.Employees, dbo.Branches, dbo.UserInRole WHERE dbo.Employees.BranchID = dbo.Branches.BranchID
Leave a comment:
-
Need Help to do a GROUP SUM
The SQL below displays an employee count for 14 branches.
What I'd like to do is keep the individual counts for branches 1 through 11 but have branch 12 be the sum of branch 12, 13, 14.
SELECT COUNT(Employees .EmployeeID) AS Count_EmployeeI D, Branches.Branch es, UserInRole.User RoleID
FROM dbo.Employees, dbo.Branches, dbo.UserInRole
WHERE dbo.Employees.B ranchID = dbo.Branches.Br anchID...
No activity results to display
Leave a comment: