Run a query based to another query

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hi!
I wrote the code MediaCambi to run a query based to other
two queries named Count_Changes and Count_Days. It works
fine.
Now i want to delete the queries Count_Changes and
Count_Days and write code for have the same queries with
code and after to base the function MediaCambi to that
code
Somebody can help me...
Thanks in advance

Function MediaCambi()
Dim dbs As Database
Dim SQL As String
Dim qdf As QueryDef
Set dbs = CurrentDb

dbs.QueryDefs.Delete "rnsTemp"
SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Giorni] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)

Set rstClients = dbs.QueryDefs
("rnsTemp").OpenRecordset
DoCmd.OpenQuery "rnsTemp"

End Function
 
Paul said:
Hi!
I wrote the code MediaCambi to run a query based to other
two queries named Count_Changes and Count_Days. It works
fine.
Now i want to delete the queries Count_Changes and
Count_Days and write code for have the same queries with
code and after to base the function MediaCambi to that
code
Somebody can help me...
Thanks in advance

Function MediaCambi()
Dim dbs As Database
Dim SQL As String
Dim qdf As QueryDef
Set dbs = CurrentDb

dbs.QueryDefs.Delete "rnsTemp"
SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Giorni] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)

Set rstClients = dbs.QueryDefs
("rnsTemp").OpenRecordset
DoCmd.OpenQuery "rnsTemp"

End Function
Click to expand...

First, why are you opening a recordset (rstClients) on the query, and
then opening the query as a datasheet? Since you exit the function
immediately afterward, I suspect that you should simply delete the line
Set rstClients = dbs.QueryDefs("rnsTemp").OpenRecordset
Click to expand...

Unless the function contains other code you deleted before posting it,
the statement serves no purpose.

Now, as to the question of how to rewrite your code so as to eliminate
the queries Count_Changes and Count_Days. You'll have to post the SQL
of those queries before anyone could tell you how to do that.
 
Hi!
Yes you are right.
I deleted the line
Set rstClients = dbs.QueryDefs("rnsTemp").OpenRecordset
Here is the SQL of the queries
1)Count_Changes:
SELECT Count([ID]) AS [N° Changes]
FROM Changes;
2)Count_Days
SELECT Count([Data]) AS [N° Days]
FROM [Count of Changes for Days];

Thanks for helping me
-----Original Message-----
Paul said:
Hi!
I wrote the code MediaCambi to run a query based to other
two queries named Count_Changes and Count_Days. It works
fine.
Now i want to delete the queries Count_Changes and
Count_Days and write code for have the same queries with
code and after to base the function MediaCambi to that
code
Somebody can help me...
Thanks in advance

Function MediaCambi()
Dim dbs As Database
Dim SQL As String
Dim qdf As QueryDef
Set dbs = CurrentDb

dbs.QueryDefs.Delete "rnsTemp"
SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Giorni] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)

Set rstClients = dbs.QueryDefs
("rnsTemp").OpenRecordset
DoCmd.OpenQuery "rnsTemp"

End Function
Click to expand...

First, why are you opening a recordset (rstClients) on the query, and
then opening the query as a datasheet? Since you exit the function
immediately afterward, I suspect that you should simply delete the line
Set rstClients = dbs.QueryDefs
Click to expand...
("rnsTemp").OpenRecordset

Unless the function contains other code you deleted before posting it,
the statement serves no purpose.

Now, as to the question of how to rewrite your code so as to eliminate
the queries Count_Changes and Count_Days. You'll have to post the SQL
of those queries before anyone could tell you how to do that.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Paul said:
Hi!
Yes you are right.
I deleted the line
Set rstClients = dbs.QueryDefs("rnsTemp").OpenRecordset
Here is the SQL of the queries
1)Count_Changes:
SELECT Count([ID]) AS [N° Changes]
FROM Changes;
2)Count_Days
SELECT Count([Data]) AS [N° Days]
FROM [Count of Changes for Days];

Thanks for helping me
Click to expand...

In the query you want to modify,
SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Giorni] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)
Click to expand...
Click to expand...

is [N° Giorni] just an untranslated form of [N° Days] ? Otherwise, I
can't see where it comes from. Is [Count of Changes for Days] a table,
or another query? If it's a query, what is its SQL?
 
