Stuart-
Bill is correct, but setting certain properties does cause the SQL to change.
Setting the Top Values property to something other than All adds the TOP
keyword.
Setting Unique Values to Yes adds the DISTINCT keyword.
Setting Unique Records to Yes adds the DISTINCTROW keyword. (DISTINCTROW is a
keyword unique to Access SQL.)
Setting the Source Database or Source Connect Str properties adds the IN clause
in the FROM clause (also unique to Access)
Clicking the Totals button on the toolbar adds the GROUP BY and HAVING clauses.
Setting field properties such as Format, Input Mask, and Caption does not change
the SQL, but these do affect the output you see.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
Sent: Tuesday, May 17, 2011 11:54 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Showing fields in a report which don't exist
Stuart
Query "properties" don't show in SQL view, only the SQL syntax. I've been
writing SQL for so long I usually start out in the design view and then switch
to SQL view to add parameters, pivot headers, nested queries, etc.
You can learn a lot how a query works by seeing the SQL.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "Stuart Luckman"
<stuartluckman@...> wrote:
>
> Hi Bill,
>
>
>
> It's always good to learn the SQL version of how to do something.
>
> Does this mean most of the query properties have an SQL translation?
>
>
>
> Thanks,
>
> Stuart
>
> My Signature
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
> Sent: Wednesday, 18 May 2011 2:22 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Showing fields in a report which don't exist
>
>
>
>
>
> Stuart
>
> You can force all headings by editing the SQL. Open the crosstab in SQL view
> and add the headings like this:
>
> TRANSFORM blah
> SELECT blah
> FROM blah
> PIVOT Season In("Autumn", "Winter", "Spring", "Summer")
>
> This will also force the order in which they appear.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Stuart Luckman"
> <stuartluckman@> wrote:
> >
> > Hi all,
> >
> > I'm making a report which lists Plant Name and Sizes as row headings and
> Autumn, Winter, Spring, Summer as column headings and two different numbers
> summed for the values.
> >
> > I have a union query which brings the two different values together and
> then a crosstab query to get the column headings and it all works fine.
> >
> > At the moment if you only have entries for Spring or Summer then they are
> the only headings you get in the query, I would like for the query to show
> Autumn and Winter as well with a zero for the value.
> >
> > Then I would have a control source for all the controls on my report.
> >
> > Thanks for any help,
> >
> > Stuart
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar