Re: Creating a query from multiple identical tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,


At first, I thought you have something like one table per year (just for
illustration). It would be easier to merge these tables into just one, where
a field, [year], would be added (again, just for illustration):


SELECT 1999 As [year], f1, f2, f3, f4 FROM year1999table
UNION ALL
SELECT 2000, f1, f2, f3, f4 FROM year2000table
UNION ALL
....
SELECT 2006, f1, f2, f3, f4 FROM year2006table


is a query that would bring the data together, but without any index, that
would be slow. That is why making it a table would be better.


Hoping it may help,
Vanderghast, Access MVP



"bluemoir" <bluemoir@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8BB98E02-04CC-4884-B171-8E8FEBDCF244@xxxxxxxxxxxxxxxx
>I am trying to create a query from multiple tables with the same number of
> columns and same headings to pull all records from each table with a
> matching
> value in one of the fields. This database holds all the information for
> each
> job we have in progress with each table representing one of our customers.
> I'd like to know how I can create a query to pull form each customers
> table
> all jobs with the status of "Dispatched". I was also wondering since I
> plan
> to recreate the database in a while if there is a better way to organise
> the
> records in order to make something like this easier to do. Is this a poor
> database design? Please let me know in your post if you need more
> information
> to help me I'll post it right away.


.

Relevant Pages

  • RE: Store Added Value List Items
    ... You could even union that query with the actual list from the form ... Each user in this database needs to have access to all the same customers. ... is praticle to create a seperate one field table to store values for a lookup ...
    (microsoft.public.access.formscoding)
  • Re: Query help needed
    ... I have my own created database from Excel spreadsheets. ... You could create another column in your query. ... Let's say the field to search is called Comments, the table Customers. ... This will prompt you to enter a search string value. ...
    (comp.databases.ms-access)
  • Re: Vulnerability in Microsoft Jet Database Engine Could Allow Rem
    ... Are the joined tables in your query remote tables ... on a test machine you might try compacting the database after ... customers who stopped working were running Windows XP Pro. ... after the service packs were applied, either Windows XP Pro/Home Service Pack ...
    (microsoft.public.access.security)
  • Re: How to convert records in expression fields in Forms to a tabl
    ... When I double click the Query, the Access gave an error: ... engine can't find input table or query "Customers". ... opportunity to review the database again and again to understand more ... open it in design view to ...
    (microsoft.public.access.forms)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)