-----Original Message-----
Paul said:
Hi!
Yes you are right.
I deleted the line
Set rstClients = dbs.QueryDefs("rnsTemp").OpenRecordset
Here is the SQL of the queries
1)Count_Changes:
SELECT Count([ID]) AS [N° Changes]
FROM Changes;
2)Count_Days
SELECT Count([Data]) AS [N° Days]
FROM [Count of Changes for Days];

Thanks for helping me
Click to expand...

In the query you want to modify,
SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Giorni] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)
Click to expand...
Click to expand...

is [N° Giorni] just an untranslated form of [N° Days] ? Otherwise, I
can't see where it comes from. Is [Count of Changes for Days] a table,
or another query? If it's a query, what is its SQL?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



.
Click to expand...
Hi!
Yes i've done a mistake, the right SQL is

SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Days] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)

1)Count_Changes is a query based on a table
Here the SQL
SELECT Count([ID]) AS [N° Changes]
FROM Changes;
2)Count_Days is a query based on some queries

2a)here is the SQL of the first query named Count_Changes
Sub1 based on a table

SELECT DISTINCTROW Cambi.Data, Cambi.Macchina, Cambi.
[Articolo tolto], Cambi.[Taglia tolto], Cambi.[Articolo
avviato], Cambi.Taglia
FROM Cambi
GROUP BY Cambi.Data, Cambi.Macchina, Cambi.[Articolo
tolto], Cambi.[Taglia tolto], Cambi.[Articolo avviato],
Cambi.Taglia, Cambi.[Data ripresa]
HAVING (((Cambi.[Data ripresa]) Is Null));

2b)here is the SQL of the second query named Count_Changes
Sub2 based on a table

SELECT DISTINCTROW Cambi.[Data ripresa], Cambi.Macchina,
Cambi.[Articolo tolto], Cambi.[Taglia tolto], Cambi.
[Articolo avviato], Cambi.Taglia
FROM Cambi
GROUP BY Cambi.[Data ripresa], Cambi.Macchina, Cambi.
[Articolo tolto], Cambi.[Taglia tolto], Cambi.[Articolo
avviato], Cambi.Taglia
HAVING (((Cambi.[Data ripresa]) Is Not Null));

2c)Here is the SQL of a Union Query based on the 2 queries
Count_Changes Sub1 and Count_Changes Sub2

SELECT [Data],[Macchina],[Articolo tolto],[Taglia tolto],
[Articolo avviato],[Taglia]
FROM [Conteggio di cambi Sub1]
UNION SELECT ALL [Data ripresa],[Macchina],[Articolo
tolto],[Taglia tolto],[Articolo avviato],[Taglia]
FROM [Conteggio di cambi Sub2];

2d)Here is the SQL of the fourth query named Count of
Changes for Days

SELECT DISTINCTROW [Conteggio Cambi Unione].Data, Count(*)
AS [Conteggio Di Cambi]
FROM [Conteggio Cambi Unione]
GROUP BY [Conteggio Cambi Unione].Data
ORDER BY Count(*) DESC;

I hope isn't too much what im asking you
Thanks in advance
 
Paul said:
-----Original Message-----
Paul said:
Hi!
Yes you are right.
I deleted the line
Set rstClients = dbs.QueryDefs("rnsTemp").OpenRecordset
Here is the SQL of the queries
1)Count_Changes:
SELECT Count([ID]) AS [N° Changes]
FROM Changes;
2)Count_Days
SELECT Count([Data]) AS [N° Days]
FROM [Count of Changes for Days];

Thanks for helping me
Click to expand...

In the query you want to modify,
SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Giorni] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)
Click to expand...

is [N° Giorni] just an untranslated form of [N° Days] ? Otherwise, I
can't see where it comes from. Is [Count of Changes for Days] a
table, or another query? If it's a query, what is its SQL?
Click to expand...
Hi!
Yes i've done a mistake, the right SQL is

SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Days] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)

1)Count_Changes is a query based on a table
Here the SQL
SELECT Count([ID]) AS [N° Changes]
FROM Changes;
2)Count_Days is a query based on some queries

2a)here is the SQL of the first query named Count_Changes
Sub1 based on a table

