Sue,
I didn't provide much of an explanation of how it works so let me know if you have any questions.
Duane Hookom MVP
MS Access
----------------------------------------
> From: sue@questor-cp.co.uk
>
> Hello Duane
>
> Thanks for your reply - sorry for the delay in responding, that looks as though it might work. I'll have a look at it later in the context of the query - I left another table out - just querying one field on it - when I posted the SQL because I found I had to do it all in design view to get my head round the syntax of all the inner joins as it was! If it doesn't seem to work I might come back & post my SQL as I'll likely have messed up the syntax! Thanks again
>
> 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 (7) |
Tidak ada komentar:
Posting Komentar