Problem with Embeded Queries

[John Ortt]

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

[Wayne Morgan]

Re: Problem with Embeded Queries

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" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in message
news:42e8e276_1 @glkas0286.gree nlnk.net...[color=blue]
> 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.....
>
>[/color]

[John Ortt]

Re: Problem with Embeded Queries

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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:WDeGe.2070 $oY.2069@newssv r25.news.prodig y.net...[color=blue]
> 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[/color]
"where"[color=blue]
> part of the function, but it will accept And/Or statements to chain[/color]
together[color=blue]
> 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" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in message
> news:42e8e276_1 @glkas0286.gree nlnk.net...[color=green]
> > 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][/color][/color]
WHERE[color=blue][color=green]
> > ([qryFlexi2].[DateID] <= [qryFlexi].[DateID]) and ([qryFlexi2].[DateID][/color]
>=[color=green]
> > [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.....
> >
> >[/color]
>
>[/color]

[John Ortt]

Re: Problem with Embeded Queries

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" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in message
news:42e9edf8$1 _1@glkas0286.gr eenlnk.net...[color=blue]
> 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[/color]
basic,[color=blue]
> 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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in[/color]
message[color=blue]
> news:WDeGe.2070 $oY.2069@newssv r25.news.prodig y.net...[color=green]
> > 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[/color]
> "where"[color=green]
> > part of the function, but it will accept And/Or statements to chain[/color]
> together[color=green]
> > 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" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in[/color][/color]
message[color=blue][color=green]
> > news:42e8e276_1 @glkas0286.gree nlnk.net...[color=darkred]
> > > Hi there,
> > >
> > > I am trying to do a two level embeded query but it is causing Dr[/color][/color][/color]
Watson[color=blue][color=green][color=darkred]
> > > errors.
> > >
> > > The line which is causing the problem is:
> > >
> > > CFCDeci: (Select sum (FlexChangeDeci ) FROM [qryFlexi] as [qryFlexi2][/color][/color]
> WHERE[color=green][color=darkred]
> > > ([qryFlexi2].[DateID] <= [qryFlexi].[DateID]) and[/color][/color][/color]
([qryFlexi2].[DateID][color=blue][color=green]
> >=[color=darkred]
> > > [qryFlexi].[PrevFlex]);)
> > >
> > > specifically ([qryFlexi2].[DateID] >= [qryFlexi].[PrevFlex]) as it[/color][/color][/color]
works[color=blue][color=green][color=darkred]
> > > 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.....
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Wayne Morgan]

Re: Problem with Embeded Queries

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" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in message
news:42e9fd77$1 _1@glkas0286.gr eenlnk.net...[color=blue]
>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?[/color]

[John Ortt]

Re: Problem with Embeded Queries

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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:afBGe.2570 $aT1.1583@newss vr19.news.prodi gy.com...[color=blue]
> 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[/color]
to[color=blue]
> specify the table as well as the field in the Where clause ("[DateID]<=#"[/color]
&[color=blue]
> [qryTimes].[DateID] & "#"). Is DateID a date or number?
>
> --
> Wayne Morgan
> MS Access MVP
>
>
> "John Ortt" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in message
> news:42e9fd77$1 _1@glkas0286.gr eenlnk.net...[color=green]
> >I have now got it to return values, but not the correct ones. It seems[/color][/color]
to[color=blue][color=green]
> > 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[/color][/color]
PrevFlex[color=blue][color=green]
> > FROM qryTimes;
> >
> > Can anyone see any further errors please?[/color]
>
>[/color]

[Wayne Morgan]

Re: Problem with Embeded Queries

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" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in message
news:42ede6d6$1 _1@glkas0286.gr eenlnk.net...[color=blue]
> 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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in
> message
> news:afBGe.2570 $aT1.1583@newss vr19.news.prodi gy.com...[color=green]
>> 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[/color]
> to[color=green]
>> specify the table as well as the field in the Where clause ("[DateID]<=#"[/color]
> &[color=green]
>> [qryTimes].[DateID] & "#"). Is DateID a date or number?
>>
>> --
>> Wayne Morgan
>> MS Access MVP
>>
>>
>> "John Ortt" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in
>> message
>> news:42e9fd77$1 _1@glkas0286.gr eenlnk.net...[color=darkred]
>> >I have now got it to return values, but not the correct ones. It seems[/color][/color]
> to[color=green][color=darkred]
>> > 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[/color][/color]
> PrevFlex[color=green][color=darkred]
>> > FROM qryTimes;
>> >
>> > Can anyone see any further errors please?[/color]
>>
>>[/color]
>
>[/color]

[John Ortt]

Re: Problem with Embeded Queries

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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:FpmHe.3005 $1g5.2133@newss vr33.news.prodi gy.com...[color=blue]
> 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[/color]
in[color=blue]
> 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" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in message
> news:42ede6d6$1 _1@glkas0286.gr eenlnk.net...[color=green]
> > Thanks for replying Wayne.
> >
> > You are correct that I have more than one field with the DateID name.[/color][/color]
One[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > left hand-dataset....
> > These are not the only errors either, but I can't see any pattern and as[/color][/color]
a[color=blue][color=green]
> > 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[/color][/color]
2.40[color=blue][color=green]
> > 06/01/2004 31/05/2004 -0.02 06/01/2004[/color][/color]
05/01/2004 -0.02[color=blue][color=green]
> > 07/01/2004 28/06/2004 2.40 07/01/2004 05/01/2004[/color][/color]
2.40[color=blue][color=green]
> > 08/01/2004 26/07/2004 1.85 08/01/2004 05/01/2004[/color][/color]
1.85[color=blue][color=green]
> > 09/01/2004 30/08/2004 0.40 09/01/2004 05/01/2004[/color][/color]
0.40[color=blue][color=green]
> > 19/01/2004 19/01/2004 0.27 19/01/2004 19/01/2004[/color][/color]
0.27[color=blue][color=green]
> > 20/01/2004 19/01/2004 0.67 20/01/2004 19/01/2004[/color][/color]
0.67[color=blue][color=green]
> > 21/01/2004 19/01/2004 0.33 21/01/2004 19/01/2004[/color][/color]
0.33[color=blue][color=green]
> > 22/01/2004 19/01/2004 0.33 22/01/2004 19/01/2004[/color][/color]
0.33[color=blue][color=green]
> > 23/01/2004 19/01/2004 -2.53 23/01/2004[/color][/color]
19/01/2004 -2.53[color=blue][color=green]
> > 26/01/2004 26/01/2004 0.22 26/01/2004 26/01/2004[/color][/color]
0.22[color=blue][color=green]
> > 27/01/2004 26/01/2004 2.12 27/01/2004 26/01/2004[/color][/color]
2.12[color=blue][color=green]
> >
> > Any pointers would be massively appreciated.
> >
> > Thanks,
> >
> > John
> >
> >
> > "Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in
> > message
> > news:afBGe.2570 $aT1.1583@newss vr19.news.prodi gy.com...[color=darkred]
> >> 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[/color][/color][/color]
or[color=blue][color=green][color=darkred]
> >> equal to the current value of the DateID field in the query. Do you[/color][/color][/color]
have[color=blue][color=green][color=darkred]
> >> more than one table in the query with a field DateID? If so, you may[/color][/color][/color]
need[color=blue][color=green]
> > to[color=darkred]
> >> specify the table as well as the field in the Where clause[/color][/color][/color]
("[DateID]<=#"[color=blue][color=green]
> > &[color=darkred]
> >> [qryTimes].[DateID] & "#"). Is DateID a date or number?
> >>
> >> --
> >> Wayne Morgan
> >> MS Access MVP
> >>
> >>
> >> "John Ortt" <JohnOrtt@Idont wantspamsonoret urnaddress.com> wrote in
> >> message
> >> news:42e9fd77$1 _1@glkas0286.gr eenlnk.net...
> >> >I have now got it to return values, but not the correct ones. It[/color][/color][/color]
seems[color=blue][color=green]
> > to[color=darkred]
> >> > 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[/color]
> > PrevFlex[color=darkred]
> >> > FROM qryTimes;
> >> >
> >> > Can anyone see any further errors please?
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]