SELECT DISTINCTROW Cambi.Data, Cambi.Macchina, Cambi.
[Articolo tolto], Cambi.[Taglia tolto], Cambi.[Articolo
avviato], Cambi.Taglia
FROM Cambi
GROUP BY Cambi.Data, Cambi.Macchina, Cambi.[Articolo
tolto], Cambi.[Taglia tolto], Cambi.[Articolo avviato],
Cambi.Taglia, Cambi.[Data ripresa]
HAVING (((Cambi.[Data ripresa]) Is Null));

2b)here is the SQL of the second query named Count_Changes
Sub2 based on a table

SELECT DISTINCTROW Cambi.[Data ripresa], Cambi.Macchina,
Cambi.[Articolo tolto], Cambi.[Taglia tolto], Cambi.
[Articolo avviato], Cambi.Taglia
FROM Cambi
GROUP BY Cambi.[Data ripresa], Cambi.Macchina, Cambi.
[Articolo tolto], Cambi.[Taglia tolto], Cambi.[Articolo
avviato], Cambi.Taglia
HAVING (((Cambi.[Data ripresa]) Is Not Null));

2c)Here is the SQL of a Union Query based on the 2 queries
Count_Changes Sub1 and Count_Changes Sub2

SELECT [Data],[Macchina],[Articolo tolto],[Taglia tolto],
[Articolo avviato],[Taglia]
FROM [Conteggio di cambi Sub1]
UNION SELECT ALL [Data ripresa],[Macchina],[Articolo
tolto],[Taglia tolto],[Articolo avviato],[Taglia]
FROM [Conteggio di cambi Sub2];

2d)Here is the SQL of the fourth query named Count of
Changes for Days

SELECT DISTINCTROW [Conteggio Cambi Unione].Data, Count(*)
AS [Conteggio Di Cambi]
FROM [Conteggio Cambi Unione]
GROUP BY [Conteggio Cambi Unione].Data
ORDER BY Count(*) DESC;

I hope isn't too much what im asking you
Thanks in advance
Click to expand...

Hmm. And you want to eliminate all those queries and use a single SQL
statement to do the job? I suppose it is possible that you could do it
using nested subqueries, but I can't afford the time at the moment to
undertake such a complicated process. Why do you want to get rid of the
stored queries? If you don't want users to see them, you can always
hide them from view, either by marking them as hidden via the Access
user interface, or by prefixing the query names with "USys".

If you leave the query [Count of Changes for Days] and its subqueries
alone, then you could still eliminate the need for the queries
[Count_Changes] and [Count_Days] by setting your SQL statement like
this:

SQL = _
"SELECT " & _
"(SELECT Count([ID]) FROM Changes) AS [N° Changes], " & _
"[N° Days], " & _
"[N° Changes]/[N° Days] AS Total " & _
"FROM " & _
"(SELECT Count([Data]) AS [N° Days] " & _
"FROM [Count of Changes for Days]) " & _
"AS Count_Days;"

At least, I believe that will work. Something very like it certainly
will.
 
-----Original Message-----
Paul said:
-----Original Message-----
Hi!
Yes you are right.
I deleted the line
Set rstClients = dbs.QueryDefs ("rnsTemp").OpenRecordset
Here is the SQL of the queries
1)Count_Changes:
SELECT Count([ID]) AS [N° Changes]
FROM Changes;
2)Count_Days
SELECT Count([Data]) AS [N° Days]
FROM [Count of Changes for Days];

Thanks for helping me

In the query you want to modify,

SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Giorni] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)

is [N° Giorni] just an untranslated form of [N° Days] ? Otherwise, I
can't see where it comes from. Is [Count of Changes for Days] a
table, or another query? If it's a query, what is its SQL?
Click to expand...
Hi!
Yes i've done a mistake, the right SQL is

SQL = "SELECT [Count_Changes].[N° Changes],
[Count_Days].[N° Days], [N° Changes]/[N° Days] AS
[Total] FROM [Count_Changes], [Count_Days];"
Set qdf = dbs.CreateQueryDef("rnsTemp", SQL)

1)Count_Changes is a query based on a table
Here the SQL
SELECT Count([ID]) AS [N° Changes]
FROM Changes;
2)Count_Days is a query based on some queries

2a)here is the SQL of the first query named Count_Changes
Sub1 based on a table

