Query help needed

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
Frank FreeseAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
@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
 
Frank FreeseAuthor Commented:
please help me here - I do not understand the issue?
0
 
Frank FreeseAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Frank FreeseAuthor Commented:
Thanks for the help on posting a question. I'll do that going forward.
0
 
NorieVBA ExpertCommented:
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
 
hnasrCommented:
Attach a sample database, with few records, demonstrating the issue. List the required output.
0
 
Frank FreeseAuthor Commented:
attached db - opens to form at the StoreID bound control. StoreBonus.mdb
0
 
hnasrCommented:
Please supply instructions to follow to demonstrate the issue.
I selected the only record in the StoreID combo, but nothing shows up.
0
 
Frank FreeseAuthor Commented:
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
 
aikimarkConnect With a Mentor Commented:
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
 
Frank FreeseAuthor Commented:
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
 
aikimarkCommented:
5 is none
0
 
Frank FreeseAuthor Commented:
just saw that - fixed the problem and again, this seems to be exactly what I need. thank you - well earned points
0
 
Frank FreeseAuthor Commented:
again, thank you!
0
 
aikimarkCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.