Hello!
I've redesigned a db for my work. The purpose of the db to assign and keep track of assigned "batch" numbers. The current db stores all the info in one table:
batchnum, username, managername, dept, employeeid, location, typeofbatch
It's a wiley beast with over 200,000 records.
I've normalised the data into 7 tables: Batch, Type, Employee, Manager, Dept, Location, AssignedBatch. The tables and relationships appear to be working correctly based on the preliminary testing I've done. I could really use some guidance in the direction of creating a form that assigns a unique batchnumber. The assignedbatch table contains 3 foreign keys: batchnum, typenum, employeenum.
I've tried to work with the form wizard, but gave up and started building in design mode and have had better progress. There are 2 tricky parts to designing this form:
1)The user must be able to type in one of two employee number and the screen will populate with that users information. (That information is stored in 4 seperate tables). Currently, I have employee information being populated into textboxes via DLookup using criteria in an unbound userid combobox. It works ok, but I imagine there's a better way.
2)There are batch number rules related to the Type selected. The first letter or number of the 3 digit batch number has a meaning. For example, let's say the type is Accounting, then the batch number should begin with an 'A'. There are about 15 type rules like this.
I'm trying to figure out how to do this second part, but I'm not sure what's the best way to go. I know that I need an outer join query to find unassigned batch numbers. About those pesky type rules, should I use a case statement to filter the unassigned batch numbers that the outer join query returns?
I have to have a snappy looking form when I'm done. I'm not afraid of coding, so don't hold back any great ideas!
Feel free to ask questions and I appreciate your comments very much.
Sincerely,
vbJupiter
I've redesigned a db for my work. The purpose of the db to assign and keep track of assigned "batch" numbers. The current db stores all the info in one table:
batchnum, username, managername, dept, employeeid, location, typeofbatch
It's a wiley beast with over 200,000 records.
I've normalised the data into 7 tables: Batch, Type, Employee, Manager, Dept, Location, AssignedBatch. The tables and relationships appear to be working correctly based on the preliminary testing I've done. I could really use some guidance in the direction of creating a form that assigns a unique batchnumber. The assignedbatch table contains 3 foreign keys: batchnum, typenum, employeenum.
I've tried to work with the form wizard, but gave up and started building in design mode and have had better progress. There are 2 tricky parts to designing this form:
1)The user must be able to type in one of two employee number and the screen will populate with that users information. (That information is stored in 4 seperate tables). Currently, I have employee information being populated into textboxes via DLookup using criteria in an unbound userid combobox. It works ok, but I imagine there's a better way.
2)There are batch number rules related to the Type selected. The first letter or number of the 3 digit batch number has a meaning. For example, let's say the type is Accounting, then the batch number should begin with an 'A'. There are about 15 type rules like this.
I'm trying to figure out how to do this second part, but I'm not sure what's the best way to go. I know that I need an outer join query to find unassigned batch numbers. About those pesky type rules, should I use a case statement to filter the unassigned batch numbers that the outer join query returns?
I have to have a snappy looking form when I'm done. I'm not afraid of coding, so don't hold back any great ideas!
Feel free to ask questions and I appreciate your comments very much.
Sincerely,
vbJupiter