SELECT DISTINCTROW Cambi.Data, Cambi.Macchina, Cambi.
[Articolo tolto], Cambi.[Taglia tolto], Cambi.[Articolo
avviato], Cambi.Taglia
FROM Cambi
GROUP BY Cambi.Data, Cambi.Macchina, Cambi.[Articolo
tolto], Cambi.[Taglia tolto], Cambi.[Articolo avviato],
Cambi.Taglia, Cambi.[Data ripresa]
HAVING (((Cambi.[Data ripresa]) Is Null));

2b)here is the SQL of the second query named Count_Changes
Sub2 based on a table

SELECT DISTINCTROW Cambi.[Data ripresa], Cambi.Macchina,
Cambi.[Articolo tolto], Cambi.[Taglia tolto], Cambi.
[Articolo avviato], Cambi.Taglia
FROM Cambi
GROUP BY Cambi.[Data ripresa], Cambi.Macchina, Cambi.
[Articolo tolto], Cambi.[Taglia tolto], Cambi.[Articolo
avviato], Cambi.Taglia
HAVING (((Cambi.[Data ripresa]) Is Not Null));

2c)Here is the SQL of a Union Query based on the 2 queries
Count_Changes Sub1 and Count_Changes Sub2

SELECT [Data],[Macchina],[Articolo tolto],[Taglia tolto],
[Articolo avviato],[Taglia]
FROM [Conteggio di cambi Sub1]
UNION SELECT ALL [Data ripresa],[Macchina],[Articolo
tolto],[Taglia tolto],[Articolo avviato],[Taglia]
FROM [Conteggio di cambi Sub2];

2d)Here is the SQL of the fourth query named Count of
Changes for Days

SELECT DISTINCTROW [Conteggio Cambi Unione].Data, Count (*)
AS [Conteggio Di Cambi]
FROM [Conteggio Cambi Unione]
GROUP BY [Conteggio Cambi Unione].Data
ORDER BY Count(*) DESC;

I hope isn't too much what im asking you
Thanks in advance
Click to expand...

Hmm. And you want to eliminate all those queries and use a single SQL
statement to do the job? I suppose it is possible that you could do it
using nested subqueries, but I can't afford the time at the moment to
undertake such a complicated process. Why do you want to get rid of the
stored queries? If you don't want users to see them, you can always
hide them from view, either by marking them as hidden via the Access
user interface, or by prefixing the query names with "USys".

If you leave the query [Count of Changes for Days] and its subqueries
alone, then you could still eliminate the need for the queries
[Count_Changes] and [Count_Days] by setting your SQL statement like
this:

SQL = _
"SELECT " & _
"(SELECT Count([ID]) FROM Changes) AS [N° Changes], " & _
"[N° Days], " & _
"[N° Changes]/[N° Days] AS Total " & _
"FROM " & _
"(SELECT Count([Data]) AS [N° Days] " & _
"FROM [Count of Changes for Days]) " & _
"AS Count_Days;"

At least, I believe that will work. Something very like it certainly
will.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
Hi!
Yes i thought i asked you something too complicate.
What i wanted to know it's how to create a query in Vba
based on a table and after to create another query always
in Vba based on the first query.
I make an example a little less complicate of the other.
Maybe you can answer me

Here is the SQL of the first query based on a table

SELECT W_Changes.ID, W_Changes.Mc, W_Changes.Date,
W_Changes.[Time stop], W_Changes.[Time restart], W_Changes.
[Day restart], Weekday([Date]) AS [ID Day stop]
FROM W_Changes
ORDER BY W_Changes.ID;

Here is the SQL of the second query based on the first
query named W_Change.
W_Weekdays is a table

SELECT w_Change.ID, w_Change.Mc, w_Change.Date, W_Weekdays.
[Day name], w_Change.[ID Day stop], w_Change.[Day
restart], w_Change.[Time stop], w_Change.[Time restart]
FROM W_Weekdays INNER JOIN w_Change ON W_Weekdays.[ID Day]
= w_Change.[ID Day stop]
ORDER BY w_Change.ID;

Thanks in advance
 
Paul said:
What i wanted to know it's how to create a query in Vba
based on a table and after to create another query always
in Vba based on the first query.
I make an example a little less complicate of the other.
Maybe you can answer me

