Sue,
I first created a ranking query that assigns consecutive numbers to a group of records. This uses two copies of the employees table joined on the field that I wanted to group by (TitleOfCourtesy). A ColHead field is created with numbers 1 through whatever based on the LastName field order. Notice the grouping, counting, and WHERE clause:
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;
I then used this SQL as a subquery aliased as [Ranking] in a crosstab query that uses the TitleOfCourtesy as the Row Heading, "Employee" and ColHead as the Column Heading, and LastName as the value.
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];
I typically would have left this a two separate queries but assumed you wanted it as one query. In the SQL above, I have added extra blank lines to show where the first SQL was inserted into the final crosstab.
A simple example of the query within a query is:
SELECT A.*
FROM (SELECT * FROM Employees) A
Which is the same as:
SELECT * FROM Employees
I hope this makes sense and works for you.
Duane Hookom MVP
MS Access
----------------------------------------
> From: sue@questor-cp.co.uk
>
> Hello Duane
>
> Yes, please, do explain it to me. Because my form's bookmarks allow for filling in the details of 4 cats, even if there are only two cats I need the headers for all the fields in the csv file. This does look perfect, I just want to make sure I understand it properly. My system in particular has a lot of queries that it redefines on the fly but in general they tend to be relatively basic.
>
> Many thanks, I shall be thrilled if this works as I want it to.
>
> Sue
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
>
> 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
>
> ________________________________
>> From: sue@questor-cp.co.uk
>>
>> 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
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar