By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,507 Members | 1,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,507 IT Pros & Developers. It's quick & easy.

Problem with Embeded Queries

P: n/a
Hi there,

I am trying to do a two level embeded query but it is causing Dr Watson
errors.

The line which is causing the problem is:

CFCDeci: (Select sum (FlexChangeDeci) FROM [qryFlexi] as [qryFlexi2] WHERE
([qryFlexi2].[DateID] <= [qryFlexi].[DateID]) and ([qryFlexi2].[DateID] >=
[qryFlexi].[PrevFlex]);)

specifically ([qryFlexi2].[DateID] >= [qryFlexi].[PrevFlex]) as it works
when I remove this.

Prev Flexi was defined in an earlier query with another embeded query, could
this be what is causing the problem?
The code for PrevFlex is below:

PrevFlex: (Select max (dateID) FROM [tblflexi] as Temp WHERE [temp].[dateid]
<= [qrytimes].[dateid])

Any help would be gratefully appreciated.

Thanks,

John.

P.S. The rest of the SQL can be posted if it is of help.....
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Try using the Domain Aggregate functions instead of the embedded query.

DSum()
DMax()

I believe the examples in the help file only show one limiter in the "where"
part of the function, but it will accept And/Or statements to chain together
more than one limiter. Also, the field name in the first part can be an
equation but it still has to be enclosed in quotes. The Domain Aggregate
function will run the equation.

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42********@glkas0286.greenlnk.net...
Hi there,

I am trying to do a two level embeded query but it is causing Dr Watson
errors.

The line which is causing the problem is:

CFCDeci: (Select sum (FlexChangeDeci) FROM [qryFlexi] as [qryFlexi2] WHERE
([qryFlexi2].[DateID] <= [qryFlexi].[DateID]) and ([qryFlexi2].[DateID] >=
[qryFlexi].[PrevFlex]);)

specifically ([qryFlexi2].[DateID] >= [qryFlexi].[PrevFlex]) as it works
when I remove this.

Prev Flexi was defined in an earlier query with another embeded query,
could
this be what is causing the problem?
The code for PrevFlex is below:

PrevFlex: (Select max (dateID) FROM [tblflexi] as Temp WHERE
[temp].[dateid]
<= [qrytimes].[dateid])

Any help would be gratefully appreciated.

Thanks,

John.

P.S. The rest of the SQL can be posted if it is of help.....

Nov 13 '05 #2

P: n/a
Thanks for the tip Wayne,

One thing I am unsure about is whether I can use the DSum and DMax in a
query as with the embeded query or whether I have to do it in Visual basic,
as the examples all seem to be in VB?

I tried the following DMax but with no luck...

SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
DMax("[DateID]","tblFlexi","[DateID] <= " & [DateID]) AS PrevFlex
FROM qryTimes;

Thanks in advance,

John
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:WD****************@newssvr25.news.prodigy.net ...
Try using the Domain Aggregate functions instead of the embedded query.

DSum()
DMax()

I believe the examples in the help file only show one limiter in the "where" part of the function, but it will accept And/Or statements to chain together more than one limiter. Also, the field name in the first part can be an
equation but it still has to be enclosed in quotes. The Domain Aggregate
function will run the equation.

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42********@glkas0286.greenlnk.net...
Hi there,

I am trying to do a two level embeded query but it is causing Dr Watson
errors.

The line which is causing the problem is:

CFCDeci: (Select sum (FlexChangeDeci) FROM [qryFlexi] as [qryFlexi2] WHERE ([qryFlexi2].[DateID] <= [qryFlexi].[DateID]) and ([qryFlexi2].[DateID]

=
[qryFlexi].[PrevFlex]);)

specifically ([qryFlexi2].[DateID] >= [qryFlexi].[PrevFlex]) as it works
when I remove this.

Prev Flexi was defined in an earlier query with another embeded query,
could
this be what is causing the problem?
The code for PrevFlex is below:

PrevFlex: (Select max (dateID) FROM [tblflexi] as Temp WHERE
[temp].[dateid]
<= [qrytimes].[dateid])

Any help would be gratefully appreciated.

Thanks,

John.

P.S. The rest of the SQL can be posted if it is of help.....