Here is the SQL of the first query based on a table

SELECT W_Changes.ID, W_Changes.Mc, W_Changes.Date,
W_Changes.[Time stop], W_Changes.[Time restart], W_Changes.
[Day restart], Weekday([Date]) AS [ID Day stop]
FROM W_Changes
ORDER BY W_Changes.ID;

Here is the SQL of the second query based on the first
query named W_Change.
W_Weekdays is a table

SELECT w_Change.ID, w_Change.Mc, w_Change.Date, W_Weekdays.
[Day name], w_Change.[ID Day stop], w_Change.[Day
restart], w_Change.[Time stop], w_Change.[Time restart]
FROM W_Weekdays INNER JOIN w_Change ON W_Weekdays.[ID Day]
= w_Change.[ID Day stop]
ORDER BY w_Change.ID;

Thanks in advance
Click to expand...

I'm not sure whether you're asking how to combine these two SQL
statements into one, or whether you're asking how to create stored
queries to represent them. I'm guessing that you want to combine the
two SQL statements, but please tell me if I have guessed wrong.

There is a general solution and a specific solution. The general
solution is to embed the first SQL statement in the second as a
subquery. Doing that alone gives us this:

SELECT
w_Change.ID,
w_Change.Mc,
w_Change.Date,
W_Weekdays.[Day name],
w_Change.[ID Day stop],
w_Change.[Day restart],
w_Change.[Time stop],
w_Change.[Time restart]
FROM
W_Weekdays
INNER JOIN
(
SELECT
W_Changes.ID,
W_Changes.Mc,
W_Changes.Date,
W_Changes.[Time stop],
W_Changes.[Time restart],
W_Changes.[Day restart],
Weekday(W_Changes.Date) AS [ID Day stop]
FROM
W_Changes
ORDER BY
W_Changes.ID
) As w_Change
ON W_Weekdays.[ID Day] = w_Change.[ID Day stop]
ORDER BY
w_Change.ID;

Note, though, that in this instance the ORDER BY clause in the subquery
is completely irrelevant and should be removed, giving this:

SELECT
w_Change.ID,
w_Change.Mc,
w_Change.Date,
W_Weekdays.[Day name],
w_Change.[ID Day stop],
w_Change.[Day restart],
w_Change.[Time stop],
w_Change.[Time restart]
FROM
W_Weekdays
INNER JOIN
(
SELECT
W_Changes.ID,
W_Changes.Mc,
W_Changes.Date,
W_Changes.[Time stop],
W_Changes.[Time restart],
W_Changes.[Day restart],
Weekday(W_Changes.Date) AS [ID Day stop]
FROM
W_Changes
) As w_Change
ON W_Weekdays.[ID Day] = w_Change.[ID Day stop]
ORDER BY
w_Change.ID;

However, this particular case lends itself to an even simpler, specific
solution that doesn't require a subquery:

SELECT
W_Changes.ID,
W_Changes.Mc,
W_Changes.Date,

W_Weekdays.[Day name],
W_Changes.[Time stop],
W_Changes.[Time restart],
W_Changes.[Day restart],
Weekday(W_Changes.Date) AS [ID Day stop]
FROM
W_Changes
INNER JOIN
W_Weekdays
ON W_Weekdays.[ID Day] = Weekday(W_Changes.Date)
ORDER BY
W_Changes.ID;

I haven't tested these out, but as far as I can see this specific
solution should give the same results as the more general one, and would
likely be more efficient.

Does that answer your question, or did I guess wrong about what you
wanted to know?
 
-----Original Message-----
Paul said:
What i wanted to know it's how to create a query in Vba
based on a table and after to create another query always
in Vba based on the first query.
I make an example a little less complicate of the other.
Maybe you can answer me

Here is the SQL of the first query based on a table

SELECT W_Changes.ID, W_Changes.Mc, W_Changes.Date,
W_Changes.[Time stop], W_Changes.[Time restart], W_Changes.
[Day restart], Weekday([Date]) AS [ID Day stop]
FROM W_Changes
ORDER BY W_Changes.ID;

Here is the SQL of the second query based on the first
query named W_Change.
W_Weekdays is a table

