Inline CrossTab Query

L

laura

Can anyone tell me how, and maybe give me an example of how to create an
inline SQL query.

The first query basically pulls in the necessary data from 3 tables. The
second query pivots the data for display. I just cannot work out how to put
the two queries together in an inline statement.

Thanks..
Laura TD
 
C

Chris2

laura said:
Can anyone tell me how, and maybe give me an example of how to create an
inline SQL query.

The first query basically pulls in the necessary data from 3 tables. The
second query pivots the data for display. I just cannot work out how to put
the two queries together in an inline statement.

Thanks..
Laura TD
Click to expand...


laura,

May I ask what you mean by, "inline"?


MS Access has QueryDefs that contain complete SQL statements.

MS Access also has Modules that are used to contain VBA programming
code that can manipulate strings to assemble complete SQL statements,
which can then be passed to the JET database engine for execution.

That's the majority of it, right there.


Sincerely,

Chris O.
 
L

laura

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. 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
 
L

laura

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
 
C

Chris2

See Below:

laura said:
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.
Click to expand...

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.
 
L

laura

Chris, If it helps, I will do each query separately as they appear in the
SQL Design window:- They work fine when I run the 3rd query, which runs the
2nd query which in turn runs the 1st query, if that makes sense. Naturally I
have to eliminate the query names and the main reason for doing it this way,
and maybe you have a better solution, is that I need to allow my users to
choose a month and year for the report.. in other words, where it says
WHERE (((Month([day]))=Month(Date())) AND ((Year([day]))=Year(Date())))
the Month(date()) and Year(date()) will be substituted to variables.. the
month and year of their choice which will come from a drop down box on my
ASP page - I just cannot figure out a way to pass those 'parameters' to the
query - it would be simple otherwise as I could just run the queries without
having to produce the SQL myself. I realise this is rather complicated... so
any help would be appreciated.


Query 3
SELECT qryEmplEvent_Crosstab.*
FROM qryEmplEvent_Crosstab
WHERE (((qryEmplEvent_Crosstab.fullname)>""));

Query 2
TRANSFORM First(qryEmplEvent.Event) AS FirstOfEvent
SELECT qryEmplEvent.fullname
FROM qryEmplEvent
GROUP BY qryEmplEvent.fullname
ORDER BY Format([day],"dd") & " " & Format([day],"ddd")
PIVOT Format([day],"dd") & " " & Format([day],"ddd");


Query 1
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;


Laura TD
 
C

Chris2

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
Click to expand...

Laura,

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 Query stripped of string assembly operators.

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 only troube is that you didn't post the *exact* SQL you were
working with. You said you replaced the query names with the actual
SQL. This creates a situation where I'm trying to figure out what's
going wrong for you, and I'm not looking at exactly what you are
using. You'll see what I mean in a second here.


Ok, first off, I'm pretty sure you can't do a TRANSFORM/PIVOT directly
on the FROM clause.

Here is a similar example (from my own list of test tables and
queries):

SELECT *
FROM (TRANSFORM IIF(MIN(T1.SelectionValue) < 0, "Y", "N")
SELECT T1.TransactionID
FROM FormsSelections AS T1
WHERE T1.SelectionValue = Yes
GROUP BY T1.TransactionID
PIVOT T1.SelectionType);

The CrossTab works ok by itself, but produces "Syntax error in FROM
clause" when I execute the above.

However, when I do (FindYesNo_FormsSelections is the name of the
Crosstab QueryDef above):

SELECT *
FROM FindYesNo_FormsSelections;

It works normally.

For you, try:

MyNewQueryDefName:
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')


SELECT *
FROM MyNewQueryDefName
WHERE (((fullname)>''))

I'm hoping that will work for you. (There, that is the trouble I
mentioned a little further back. If I read what you did correctly
from the beginning of your post, you already are looking at just this.
However, it works for me, but isn't working for you.)

I run Win2k SP-4, Access 2000 SP-3, JET SP-8.



However, now I'm curious. You've mentioned a "blank line". What do
mean by that?


Sincerely,

Chris O.
 
L

laura

Chris,

I knew this was not going to be easy to explain, and I'm sorry to put you to
so much time and trouble. It is much appreciated.

I am using Windows XP, Access 2K SP-3 and basically I am trying to do
something, for the first time, on an ASP page so that I can display the
results on a web page - eventually it will be for an intranet so that all
employees can see when people are away for any given month and year. The
only way I could figure out how to make the days of any given month as
column headings was to create a table with one field and 365 records - days
of the year (well, it will contain two years or more eventually so they can
forward plan).

The first query picks up at least one instance of every day in any month,
then events, i.e., various trips made by various employees, and of course
the employee table to pick up their names.

The second query pivots the table so that the days of the week at the top -
I need one instance of each day of the week, even though there may be
several 'events' for each day (i.e., different employees on different trips
on the same day). This works fine - but when I pivot the table, I get one
blank record at the top of the grid. I cannot filter out that blank record
in the crosstab query because then, if there are no trips for any given day,
then that day will not show up as a column heading. I need every day to show
up, even if there are no trips.

Therefore, the final query says, give me everything in the pivot table,
except the blank record.

I realise this might not make sense without actually seeing the tables etc.,
and more importantly, there is probably an easier
way to produce this grid?! I am a relative beginner and have not used
QueryDefs at all and even though I can probably do it your way in Access...
I am not sure how I would do it with VBScript on my ASP page.

The thing is, it all works in Access if I call the final query, which calls
the second query, which calls the first query and it will even work if I
call the final query from my ASP Page.. which is great... except that I need
to be able to pass parameters of the month and year that is chosen, even
though it will default to the current month of the current year to begin
with.