Nov 13 '05 #3

P: n/a
I have now got it to return values, but not the correct ones. It seems to
be returning random entries.
The only change I have made is to add the # symbols as shown below:

SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
DMax("[DateID]","[tblFlexi]","[DateID] <=#" & [DateID] & "#") AS PrevFlex
FROM qryTimes;

Can anyone see any further errors please?

Thanks,

John
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42**********@glkas0286.greenlnk.net...
Thanks for the tip Wayne,

One thing I am unsure about is whether I can use the DSum and DMax in a
query as with the embeded query or whether I have to do it in Visual basic, as the examples all seem to be in VB?

I tried the following DMax but with no luck...

SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
DMax("[DateID]","tblFlexi","[DateID] <= " & [DateID]) AS PrevFlex
FROM qryTimes;

Thanks in advance,

John
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:WD****************@newssvr25.news.prodigy.net ...
Try using the Domain Aggregate functions instead of the embedded query.

DSum()
DMax()

I believe the examples in the help file only show one limiter in the

"where"
part of the function, but it will accept And/Or statements to chain

together
more than one limiter. Also, the field name in the first part can be an
equation but it still has to be enclosed in quotes. The Domain Aggregate
function will run the equation.

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42********@glkas0286.greenlnk.net...
Hi there,

I am trying to do a two level embeded query but it is causing Dr Watson errors.

The line which is causing the problem is:

CFCDeci: (Select sum (FlexChangeDeci) FROM [qryFlexi] as [qryFlexi2] WHERE ([qryFlexi2].[DateID] <= [qryFlexi].[DateID]) and ([qryFlexi2].[DateID]=
[qryFlexi].[PrevFlex]);)

specifically ([qryFlexi2].[DateID] >= [qryFlexi].[PrevFlex]) as it

works when I remove this.

Prev Flexi was defined in an earlier query with another embeded query,
could
this be what is causing the problem?
The code for PrevFlex is below:

PrevFlex: (Select max (dateID) FROM [tblflexi] as Temp WHERE
[temp].[dateid]
<= [qrytimes].[dateid])

Any help would be gratefully appreciated.

Thanks,

John.

P.S. The rest of the SQL can be posted if it is of help.....



Nov 13 '05 #4

P: n/a
Are you asking for what you are wanting? You have asked DMax to return the
largest value in the field DateID where the field DateID is less than or
equal to the current value of the DateID field in the query. Do you have
more than one table in the query with a field DateID? If so, you may need to
specify the table as well as the field in the Where clause ("[DateID]<=#" &
[qryTimes].[DateID] & "#"). Is DateID a date or number?

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42**********@glkas0286.greenlnk.net...
I have now got it to return values, but not the correct ones. It seems to
be returning random entries.
The only change I have made is to add the # symbols as shown below:

SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
DMax("[DateID]","[tblFlexi]","[DateID] <=#" & [DateID] & "#") AS PrevFlex
FROM qryTimes;

Can anyone see any further errors please?

Nov 13 '05 #5

P: n/a
Thanks for replying Wayne.

You are correct that I have more than one field with the DateID name. One
in the tblTimes and one in the tblFlexi.

Essentially I would like the query to work as follows but it doesn't.

SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
DMax("[DateID]","[tblFlexi]","[tblFlexi].[DateID] <=#" & [qryTimes].[DateID]
& "#") AS PrevFlex
FROM qryTimes;

It gives me results, but not what I was expecting.
The data below shows the results of the DMax query on the left and the
results of the embedded query on the right.
You can see that for the first week the PrevFlex date goes haywire in the
left hand-dataset....
These are not the only errors either, but I can't see any pattern and as a
result I can't figure out what's happening.

