Sub-tables? Access 97

Hi.
I have a database used for reporting company purchases. Each purchase type has a 6 digit code. I have been asked to add an extra level of detail to the database to improve the accuracy of identifying the purchases.
There are about 60 purchase types, and I've been asked to add unique 'sub-types' to a handful of those types. In other words I'll have:

Type 1
Type 2
___sub-type 1
___sub-type 2
___sub-type 3
Type 3
Type 4
Type 5
Type 6
___sub type 4
___sub-type 5

...and so on...

Is there a simple way to add this 'next level' to the main table? The outer 'type' field in the main table uses a lookup table (which appears as a drop down menu / combo box on the various forms). Would it be done with some kind of sub-table, or with a group of lookups from the Types table for each Type that has a sub-type?
Am I making any sense??!!

After I get passed that, I'll also have to include the sub-type code field in a number of forms (which already contain subforms).
Ideally, the sub-types that would appear in the drop-down menu would be dependent on whichever type had just been entered - in other words, using the exxample above, say type 6 was selected, I'd like the only available options in the sub-type code drop-down list to be sub-type 4 or sub-type 5.

Then come the queries and advanced reports.... I just need to get this started, so any ideas are hugely appreciated.

Gram

 

Adding it to the same table would probably a mistake and a nightmare to implement. You should just make a new table with the sub types and have the main types PK be the FK in this table. YOu could also create another field that would denote if it is a sub-type 1, 2, or 3.

 

Okay - what are PK and FK? Oh, Primary Key and....

A bit more info: The outer types are Departments, the sub-types are Activities. Some Departments have no Activities, some do.
I figured the easy way to do this is just to create a new table containing all of the Activities, and then adding a combo box to each data entry form, so the whole list of possible Activities are available.
However, I don't want people to be able to select an Activity that is outside of it's Department. So, if someone selects the Department "Imports", I don't want them to be able to enter (or even see, if possible) the Activity "Truck Storage" - that Activity should only be available if the Department entered was "Storage & Handling".
This limitations doesn't have to be enforced in the table (although I guess it would be good practice), just in the form. I try to use "Limit to list" drop-downs, because the data must be standardised, and if you just let them users type an entry into a text box, you come out with all sorts of typos and things get missed off reports.
And this is the same thing - I want to force them to choose only one of a small group of specific options (for Activity), dependent on the Department.

Oh, and just to make it all the more awkward, I don't know VB.

The alternative, which I think you were getting at, would be to adjust the Department drop-down menu, so that records would contain both the Department and the Activity, so I might have:

Department - Activity - Department Code - Activity Code
Storage & Handling - Truck Storage - 123456 - 5555
Storage & Handling - Prep Handling - 123456 - 5556

I'd prefer not to combine the codes - 1234565555 - as I will need to create reports taht look for specific departments etc later.

Don't know if this clarified or confused...
Gram

 

PK = Primary Key, FK = Foreign Key from other table. You have the right idea on the new tables. You should make 2 new tables if you are going to do it right i guess. One table would have the activity codes with a primary key field, one field for activity description and one for a code that you assign. If you are looking for an activity type of thing you will have to create a table that is updated when a user finalizez inputting their data and takes the Dept Code, and Activity Code and any other pertinent info you think should be there. You are going to have to do a lot of coding unfortunately if you want some kind of restriction like you were stating before. You would need some login authentication set up in some kind of module and depending upon how complex your app is it would have to be coded into every form. I am assuming this is an Access application on your network.

 

Sorry Rockn, but I beg to differ.

Here's the gig: you're definitely looking at two tables. One has department, one has activity. The PK of lktblDept is DeptID, the 6-dig code. The PK of lktblActivity is ActivityID, looks like it's a 4-dig code. Each PK has a corresponding name field, no biggie. The FK of lktblActivity is, of course, DeptID, you have cascading referential integrity set up.

The key (ouch. no pun intended, I swear) is this: each department has a default activity type of zero. In other words, there's a third field in lktblActivity, something like DeptActNum. Who cares what you call it. The point is, when you first build lktblActivity, you do it with an append query, creating one record for each dept, with a DeptActNum of 0. Step by step you do this:

