I NEED HELP WITH FINDING RECORDS

[HerickP]

Hi, its me again,<br><br>I dunno if this actually can be done, but here is the challenge:<br><br>I have many tables holding info for specific vendors, and on each of these tables, there are fields ( for PO number, Customer Name) and I have a total of 20 tables, each one with specific info for orders for that vendor... what I would like to do is have a find or search feature in a form ( a form with a text box where the user would imput search criteria, for example, a PO number, and a command button labeled &quot;find&quot; that would trigger a search)...so what I would like done is right after the user clicks on &quot;find&quot;, access would search thru the tables ( or table, i can have a find for each vendor) and return a result in another form, with all the fields related to the PO in the table....Hope I could explain, and I would appreciate a lot your help guys!!! Thanks

 

[elizabeth]

Is it necessary to have a separate table for each vendor? Sounds like all orders should be un the same table with a forien key of VendorID. This would change the nature of your problem, make it more commonplace. If you use a comon approach to building your tables, you'll end up hitting fewer &quot;chalenges&quot;.

 

[HerickP]

how to do that?????? I didnt think about that.....Help!!

 

[elizabeth]

1. Create a table: tblVendor: VendorID (Autonumber), Vendor Name, etc.<br>2. Fill in this table so that all 20 vendors have unique IDs.<br>3. Create a new table: tblOrders. OrderID (Autonumber), VendorID (Number, Long Integer), and all the order fields you have in those other 20 tables.<br>4. Append all your data from the 20 tables into the one new table. Several ways to do this. <br>(a) If your Orders do not already have an AutoNumberID, you may be able to copy and paste them (use select all). Or,<br>(b) Create an append query that appends all the order fields from your (1st of 20) table to the new table. In addition to the order fields, create an expression to load the matching VendorID into the vendorID field. Or,<br>(c) Add the VendorID field to each of the 20 tales and fill in the value appropriately, or<br>(d) Key in the VendorIDs in the new table after each append if you don't have many. <br>5. Run the same query against each of the other 19 tables, changing the expression each time if you use that method. <br><br>As you may have noticed, it's a lot easier to do structure right the first time thatn to make changes. You might want to take a look at something on relational design principles (or normalization - see help) before you go any further. Good luck