DateID PrevFlex FlexChangeDeci DateID PrevFlex
FlexChangeDeci
05/01/2004 26/04/2004 2.40 05/01/2004 05/01/2004 2.40
06/01/2004 31/05/2004 -0.02 06/01/2004 05/01/2004 -0.02
07/01/2004 28/06/2004 2.40 07/01/2004 05/01/2004 2.40
08/01/2004 26/07/2004 1.85 08/01/2004 05/01/2004 1.85
09/01/2004 30/08/2004 0.40 09/01/2004 05/01/2004 0.40
19/01/2004 19/01/2004 0.27 19/01/2004 19/01/2004 0.27
20/01/2004 19/01/2004 0.67 20/01/2004 19/01/2004 0.67
21/01/2004 19/01/2004 0.33 21/01/2004 19/01/2004 0.33
22/01/2004 19/01/2004 0.33 22/01/2004 19/01/2004 0.33
23/01/2004 19/01/2004 -2.53 23/01/2004 19/01/2004 -2.53
26/01/2004 26/01/2004 0.22 26/01/2004 26/01/2004 0.22
27/01/2004 26/01/2004 2.12 27/01/2004 26/01/2004 2.12

Any pointers would be massively appreciated.

Thanks,

John
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:af*****************@newssvr19.news.prodigy.co m...
Are you asking for what you are wanting? You have asked DMax to return the
largest value in the field DateID where the field DateID is less than or
equal to the current value of the DateID field in the query. Do you have
more than one table in the query with a field DateID? If so, you may need to specify the table as well as the field in the Where clause ("[DateID]<=#" & [qryTimes].[DateID] & "#"). Is DateID a date or number?

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42**********@glkas0286.greenlnk.net...
I have now got it to return values, but not the correct ones. It seems to be returning random entries.
The only change I have made is to add the # symbols as shown below:

SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
DMax("[DateID]","[tblFlexi]","[DateID] <=#" & [DateID] & "#") AS PrevFlex FROM qryTimes;

Can anyone see any further errors please?


Nov 13 '05 #6

P: n/a
What data type is DateID defined as in the table? Is it a Date/Time data
type? What format is your short date? Is it US format (mm/dd/yyyy) or
something else? If something else, you may need to format it to US format in
the statement to get it to work properly.

DMax("[DateID]","[tblFlexi]","[tblFlexi].[DateID] <=#" &
Format([qryTimes].[DateID], "mm/dd/yyyy") & "#")

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42**********@glkas0286.greenlnk.net...
Thanks for replying Wayne.

You are correct that I have more than one field with the DateID name. One
in the tblTimes and one in the tblFlexi.

Essentially I would like the query to work as follows but it doesn't.

SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
DMax("[DateID]","[tblFlexi]","[tblFlexi].[DateID] <=#" &
[qryTimes].[DateID]
& "#") AS PrevFlex
FROM qryTimes;

It gives me results, but not what I was expecting.
The data below shows the results of the DMax query on the left and the
results of the embedded query on the right.
You can see that for the first week the PrevFlex date goes haywire in the
left hand-dataset....
These are not the only errors either, but I can't see any pattern and as a
result I can't figure out what's happening.

DateID PrevFlex FlexChangeDeci DateID PrevFlex
FlexChangeDeci
05/01/2004 26/04/2004 2.40 05/01/2004 05/01/2004 2.40
06/01/2004 31/05/2004 -0.02 06/01/2004 05/01/2004 -0.02
07/01/2004 28/06/2004 2.40 07/01/2004 05/01/2004 2.40
08/01/2004 26/07/2004 1.85 08/01/2004 05/01/2004 1.85
09/01/2004 30/08/2004 0.40 09/01/2004 05/01/2004 0.40
19/01/2004 19/01/2004 0.27 19/01/2004 19/01/2004 0.27
20/01/2004 19/01/2004 0.67 20/01/2004 19/01/2004 0.67
21/01/2004 19/01/2004 0.33 21/01/2004 19/01/2004 0.33
22/01/2004 19/01/2004 0.33 22/01/2004 19/01/2004 0.33
23/01/2004 19/01/2004 -2.53 23/01/2004 19/01/2004 -2.53
26/01/2004 26/01/2004 0.22 26/01/2004 26/01/2004 0.22
27/01/2004 26/01/2004 2.12 27/01/2004 26/01/2004 2.12

Any pointers would be massively appreciated.

Thanks,

John
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:af*****************@newssvr19.news.prodigy.co m...
Are you asking for what you are wanting? You have asked DMax to return
the
largest value in the field DateID where the field DateID is less than or
equal to the current value of the DateID field in the query. Do you have
more than one table in the query with a field DateID? If so, you may need

