Re: Table selection in query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Duane,

Thank you for clarifying. Maybe I need to rethink the way that database
will work. I was looking at trying to automate the database by creating a
macro to run that will copy the current table into a new table, clear the
data from the current table, import the data into the current table and then
the query could run against either the new table or the current table.

Thank you,
Aaron

"Duane Hookom" wrote:

> Are you looking for a better method than using the union query?
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "Aaron" <Aaron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:DE8E3C44-A8FA-4B7F-ABFB-3E505608BE87@xxxxxxxxxxxxxxxx
> > Hi Duane,
> >
> > Thank you for your help! The reason for multiple tables with the same
> > structure is that we would like to keep history on the fields. I was
> > thinking that we could run one query against these tables should we
> > require
> > to obtain historical information. Is there a better way of doing this?
> >
> > Thank you,
> > Aaron
> >
> > "Duane Hookom" wrote:
> >
> >> The question is "why do you have multiple tables with the same
> >> structure"?
> >> You can stick them together in a union query like:
> >>
> >> Select "First" as TableName, Field1, Field2, Field3, Field4,...
> >> FROM tblFirst
> >> UNION ALL
> >> Select "Second", Field1, Field2, Field3, Field4,...
> >> FROM tblSecond
> >> UNION ALL
> >> Select "Third", Field1, Field2, Field3, Field4,...
> >> FROM tblThird;
> >>
> >> You can then run a query the sets a criteria by the derived TableName
> >> field.
> >>
> >> --
> >> Duane Hookom
> >> MS Access MVP
> >>
> >>
> >> "Aaron" <Aaron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:89DFD696-ECE8-4ED4-9F2E-DE2F163117CA@xxxxxxxxxxxxxxxx
> >> > Hi,
> >> > Is there a way in Access to be able to prompt for the table name that
> >> > you
> >> > would like to run a query against? What I'm trying to do is to create
> >> > one
> >> > query to access different tables with the same fields but different
> >> > values.
> >> > Can Access accomplish this?
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... which is one of the union-ed queries. ... I also tried a UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Can you transpose the columns and rows in a query result?
    ... Duane Hookom ... >> Assuming you have a primary key field of ID create a union query ... >> FROM tblAddresses ...
    (microsoft.public.access.queries)
  • Re: Query to search multiple identical tables
    ... You seem to think Query and Form have their own set ... So, when you edit ... UNION Queries are NOT updateable since in a union, ... >This, of course, violates the Relational Database Design ...
    (microsoft.public.access.queries)
  • Re: Sum of numbers
    ... allowed to re-design the database but had to work with it, ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ...
    (microsoft.public.access.reports)