SELECT w_Change.ID, w_Change.Mc, w_Change.Date, W_Weekdays.
[Day name], w_Change.[ID Day stop], w_Change.[Day
restart], w_Change.[Time stop], w_Change.[Time restart]
FROM W_Weekdays INNER JOIN w_Change ON W_Weekdays.[ID Day]
= w_Change.[ID Day stop]
ORDER BY w_Change.ID;

Thanks in advance
Click to expand...

I'm not sure whether you're asking how to combine these two SQL
statements into one, or whether you're asking how to create stored
queries to represent them. I'm guessing that you want to combine the
two SQL statements, but please tell me if I have guessed wrong.

There is a general solution and a specific solution. The general
solution is to embed the first SQL statement in the second as a
subquery. Doing that alone gives us this:

SELECT
w_Change.ID,
w_Change.Mc,
w_Change.Date,
W_Weekdays.[Day name],
w_Change.[ID Day stop],
w_Change.[Day restart],
w_Change.[Time stop],
w_Change.[Time restart]
FROM
W_Weekdays
INNER JOIN
(
SELECT
W_Changes.ID,
W_Changes.Mc,
W_Changes.Date,
W_Changes.[Time stop],
W_Changes.[Time restart],
W_Changes.[Day restart],
Weekday(W_Changes.Date) AS [ID Day stop]
FROM
W_Changes
ORDER BY
W_Changes.ID
) As w_Change
ON W_Weekdays.[ID Day] = w_Change.[ID Day stop]
ORDER BY
w_Change.ID;

Note, though, that in this instance the ORDER BY clause in the subquery
is completely irrelevant and should be removed, giving this:

SELECT
w_Change.ID,
w_Change.Mc,
w_Change.Date,
W_Weekdays.[Day name],
w_Change.[ID Day stop],
w_Change.[Day restart],
w_Change.[Time stop],
w_Change.[Time restart]
FROM
W_Weekdays
INNER JOIN
(
SELECT
W_Changes.ID,
W_Changes.Mc,
W_Changes.Date,
W_Changes.[Time stop],
W_Changes.[Time restart],
W_Changes.[Day restart],
Weekday(W_Changes.Date) AS [ID Day stop]
FROM
W_Changes
) As w_Change
ON W_Weekdays.[ID Day] = w_Change.[ID Day stop]
ORDER BY
w_Change.ID;

However, this particular case lends itself to an even simpler, specific
solution that doesn't require a subquery:

SELECT
W_Changes.ID,
W_Changes.Mc,
W_Changes.Date,

W_Weekdays.[Day name],
W_Changes.[Time stop],
W_Changes.[Time restart],
W_Changes.[Day restart],
Weekday(W_Changes.Date) AS [ID Day stop]
FROM
W_Changes
INNER JOIN
W_Weekdays
ON W_Weekdays.[ID Day] = Weekday (W_Changes.Date)
ORDER BY
W_Changes.ID;

I haven't tested these out, but as far as I can see this specific
solution should give the same results as the more general one, and would
likely be more efficient.

Does that answer your question, or did I guess wrong about what you
wanted to know?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
Hi!
I was asking how to create stored queries
If you can answer me i'll be glad
 
Hi!
I was asking how to create stored queries
If you can answer me i'll be glad
Click to expand...

Oh. Then I've been going far afield from what you really wanted. Sorry
about that.

Here's an example of creating a stored query that uses that last SQL
statement I worked out:

'---- start of example ----
Dim strSQL As String

strSQL = _
"SELECT " & _
"W_Changes.ID, " & _
"W_Changes.Mc, " & _
"W_Changes.Date, " & _
"W_Weekdays.[Day name], " & _
"W_Changes.[Time stop], " & _
"W_Changes.[Time restart], " & _
"W_Changes.[Day restart], " & _
"Weekday(W_Changes.Date) As [ID Day stop] " & _
"FROM " & _
"W_Changes " & _
"INNER JOIN " & _
"W_Weekdays " & _
"ON W_Weekdays.[ID Day] = Weekday(W_Changes.Date) " & _
"ORDER BY " & _
"W_Changes.ID;"

CurrentDb.CreateQueryDef "MyStoredQuery", strSQL

'---- end of example ----

Does *that* answer your question?
 
Back
Top