Need help with a new design.

[Xenos132]

I am trying to automate our work order system and incorporate it into my data base. Just having a bit of trouble with the design.

I have 4 different system for work orders depending on their use, each has a distinct beginning. Construction for example is BCx , Maintence is Mx, service is SJx…

I am thinking of using 4 tables to generate my numbers by using “leter”000 in the format field of the auto number. So table one would generate, “BC”001… table 2 would generate “M”001… and so on.

Now the problem. I want to have one table called work orders. I am thinking of having a combo box where the items listed are the 4 table names where I am generating the numbers from. I am also thinking when I select one of these it will give me the next available number and I will use this as my work order number. I just can’t figure out how to put this together.

Anyone have a suggestion.

Thanks

 

[FancyPrairie]

I'm not sure I would take that approach (but maybe after studying it more, I might). I think I would just have 1 table with one field representing the Work Order Number and another field indicating the system type (Bcx, Mx, Sjx). That way if wanted to do reporting or editing of the data, I wouldn't have to join 4 different tables together. And it would be easier to filter the data and archive it. I wouldn't have to have separate reports and edit screens (rather just change the Where clause). Now, with the way you suggested, you may still not have to have separate reports and edit screens, but I think it will become more complicated with 4 tables.

Just something to think about.

 

[Xenos132]

The idea of the 4 tables are just for generating the numbers, these numbers will be stored in one table called TblWorkOrders.

Right now I have it all in the same table. The problem is the construction manager requires his work orders in sequential order. Right now I use the primary key in my work order table for all the work orders, today if the construction manager asks for a construction WO it will be 1000 then if he asks for one tomorrow it could be 1005. (if some one generated other work orders for different jobs like service). By generating the numbers in separate tables I could issue him BC1000 today then tomorrow BC1001…

The separate tables are not for storing data just generating the next work order number. That’s if it’s possible. I was actually thinking the only data in each table would be the Primary Key (BCx) and a column to store the id number from the work order table that was generated when the new record was made.

 

[Xenos132]

I think I may have found my answer with

DepSeqNum = Nz(DMax("DepSeqNum", "OrderTable", "DepType = '" & Me.DepType & "'"), 0) + 1

Can you lend a hand on how I would use this? Do I use it on the form or table ? If on a form in the control source or do I use it on click? I trying to figure it out on my own but I keep getting an error.

I have tried it in the control source as.

= Nz(DMax("WOSeqNum", " PO_numbers ", "Type = '" & Me. Type & "'"), 0) + 1