Query to check for values in multiple tables

[Guest]

I have a database that tracks store remodels for my company. Each year has
its own separate table and form so that we can have a history of all remodels
for all stores. There is a query for each year (qryRemodelSchedule) that
gathers all stores for which the field RemodelStartDate is not null. All the
information is displayed on a tabbed form frmStoreInformation.

What I want to do now is add a History tab to the form and have the dates of
each remodel automatically fill into text boxes for each store based on the
values in the underlying tables. How do I write the master query and how do
I program it? Is it even possible? Thanks!!

 

[Dave Miller]

Karen,

You need to use a union query like this, name it
"qtotRemodelSchedule":

SELECT * FROM qryRemodelSchedule_2006
UNION SELECT * FROM qryRemodelSchedule_2005
UNION SELECT * FROM qryRemodelSchedule_2004
WHERE RemodelStartDate is not Null

David Miller

 

[Guest]

I think you would be better off having a generic query that either prompts
for a filter (e.g., [Enter a year]) or filters from input boxes on the form.
Your form for the history can remove the filter so you don't need to worry
about a Union query (and having to continually modify it every year -
consider the union query design after 5-6 years).

 

[Guest]

Thanks, David! I tweaked it a little and wound up with this, which works.

SELECT [StoreNumber], [TargetCompletion], [ScopeofWork]
FROM [RemodelInfo]
WHERE [TargetCompletion] is not Null
UNION
SELECT [StoreNumber], [TargetCompletion], [ScopeofWork]
FROM [2006RemodelInfo]
WHERE [TargetCompletion] is not Null
UNION
SELECT [StoreNumber], [TargetCompletion], [ScopeofWork]
FROM [2007RemodelInfo]
WHERE [TargetCompletion] is not Null

Now my question is, how do I get these to automatically fill into the text
boxes that I will set up on the form? (I.e. how do I refer to this as a data
source, since it will have more than one entry for each store?) It's not as
simple as naming the query as the data source for the control, that much I
know. =)

 

[Guest]

The problem is, I'm working with people who are less Access-savvy than me,
who knows just enough to be dangerous. > My boss wants to open a record
for store #54, click the history tab and see the entire history right there
for all years, without having to answer any prompts or anything of that
nature that might conceivably limit the data. MY boss is patient, but as in
a typical workplace, patience is reduced by an order of magnitude for each
step up the food chain you go. =) Yes, the union query would be impossibly
long after 5-6 years, but if I can get the data filling into the form, it
does the job. He doesn't care what's behind the scene, just what's in front
of his eyes so he has all the info he wants at a glance.

I think you would be better off having a generic query that either prompts
for a filter (e.g., [Enter a year]) or filters from input boxes on the form.
Your form for the history can remove the filter so you don't need to worry
about a Union query (and having to continually modify it every year -
consider the union query design after 5-6 years).

I have a database that tracks store remodels for my company. Each year has
its own separate table and form so that we can have a history of all remodels
for all stores. There is a query for each year (qryRemodelSchedule) that
gathers all stores for which the field RemodelStartDate is not null. All the
information is displayed on a tabbed form frmStoreInformation.

What I want to do now is add a History tab to the form and have the dates of
each remodel automatically fill into text boxes for each store based on the
values in the underlying tables. How do I write the master query and how do
I program it? Is it even possible? Thanks!!