[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query help needed

Posted on 2011-10-12
17
Medium Priority
?
216 Views
Last Modified: 2012-05-12
Experts,
The attached word document makes reading my querstion easier. Basically, I need to change my query output from vertical to horizontal.

Experts,
My form frmModifyDesignatedEmployee is based upon the table tblDesignatedEmployee as follows:

DesignatedEmployeeID (PK)
StoreID (FK)
EmployeeID (FK)
EmployeeTypeID (FK)

Name: cboStoreID   Control Source: StoreID from the table tblStore. Here is the query for the combo control:
SELECT tblStore.StoreNumberID, "# " & [StoreNumber] & " " & [StoreAddressOne] AS Store, 
tblEmployee.EmployeeFirstName, tblEmployeeType.EmployeeType 

FROM tblEmployeeType INNER JOIN ((tblStore INNER JOIN tblDesignatedEmployee ON tblStore.StoreNumberID = tblDesignatedEmployee.StoreID) 
INNER JOIN tblEmployee ON tblDesignatedEmployee.EmployeeID = tblEmployee.EmployeeID) ON 
(tblDesignatedEmployee.EmployeeTypeID = tblEmployeeType.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND 
(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) 

GROUP BY tblStore.StoreNumberID, "# " & [StoreNumber] & " " & [StoreAddressOne], tblEmployee.EmployeeFirstName, tblEmployeeType.EmployeeType;

Open in new window


Here’s the output I am getting from the above query:
StoreNumberID		Store	             	EmployeeFirstName	EmployeeType
3			#1 7885 Hwy 51 North	Antoine			Store Manager
3			# 1 7885 Hwy 51 North	Mildred	Area 		Support Manager
3			# 1 7885 Hwy 51 North	Samanthia		Assistant Manager
3			# 1 7885 Hwy 51 North	Sherry			District Manager

Open in new window


Here’s the output I need:
Rather than listing the output vertically, I need to change the orientation horizontally where one store lists the employee’s name and types.

Query-help.docx
0
Comment
Question by:Frank Freese
  • 8
  • 4
  • 2
  • +1
15 Comments
 

Author Comment

by:Frank Freese
ID: 36957284
please help me here - I do not understand the issue?
0
 

Author Comment

by:Frank Freese
ID: 36957300
I think what you're saying is there's a better way to post my question. If that's the case, how did you post it the way you did?
0
 

Author Comment

by:Frank Freese
ID: 36957730
Thanks for the help on posting a question. I'll do that going forward.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Norie
ID: 36959809
Can you post a sample of how you want the output?

Also, and this is kind of off topic, why is this repeated?

(tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID) AND

If you need it you only need it once.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 36959992
Attach a sample database, with few records, demonstrating the issue. List the required output.
0
 

Author Comment

by:Frank Freese
ID: 36961683
attached db - opens to form at the StoreID bound control. StoreBonus.mdb
0
 
LVL 31

Expert Comment

by:hnasr
ID: 36964701
Please supply instructions to follow to demonstrate the issue.
I selected the only record in the StoreID combo, but nothing shows up.
0
 

Author Comment

by:Frank Freese
ID: 36965099
I've attached another db. Look at the query in the combo box storeid. Run the query. You should get results similiar to what I first posted. What I need is to have a query that lists across, e.g.
District Manager  Support Assistant  Store Manager Assistant Store Manager not
District Manager
Support Assistant
Store Manager
Assistant Store Managr

Hope this helps
StoreBonus.mdb
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 36965300
@fh_freese

Since you've already created one column expression in your query, you should be able to create new ones for the data layout you need.

This is the column you created and named "Store"
"# " & [StoreNumber] & " " & [StoreAddressOne] AS Store, 

Open in new window


What you want will be a Select From Where query inside parentheses with a name.  It will look something like this:
SELECT DISTINCT tblStore.StoreNumberID, tblStore.StoreNumber, 
"# " & [StoreNumber] & " " & [StoreAddressOne] AS Store, 
(Select tblEmployee.EmployeeFirstName 
FROM tblEmployee INNER JOIN tblDesignatedEmployee 
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=1) AS DistrictManager, 
(Select tblEmployee.EmployeeFirstName 
FROM tblEmployee INNER JOIN tblDesignatedEmployee 
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=2) AS StoreManager,
(Select tblEmployee.EmployeeFirstName 
FROM tblEmployee INNER JOIN tblDesignatedEmployee ON 
tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=3) AS AssistantManager,
(Select tblEmployee.EmployeeFirstName 
FROM tblEmployee INNER JOIN tblDesignatedEmployee 
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=4) AS AreaSupportManager

FROM tblStore;

Open in new window

0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 2000 total points
ID: 36965329
You can create more useful columns if you use both first and last names in your four new column expressions:

(Select tblEmployee.EmployeeFirstName & " " & tblEmployee.EmployeeLastName 

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 36965435
So very, very, very close.....wow! I need to see why I'm not getting anything back but a blank on Area Support, which can, by the way, be a 4 or a 5. Let me look at this, and if I cannot figure it out I'll let you know. just fantastic.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 36965464
5 is none
0
 

Author Comment

by:Frank Freese
ID: 36965488
just saw that - fixed the problem and again, this seems to be exactly what I need. thank you - well earned points
0
 

Author Closing Comment

by:Frank Freese
ID: 36965492
again, thank you!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 36965605
You're welcome.

Note to future readers:  This problem might can also be solved with a crosstab query.

If we create a query [Query2]
SELECT tblStore.StoreNumberID, "#" & [StoreNumber] & " " & [StoreName] & " " & [StoreAddressOne] AS StoreNumNameAddr, 
tblEmployeeType.EmployeeType, [EmployeeFirstName] & " " & [EmployeeLastName] AS EmpName
FROM tblEmployeeType INNER JOIN (tblStore INNER JOIN (tblDesignatedEmployee 
INNER JOIN tblEmployee ON tblDesignatedEmployee.EmployeeID = tblEmployee.EmployeeID) 
ON tblStore.StoreNumberID = tblDesignatedEmployee.StoreID) 
ON tblEmployeeType.EmployeeTypeID = tblEmployee.EmployeeTypeID
WHERE (((tblEmployeeType.EmployeeTypeID) Between 1 And 4));

Open in new window


Then the following crosstab query produces the same results as above:

TRANSFORM First(Query2.EmpName) AS FirstOfEmpName
SELECT Query2.StoreNumberID, Query2.StoreNumNameAddr
FROM Query2
GROUP BY Query2.StoreNumberID, Query2.StoreNumNameAddr
PIVOT Query2.EmployeeType;

Open in new window

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question