select table before query

  • Thread starter Thread starter Jose Lopes
  • Start date Start date
J

Jose Lopes

Basically I have access to a bunch of tables that are created once a day.
What I want is to have a box where I input the name of the desired table and
then be asked for the fields to search as usual. So far I’ve been changing my
origin table by hand its quite a boring task and not free from errors. How
can I do this?

thx in advance.
 
On my website (www.rogersaccesslibrary.com), are several small Access
database which illustrate how to do this in various ways.

CreateQueries2.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368
CreateQueries5.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=365
ChooseReportFields.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=385
ChooseReportFieldsRows.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=384

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Basically I have access to a bunch of tables that are created once a day.
Click to expand...

That's why you're having problems. This is not how Access is designed to work!
Storing data in a tablename is NOT a good idea, and that's what you're doing.

Might it be possible to instead have *one big table* - or a set of related
tables - into which you import records once a day? There would need to be an
additional field or fields indicating the record's data source.

Table too big, you say? Well, it's a lot smaller than storing the same data in
multiple tables, each with its own substantial overhead.
 
Thx for your answer John. I knew someone was going to mention that my DB is
not well designed. But that’s just the way the data is generated. I have a
different table every day. You said I should import the data to a big table…
allow me to give you a quick glance of my tables.

Table 1: [20080901]

Product wharehouse1 wharehouse2
12345678 20 20
01234567 5 10
98765432 3 3

Table 2: [20080902]

Product wharehouse1 wharehouse2
12345678 10 10
01234567 5 5
98765432 1 1

At the moment I need to chose the relevant table to query. What is your
advice?
 
yeah thats just shocking

you should have AT LEAST and i say AT LEAST as this is not the correct
solution but it removes the potentionaly infinate number of tables in
your database which may or may not kill access i dont know then to
query which tabl you would just query the date.

tblproduct

product warehouse1 warehouse2 storagedate
12345678 20 20 20080901
01234567 5 10 20080901
98765432 3 3 20080901
12345678 10 10 20080902
01234567 5 5 20080902
98765432 1 1 20080902

and even better the more correct solution

tblproduct
productid product
1 12345678
2 01234567
3 98765432

tblwarehouse
warehouseid warehousename
1 warehouse1
2 warehouse2

tblstorage
warehouseid productid storagedate quantity
1 1 20080901 20
1 2 20080901 5
1 3 20080901 3
2 1 20080901 20
2 2 20080901 10
2 3 20080901 3
1 1 20080902 10
1 2 20080902 5
1 3 20080902 1
2 1 20080902 10
2 2 20080902 5
2 3 20080902 1

and if you really want to be padantic you make a table for dates as
well to remove all redundancy but this maybe a bit far

hope this information helps

Regards
Kelvan
 
Table 1: [20080901]

Product wharehouse1 wharehouse2
12345678 20 20
01234567 5 10
98765432 3 3

Table 2: [20080902]

Product wharehouse1 wharehouse2
12345678 10 10
01234567 5 5
98765432 1 1

At the moment I need to chose the relevant table to query. What is your
advice?
Click to expand...

Normalize.

Products
ProductID <primary key>
<information about the product as an entity>

Warehouses
WarehouseNo <Number, Primary Key>
WarehouseName
<address, other information about the warehouse itself>

Inventory
InventoryID <autonumber primary key>
InventoryDate <Date/Time, e.g. #9/1/2008#>
WarehouseNo <Number, link to Warehouses>
ProductID <link to Products, e.g. 12345678>
Amount <e.g. 20>

You could then APPEND each day's inventory (or whatever these numbers mean,
you didn't say) into the Inventory table, including the date.
 
Thank you both for your help. Now I have to learn two things: how to create a
query to insert the dates in every row and how to add new records to a single
table. This means I’ll try Kelvan’s first solution. Something tells me I’ll
be back in the forum soon :)
 
Back
Top