Union and variable

[Guest]

Hello,

I have a series of queries which look similar to:

SELECT tblCandidates.Booked, Count(tblCandidates.Booked) AS NumberBooked,
Weekday([Booked]) AS WedOnly
FROM tblCandidates
GROUP BY tblCandidates.Booked, Weekday([Booked])
HAVING (((tblCandidates.Booked)>=Date()-7) AND ((Weekday([Booked]))=4));

and

SELECT [qGoals Tracking - All Weekly CO Books].ThisWeek, [qGoals Tracking -
All Weekly CO Books].NumberBooked
FROM [qGoals Tracking - All Weekly CO Books]
WHERE ((([qGoals Tracking - All Weekly CO Books].ThisWeek)=[qGoals Tracking
- All Weekly CO Books]![BegOfWeek]));

I have a union query which looks like this:

Select NumberBooked, Booked From [qGoals Tracking - 1Sun CO Books] Union
Select NumberBooked, Booked From [qGoals Tracking - 2Mon CO Books] Union
Select NumberBooked, Booked From [qGoals Tracking - 3Tue CO Books] Union
Select NumberBooked, Booked From [qGoals Tracking - 4Wed CO Books] Union
Select NumberBooked, Booked From [qGoals Tracking - 5Thu CO Books] Union
Select NumberBooked, Booked From [qGoals Tracking - 6Fri CO Books] Union
Select NumberBooked, Booked From [qGoals Tracking - 7Sat CO Books] Union
Select NumberBooked, ThisWeek From [qGoals Tracking - This Week's CO Books]
UNION
Select NumberBooked, ThisWeek From [qGoals Tracking - 1 Week Ago CO Books];

When I run this last query I get a dialog box asking for a Parameter Value
for "ThisWeek". My goal is to pulls all of this information into a single
query so I can use the query for a report. I'm stuck as to how to get over
this hurdle.

Thanks for your help.

 

[Michel Walsh]

Hi,


Either ThisWeek is not part of [qGoals Tracking - This Week's CO Books] or
of [qGoals Tracking - 1 Week Ago CO Books]; either ThisWeek is a parameter
inside one of the otehr queries. Check typo, specifically. In other words,
try:


Select NumberBooked, ThisWeek From [qGoals Tracking - This Week's CO Books]



all alone. Same with



Select NumberBooked, ThisWeek From [qGoals Tracking - 1 Week Ago CO Books]



Hoping it may help,
Vanderghast, Access MVP

 

[George Nicholson]

Are you sure that both [qGoals Tracking - This Week's CO Books] and [qGoals
Tracking - 1 Week Ago CO Books] return a field called ThisWeek?

(Are you *really, really* sure "q...1 Week Ago..." doesn't return a field
called LastWeek rather than one called ThisWeek?)

In any case, somewhere along the way, your Union query can't find a field
called ThisWeek where you have told it to expect one. You need to find out
why. Since you only reference ThisWeek in those 2 parts of your Union, the
possiblities are, I hope, fairly limited.

However, there is also a chance that the prompt is not being directly driven
by the queries in your union, but by one of their source queries (i.e.,
somewhere upstream in the query chain). If necessary, run each one
independently to make sure they run "clean" by themselves. Once you know
each query runs by itself without a prompt, you know the problem is that the
field names they are returning don't correspond to the field names in your
Union.


HTH,