RE: [MS_AccessPros] Amalgamating data using only a query

Sue,

Consider the Employees table in Northwind with fields including LastName and Title. 
(I'm not sure how these tables will render in my response)

Employees
TitleOfCourtesy  Last Name
Ms. Davolio
Dr. Fuller
Ms. Leverling
Mrs. Peacock
Mr. Buchanan
Mr. Suyama
Mr. King
Ms. Callahan
Ms. Dodsworth
Mr. Hookom

If you want to get all of the titles in the left column with employees listed in separate columns, you can combine ranking and crosstab queries into one SQL statement:

TRANSFORM First(Ranking.LastName) AS FirstOfLastName
SELECT Ranking.TitleOfCourtesy
FROM (SELECT Employees.TitleOfCourtesy, Employees.LastName, Count(Employees_1.TitleOfCourtesy) AS ColHead
FROM Employees INNER JOIN Employees AS Employees_1 ON Employees.TitleOfCourtesy = Employees_1.TitleOfCourtesy
WHERE (((Employees.LastName)>=[Employees_1.LastName]))
GROUP BY Employees.TitleOfCourtesy, Employees.LastName)  AS Ranking
GROUP BY Ranking.TitleOfCourtesy
PIVOT "Employee" & [ColHead];

Query4
TitleOfCourtesy  Employee1 Employee2 Employee3 Employee4
Dr. Fuller


Mr. Buchanan Hookom King Suyama
Mrs. Peacock


Ms. Callahan Davolio Dodsworth Leverling


Duane Hookom MVP
MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: sue@questor-cp.co.uk
> Date: Fri, 10 Jan 2014 16:24:26 +0000
> Subject: RE: [MS_AccessPros] Amalgamating data using only a query
>
> Hello Bill
>
> Thanks for replying; I thought that was probably the case. This
> application has some built in reports and custom reports but they are
> pretty basic, and as far as I can see the limitations are the same – if
> I create a report or Word doc I am still limited by the fields that are
> there and the scope of a single SQL query. The form has 3 parts – a
> front page which is kept by me and has the customer's details, plus
> details of each cat and its diet, care etc – the customer signs that
> and I need to keep it as proof of agreement and in case the licensing
> department wants to see it, a run form with all the info in it, and a
> different sheet for the customer to take away.
>
> I did build the query I posted in the back end – a backup of it,
> anyway!, because it was impossible to test it properly otherwise. If I
> were writing the sub to do it I would have it run a query and then
> iterate through any cats, populating those parts of the form. I
> probably need to push myself to get on and do the necessary work on my
> system!
>
> Thanks again for the help
>
> Regards
> Sue
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> wrmosca@comcast.net
> Sent: 10 January 2014 15:05
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Amalgamating data using only a query
>
>
>
>
>
>
> Sue
>
> You're not able to do that in a query. Even if you used a cross tab
> query you can only put one field to return values across the row.
>
>
>
> If the Access back end is available to you you could create a report
> there provided it isn't too locked down. Or you could create an Access
> front end and link to the tables and make your report in the front end.
>
>
>
> An Access report will give you the ability to use a group header to
> show the customer info and then the detail would be the pet stuff. Or
> you could use a report for the customer stuff and a sub report for the
> pet stuff.
>
>
>
> Regards,
>
> Bill Mosca, Founder - MS_Access_Professionals
>
> http://www.thatlldoit.com
>
> Microsoft Office Access MVP
>
> http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
>
> My nothing-to-do-with-Access blog
>
> http://wrmosca.wordpress.com
>
>
>
>
>
>
>
>
>
>

------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/