Re: Inline CrossTab Query
From: laura (replyto_at_group.com)
Date: 02/14/05
- Next message: Chris2: "Re: Help with Append Query"
- Previous message: Shawn: "system resources exceeded error"
- In reply to: Chris2: "Re: Inline CrossTab Query"
- Next in thread: Chris2: "Re: Inline CrossTab Query"
- Reply: Chris2: "Re: Inline CrossTab Query"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 14 Feb 2005 13:58:46 -0000
sqlCurrentEvents = ("SELECT * FROM (TRANSFORM First(Event) AS
FirstOfEvent " _
& "SELECT fullname " _
& "FROM (SELECT tblAllDates.day, tblEvent.EventDate, tblEvent.Event,
[sname] & ' ' & [fname] AS fullname " _
& "FROM tblAllDates LEFT JOIN (tblEvent LEFT JOIN tblEmployee ON
tblEvent.EmployeeID = tblEmployee.EmployeeID) ON tblAllDates.day =
tblEvent.EventDate " _
& "WHERE (((Month([day]))=Month(Date())) AND
((Year([day]))=Year(Date()))) " _
& "ORDER BY tblAllDates.day) " _
& "GROUP BY fullname " _
& "ORDER BY Format([day],'dd') & ' ' & Format([day],'ddd') " _
& "PIVOT Format([day],'dd') & ' ' & Format([day],'ddd')) " _
& "WHERE (((fullname)>''))")
The SQL I used I just copied from the three queries in the Query Design
window.. I just eliminated the actual names of the queries from the
statement above.
I hope you can help - I've been on this for a week now and have not made any
progress with the rest of my program.
Thanks
Laura TD
"Chris2" <rainofsteel.NOTVALID@GETRIDOF.luminousrain.com> wrote in message
news:7bOdneQsOfgsMY3fRVn-iA@comcast.com...
> See Below:
>
> "laura" <replyto@group.com> wrote in message
> news:e5SGijpEFHA.2180@TK2MSFTNGP10.phx.gbl...
>> I am trying to produce a SQL statement to bring together 3 queries
> that work
>> if I create them in the Query Design Window. That is what I meant by
>> "inline" (bad use of terminology - sorry).
>>
>> I am producing a travel and absence program for employees. I want to
> show
>> every day of a selected month across the top, employee names down
> the side
>> and the locations they are travelling to in the grid.
>>
>> The first query brings in data from 3 tables.
>> The second query is a Crosstab query to flip the data over - days of
> the
>> month across the top, employee names at the sides.
>> The third query eliminates one blank record which is produced as a
> result of
>> the way I have designed the other two queries.
>>
>> The reason I want to create an SQL statement is that I want to be
> able to
>> pass parameters (taken from an ASP page, not an Access Form) to the
> first
>> query which will set the month and year for the report.
>>
>> As you can imagine, the SQL statement is quite long and I have been
> able to
>> put together queries 1 and 2, but the third, very simple query that
> says
>> something like "SELECT * FROM (TRANSFORM <2nd query
> crosstab>........ ...
>> (SELECT etc..<first query which will filter the month and year>))
> WHERE
>> fullname > ' ' "
>>
>> fullname is in the crosstab query - I want to eliminate one blank
> record and
>> this is the only way I can eliminate it without messing up the
> crosstab
>> query.
>>
>> I am getting an error message that says there is an Error in the
> FROM
>> clause.
>
> Laura,
>
> Here it is, there is a problem in the FROM clause.
>
> I order to search for that, I'll need to look at the SQL.
>
> Can you please post the full SQL that is producing the error (for all
> Queries involved), and the exact text of the error message?
>
>
> Sincerely,
>
> Chris O.
>
>
>> The SQL works without the 3rd query, but as soon as I add it, I get
>> the error message. It also works when I run the three queries in the
> design
>> window, but not as SQL statement.. It may just be a case of the
> first SELECT
>> statement being wrong - I just cannot work it out.
>>
>> Thanks
>> Laura TD
>
>
- Next message: Chris2: "Re: Help with Append Query"
- Previous message: Shawn: "system resources exceeded error"
- In reply to: Chris2: "Re: Inline CrossTab Query"
- Next in thread: Chris2: "Re: Inline CrossTab Query"
- Reply: Chris2: "Re: Inline CrossTab Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