1. You already have lktblDept, whatever you're calling it. Nothing to do here.
2. Build an lktblActivity, whatever you're calling it. Must have the FK of DeptID, an ActivityID for easy storage, and a (optional: you do not have to have this, but it sure makes it easier to explain) DeptActNum field. Plus an ActivityName field, of course. Make ActivityID an AutoNumber field. (You'll change it later.)
3. Set up your relationship between the tables.
4. Create an append query, whose source table is lktblDept. Append to lktblActivity. DeptID appends to DeptID, 0 appends to DeptActNum field, "<none>" appends to ActivityName. Set no criteria; you will thus append all records.
5. Remove all your relationships between lktblDept and whatever tables it's saved in.
6. Change the ActivityID from AutoNumber to whatever data type you're going to use. (I'd suggest Number > Long Integer, but it's your call, keeping in mind that any other type will be harder to update. It should in any case be the same data type as DeptID.) You must do this before you build the new relationships, or Access won't let you. (And rightly so.)
7. Change the name of DeptID in each other table to ActivityID (you may have to also change some forms and reports to match). No need to change your ActivityID values yet, though.
8. Create a query which links up each DeptID in lktblDept to each ActivityID in lktblActivity, obviously with a join on the DeptID field. Use this query as a base to build other queries to update your various instances of former-DeptID now-ActivityID in your other tables.
9. Build relationships between lktblActivity and these tables, the same relationships you used to have to lktblDept.
10. Once referential integrity is established on every single table using ActivityID, go back and change your ActivityID values in lktblActivity to whatever you want. (The cascades should do the rest.)

Once you've completed these steps, you have added a new level to your db: rather than keying all your tables to the Dept tbl, you're keying them to the Activity tbl, on a "default" record of "no activity". It's not hard; the whole process should take about an hour or two, depending on how many forms you have to revise. If it seems hard, it just means I'm going too fast, so ask questions.

Then all you have to do is add in activities for the dept(s) that have them. Each one will get a unique ActivityID, and if you're using the DeptActNum field, you should number them sequentially, e.g. ActivityID 5555 is DeptActNum 1, ActivityID 5556 is DeptActNum 2, etc. And as for DeptActNum for DeptID 123456 where there is no activity? Doesn't matter. Just so long as it's not a number you're going to use as an ActivityID later (like 5555 or 5556). And you can always go back and change them in one shot, because of the relationships.

Then (on downwitch time you're now clocking about 40 minutes) you build one lookup query, which you will use for all your combos and lists. It should contain both lktblDept and lktblActivity. Its first column should be ActivityID. Its second column will be one of the following:
If you've used DeptActNum:

Code:

ActNmDisplay:  [lktblDeptID].[DeptNm]
& iif([lktblActivity].[DeptActNum]=0,""," >> " 
& [lktblActivity].[ActivityNm])

If you haven't (in which case the "<none>" of the ActivityNm becomes necessary):

Code:

ActNmDisplay:  [lktblDeptID].[DeptNm] 
& iif([lktblActivity].[ActivityNm]="<none>",""," >> "
& [lktblActivity].[ActivityNm])

Make sure to remove the line breaks; I just used them to reduce screen width. As you may guess from reading this code, and as you will see when you build this query if not, you get one list; for every "default" department name, that is, where DeptActNum is 0 or ActivityNm is "<none>", you just see the DeptNm. And for each "sub" record of Dept, you see the DeptNm and then >> and then the ActivityNm. And all your records are still stored in one table, with one key, which is easily updatable. And you can still regroup your results by Dept if you want to, or just pull out the "default" Activity listings.

Let us know if this makes sense...

 

You are more eloquent than I am DWYBS, but that was basically what I meant....LOL
There is still the coding he will have to do to limit the dropdown lists to the department that is accessing them but setting up the tables correctly is more important at the moment. I am no expert by any means so fire away....

 

Thanks guys, I'll give it a go tomorrow morning.
I'll post back when I get stuck!

I have quite a lot to do in very limited time, but hopefully this will get me started.

Ta.
Gram

 

It turns out that this was just the very tip of the iceberg, the project required a massive amount of additional work, including setting up a network for this db to be used in three or four locations around the country.
As such, I've stepped back for someone more technically minded to take over.

Thanks anyway guys.
Gram

 

WARNING!! If you are scaling the project over such a wide area Access is going to choke. You need to look at a SQL Server or Oracle solution instead.