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
Sent: 10 January 2014 16:38
To: Access Professionals Yahoo Group
Subject: 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
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
Tidak ada komentar:
Posting Komentar