If it would help for me to let you see the tables and queries.. let me know
if I can send them to you. Again, my apologies for confusions and thanks for
taking the time.

Laura TD




Chris2 said:
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
Click to expand...

Laura,

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 Query stripped of string assembly operators.

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 only troube is that you didn't post the *exact* SQL you were
working with. You said you replaced the query names with the actual
SQL. This creates a situation where I'm trying to figure out what's
going wrong for you, and I'm not looking at exactly what you are
using. You'll see what I mean in a second here.


Ok, first off, I'm pretty sure you can't do a TRANSFORM/PIVOT directly
on the FROM clause.

Here is a similar example (from my own list of test tables and
queries):

SELECT *
FROM (TRANSFORM IIF(MIN(T1.SelectionValue) < 0, "Y", "N")
SELECT T1.TransactionID
FROM FormsSelections AS T1
WHERE T1.SelectionValue = Yes
GROUP BY T1.TransactionID
PIVOT T1.SelectionType);

The CrossTab works ok by itself, but produces "Syntax error in FROM
clause" when I execute the above.

However, when I do (FindYesNo_FormsSelections is the name of the
Crosstab QueryDef above):

SELECT *
FROM FindYesNo_FormsSelections;

It works normally.

For you, try:

MyNewQueryDefName:
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')


SELECT *
FROM MyNewQueryDefName
WHERE (((fullname)>''))

I'm hoping that will work for you. (There, that is the trouble I
mentioned a little further back. If I read what you did correctly
from the beginning of your post, you already are looking at just this.
However, it works for me, but isn't working for you.)

I run Win2k SP-4, Access 2000 SP-3, JET SP-8.



However, now I'm curious. You've mentioned a "blank line". What do
mean by that?


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

laura said:
Chris,

I knew this was not going to be easy to explain, and I'm sorry to put you to
so much time and trouble. It is much appreciated.

I am using Windows XP, Access 2K SP-3 and basically I am trying to do
something, for the first time, on an ASP page so that I can display the
results on a web page - eventually it will be for an intranet so that all
employees can see when people are away for any given month and year. The
only way I could figure out how to make the days of any given month as
column headings was to create a table with one field and 365 records - days
of the year (well, it will contain two years or more eventually so they can
forward plan).

The first query picks up at least one instance of every day in any month,
then events, i.e., various trips made by various employees, and of course
the employee table to pick up their names.

The second query pivots the table so that the days of the week at the top -
I need one instance of each day of the week, even though there may be
several 'events' for each day (i.e., different employees on different trips
on the same day). This works fine - but when I pivot the table, I get one
blank record at the top of the grid. I cannot filter out that blank record
in the crosstab query because then, if there are no trips for any given day,
then that day will not show up as a column heading. I need every day to show
up, even if there are no trips.

Therefore, the final query says, give me everything in the pivot table,
except the blank record.

I realise this might not make sense without actually seeing the tables etc.,
and more importantly, there is probably an easier
way to produce this grid?! I am a relative beginner and have not used
QueryDefs at all and even though I can probably do it your way in Access...
I am not sure how I would do it with VBScript on my ASP page.
Click to expand...

One thing at a time. Before we worry about VBScript and ASP page
processing, let's get the SQL working.
The thing is, it all works in Access if I call the final query, which calls
the second query, which calls the first query and it will even work if I
call the final query from my ASP Page.. which is great... except that I need
to be able to pass parameters of the month and year that is chosen, even
though it will default to the current month of the current year to begin
with.

If it would help for me to let you see the tables and queries.. let me know
if I can send them to you. Again, my apologies for confusions and thanks for
taking the time.
Click to expand...

I'm sorry, but I don't accept attachments.

I've got a good look at your basic table structures, and you've
posted your SQL.

Time to put a cap on the triple crown of diagnotic needs, and add
some sample data, and I'll be able to cobble up some sample tables to
hold them and run your SQL queries on.

Oh, can you tell me what values are for the Subform's Link Master
Fields and Link Child Fields values are? They can be located by
opening the Form in Design view. Clicking on the edge of the Subform,
and bringing up the properties page. Make sure the title-bar of the
properties page reads: Subform/Subreport: <subform name>. If it says
Form, instead, you haven't selected the Subform Control, you have
selected the Form inside the Subform Control. When you click on the
Data Tab, there should only be 5 properties.

SourceObject = Subform the Subform Control loads.
Link Child Fields
Link Master Fields
Enabled
Locked.

Oh, hmm, if we're doing this for an ASP page, why are we futzing
around with the Form/Subform problem?


Note: My house is getting remodeled, so I may have less time per
night this week to respond than I normally do.


Sincerely,

Chris O.
 
C

Chris2

laura said:
Dear Chris,

I have not used a form at all - just created the 3 queries and run them from
the Query Design Window - or on my test website - I just created a table and
populated it with the results of the triple query. Sorry I haven't much more
to go on.
Click to expand...

Hmm, I must have confused this with another problem. My apologies.

I used to program in dBase and Clipper and I think that if I was doing this
routine in those languages, to resolve my present problem, I probably would
have created a temporary table and populated it each time with each month's
worth of data and used that to fill my grid... emptying it and re-filling it
each time a new 'query' was run, but that does not seem too
Click to expand...
efficient.

As I mentioned, I don't have a lot of time right now. :(


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I add an inline comment to an SQL query? 4
Query is too complex 5
crosstab query? 3
Make-table query with around 2 ml records taking too long 0
Query a crosstab query 2
Parameter Value in Crosstab Query 4
Crosstab Query using 2 date ranges 4
Crosstab query question 2

Top