to
specify the table as well as the field in the Where clause ("[DateID]<=#"

&
[qryTimes].[DateID] & "#"). Is DateID a date or number?

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in
message
news:42**********@glkas0286.greenlnk.net...
>I have now got it to return values, but not the correct ones. It seems to > be returning random entries.
> The only change I have made is to add the # symbols as shown below:
>
> SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
> DMax("[DateID]","[tblFlexi]","[DateID] <=#" & [DateID] & "#") AS PrevFlex > FROM qryTimes;
>
> Can anyone see any further errors please?



Nov 13 '05 #7

P: n/a
Well done Wayne, you solved it!

It was the date format (UK). When I put in the code you supplied it worked
a treat.

Thanks again...

John
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Fp*****************@newssvr33.news.prodigy.co m...
What data type is DateID defined as in the table? Is it a Date/Time data
type? What format is your short date? Is it US format (mm/dd/yyyy) or
something else? If something else, you may need to format it to US format in the statement to get it to work properly.

DMax("[DateID]","[tblFlexi]","[tblFlexi].[DateID] <=#" &
Format([qryTimes].[DateID], "mm/dd/yyyy") & "#")

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:42**********@glkas0286.greenlnk.net...
Thanks for replying Wayne.

You are correct that I have more than one field with the DateID name. One in the tblTimes and one in the tblFlexi.

Essentially I would like the query to work as follows but it doesn't.

SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
DMax("[DateID]","[tblFlexi]","[tblFlexi].[DateID] <=#" &
[qryTimes].[DateID]
& "#") AS PrevFlex
FROM qryTimes;

It gives me results, but not what I was expecting.
The data below shows the results of the DMax query on the left and the
results of the embedded query on the right.
You can see that for the first week the PrevFlex date goes haywire in the left hand-dataset....
These are not the only errors either, but I can't see any pattern and as a result I can't figure out what's happening.

DateID PrevFlex FlexChangeDeci DateID PrevFlex
FlexChangeDeci
05/01/2004 26/04/2004 2.40 05/01/2004 05/01/2004 2.40 06/01/2004 31/05/2004 -0.02 06/01/2004 05/01/2004 -0.02 07/01/2004 28/06/2004 2.40 07/01/2004 05/01/2004 2.40 08/01/2004 26/07/2004 1.85 08/01/2004 05/01/2004 1.85 09/01/2004 30/08/2004 0.40 09/01/2004 05/01/2004 0.40 19/01/2004 19/01/2004 0.27 19/01/2004 19/01/2004 0.27 20/01/2004 19/01/2004 0.67 20/01/2004 19/01/2004 0.67 21/01/2004 19/01/2004 0.33 21/01/2004 19/01/2004 0.33 22/01/2004 19/01/2004 0.33 22/01/2004 19/01/2004 0.33 23/01/2004 19/01/2004 -2.53 23/01/2004 19/01/2004 -2.53 26/01/2004 26/01/2004 0.22 26/01/2004 26/01/2004 0.22 27/01/2004 26/01/2004 2.12 27/01/2004 26/01/2004 2.12
Any pointers would be massively appreciated.

Thanks,

John
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:af*****************@newssvr19.news.prodigy.co m...
Are you asking for what you are wanting? You have asked DMax to return
the
largest value in the field DateID where the field DateID is less than or equal to the current value of the DateID field in the query. Do you have more than one table in the query with a field DateID? If so, you may
need to
specify the table as well as the field in the Where clause
("[DateID]<=#" &
[qryTimes].[DateID] & "#"). Is DateID a date or number?

--
Wayne Morgan
MS Access MVP
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in
message
news:42**********@glkas0286.greenlnk.net...
>I have now got it to return values, but not the correct ones. It
seems to
> be returning random entries.
> The only change I have made is to add the # symbols as shown below:
>
> SELECT qryTimes.DateID, qryTimes.FC AS FlexChangeDeci,
> DMax("[DateID]","[tblFlexi]","[DateID] <=#" & [DateID] & "#") AS

PrevFlex
> FROM qryTimes;
>
> Can anyone see any further errors please?



Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.