For example: I have a work order log .mdb which has the w.o.log table and form. I have duplicated the same table but renamed it according to what month we are on next.. ex: w.o. log january 2002, etc, etc, ... Nothing in the tables are different from one another except the work order # which is set up to reflect a certain automatic format according to month Feb, 2002 would be 02-02-001,002,003 and so on and so forth... Now I want to make a query which will pull for ex: field 1 and field 4 and field 5 etc. etc. From each of these tables. according to the date I input,, like Between [Beginning Date] and [Ending Date]... Once I put what date I want info for... I will get data from all of these fields which meet this criteria...
I am trying to gather info on what work orders have been closed within a certain time period... and we could be working on equipment that has a work order created back in January but it was not completed until March... so that's why I need to pull from all available tables...
Hope I am making some sense... anyway please help.. I am frustrated.

You don't need and shouldn't create a table for each month, if you're entering orders by date you can retrieve orders for any period, using the criteria you've already given.

So I can use just one table, but when the next month rolls around how can I change the work order # format to reflect the current month with out causing all of the other data from previous months to not be changed to the current format. I need January's work order numbers to stay the same...

I would not bother with the 'automatic' numbering system. Use Autonumber as the Primary Key for your records and that will keep your records straight and prevent duplicates. As far as the date goes - when you enter an order just type in the date or let Access use the current date. All this goes into one table. If you want to see work order from 3/15/2002 to 3/31/2002 you can use your Between code in a query and you will be good to go.

If you have to have the 02/02/02-001 you can use it, but you really don't need it unless you want to give each work order a number...

You really need to use just one table to hold all the workorders. Do you realize that you'll need to change your queries EVERY month! And what about the recordsources for forms and reports?