Please help with 'guiding' me with this relationship / table structure

[Chaz]

I am struggling to get an understanding of what is best in my specific scenario to tie together certain 'jobs'. Ive tried different queries and keep getting odd issues.

So looking back to basics and thinking that there may be a better way, need your collective advice on which way to go with this.

Simply put - my database has 3 core main tables and many others for lookup etc. I need to find a method by which to link these tables together so that when queried later - I can see all related info together.

What is common with each is something called a TNE Number. The issue with this number however is that it is not unique and can be duplicated.

t_services, t_hardware_orders and t_los are my 3 main tables, each will then have a field that has a TNE number. When this number matches, it refers to the same 'service'.

Is there a method to link these three tables together or should I consider a new table - lets call it t_job_link (Junction Table) and then have:

ID (PK)
Services_TNE_1 (FK)
Services_TNE_2 (FK)
Hardware_TNE_1 (FK)
Hardware_TNE_2(FK)
Hardware_TNE_3 (FK)
Hardware_TNE_4 (FK)
LOS_TNE_1 (FK)

The link is then between the PK of each of the main tables into these FKs. There is however a lot of manual work that will need to be done to manually update these.

The reason for the multiple entries is that a specific job can have 2 sets of services bought and 4 items of hardware purchased.

Thanks in advance.

 

[Scooterbug]

I would go with the Junction Table. You should not be using the TNE number as a primary key since you can have duplicates. The best way is to have each entry have an Autonumber as the Primary key. Your Junction table should contain the following:

tblJunction
JunctionID (Primary Key)
TypeID (Foreign Key: Either Service, Hardware or LOS)
TypeForeignKey (Foreign Key for the above mentioned type)
TNE_NumberID (Foreign Key)

If you dont want to do a Junction Table and the TNE has data specific to itself, you could also have a table for TNE data. Create a TNE number, assign a Primary Key to it. You can also store any data related to each TNE. Then store the TNE Primary key in each record for Service, Hardware or LOS.

 

[Chaz]

I would go with the Junction Table. You should not be using the TNE number as a primary key since you can have duplicates. The best way is to have each entry have an Autonumber as the Primary key. Your Junction table should contain the following:

tblJunction
JunctionID (Primary Key)
TypeID (Foreign Key: Either Service, Hardware or LOS)
TypeForeignKey (Foreign Key for the above mentioned type)
TNE_NumberID (Foreign Key)

If you dont want to do a Junction Table and the TNE has data specific to itself, you could also have a table for TNE data. Create a TNE number, assign a Primary Key to it. You can also store any data related to each TNE. Then store the TNE Primary key in each record for Service, Hardware or LOS.

Thanks - seems I am on the right track but the devil is in the detail.

Is there a method by which to 'auto' populate this table through queries or will this likely be a manual process?

Also - your 'TypeForeignKey' field - what is the purpose of this? In the field above that, do you want to record the 'type' of activity. So there may be another table where I have a simple 3 row table with Services, LOS and Hardware as options and this is then populated into TypeID or am I understanding this wrong?

I do like the approach (and saw something later while reading) of TNE ID (from TNE table using the IDs and not the numbers) and then using each 'jobs' specific PK. That said, will need to find a way for the TNE numbers to 'propogate' as there are at least 3 sources where the TNE number can be started from as the order for each service can be placed irrespective of other services.

 

[Scooterbug]

You can use an append query to fill in the table.

 

[Chaz]

You can use an append query to fill in the table.

Hi. I added a few more questions (edit) after you had responded. Appreciate any further info / feedback.

Thanks!

 

[Scooterbug]

Also - your 'TypeForeignKey' field - what is the purpose of this? In the field above that, do you want to record the 'type' of activity. So there may be another table where I have a simple 3 row table with Services, LOS and Hardware as options and this is then populated into TypeID or am I understanding this wrong?

My bad, I was a bit vague. What it should say is:

TypeForeignKey (Foreign Key of the above mentioned type)

By storing the Primary key of each activity, you can then reference it. As for having a table for the different activities, yes the ID is stored in the TypeID so that if you have different tables, you can determine what type of activity it is.

I do like the approach (and saw something later while reading) of TNE ID (from TNE table using the IDs and not the numbers) and then using each 'jobs' specific PK. That said, will need to find a way for the TNE numbers to 'propogate' as there are at least 3 sources where the TNE number can be started from as the order for each service can be placed irrespective of other services.

The database that I manage here at work is basically a sales database, so I tend to look at things from that perspective. In your case, I see the TNE as an Order, and the different activities as line items. So for me, it makes sense to have a TNE table with associated data (DateCreated, Desc, etc) and have a relationship to the activities.

As for creating a TNE from one of the 3 activities, I picture having a Parent form with the TNE infor on it, then 3 subforms, one for each Activity nested in a tab control. You can then link them via the TNE Primary key and all the data is there. Going this route will also allow the creation of a new TNE when necessary. I would imagine that regardless of which activity is first, a TNE needs to be created prior to that.

 

[Chaz]

I give up. It doesnt matter what I try - when I try and associate the TNE numbers to the 3 items (even 2, forget LOS) I get recordset cannot be updated errors and I dont know why.

I created a new table for the tne_numbers by importing them and removing duplicates. This way I know that the TNE number exists in this new table.

I think just simply link back to them in the tables as I tried use the actual TNE Number as the PK.

It doesnt matter how I do the relationship or if I try with no relationship and only do it when I query I either get duplicate records (3 for one TNE where if I edit the one record the others also change) or I get the recordset error which I have tried to tackle before.

I get 'ambigious outer joins' too - so tried using a simple query to connect to another simple query - as soon as I combine them, all hell breaks loose again.

I need to run a major report that is already overdue and I cant combine the records without causing problem (apart from some level of read only).

Wish I could sit with someone for 1 hour to understand whats going on. Argh.

Thanks for your help once again - will read through your details and see what else can be done.

Perhaps I need to create a TNE Table with a separate field for the ID (PK) and then manually do some records to update with the new key (versus saved TNE number) and see if this works.

Its almost like I have a dodgy file and I keep building on it only for it to crash - might be nothing wrong with my query but the file is screwed. Perhaps not - grasping at straws here clearly ....

 

[Scooterbug]

If you want, post the database...include a bit of data and I'll take a look at it tomorrow. I dont have A2007, so if you do, save it as an MDB. Compact and Repair and zip to lower the size.

 

[Chaz]

If you want, post the database...include a bit of data and I'll take a look at it tomorrow. I dont have A2007, so if you do, save it as an MDB. Compact and Repair and zip to lower the size.

Thanks mate - will see if I can get a scaled down copy for this purpose.

Out of interest - you in the UK?

 

[Chaz]

Sorry - wanted to add. The database has a split frontend but suspect in this case you are not too concerned with the front end?

If you can see the table stuctures and how the 3 core tables need to work together - that may show the error of my ways :-(

 

[Chaz]

Nevermind - got it up when zipped. Tried to keep as much as possible to show some level of detail. Ive needed to strip out many tables to get the size down.

I suspect if you look at t_services (even the scaled down version) you will find that there is probably better ways to do things as info is duplicated between tables but mainly as I cant get them linked to the TNE which is the original issue.

Many thanks for your help.

 

[Scooterbug]

Nevermind - got it up when zipped. Tried to keep as much as possible to show some level of detail. Ive needed to strip out many tables to get the size down.

I suspect if you look at t_services (even the scaled down version) you will find that there is probably better ways to do things as info is duplicated between tables but mainly as I cant get them linked to the TNE which is the original issue.

Many thanks for your help.

Ok, I took a look at it. Not exactly sure what you final goal is, but I'm assuming it has to to with viewing and entering data for LOS, Hardware and Service. So, I did up a quick form for ya. But first, I noticed a few things.

1. No primary key for LOS Table. Cant use the junction table properly unless you have some way to distinguish unique rows. You cant use the TNE number since there can be multiple instances. I went ahead and added that.
2. Service Table. I notice you had some entries where there were _A and _B. When you come into situations where you are recording the same thing, it's best to create a new table. What happens if there needs to be a _C later on? The way the table is now, you have to go in and modify the table. If you had a separate table where you recorded the data that is unique to each _A, _B, etc then you can have as many as you want. You tie them together with a Primary/Foreign Key.

Now, the database. First, I created a Junction table. I then ran 3 queries (I saved them so you could view them) that dumped the TNE_Number, Primary Key and TypeID into the Junction Table.

You will see a form called frmDisplayTneData. There is a combo box who's row source is the TNE table. When you choose a TNE number, there are 3 subforms, one for each type. Using the Junction Table, you can filter the results. By adding a criteria to the queries that are the record source for the subforms, you can have them display the relavent data. I also added some txt boxes to display the record count based on the TNE number.

Now, adding new records. Because the results are filtered by the Junction table, the queries are not updatable. But, that doesn't mean you can't add records. On the Hardware subform, there is a button for adding new records. When clicked, it opens up a new form which doesn't have the Junction Table in the record source. You would fill in all the data and press the Confirm button. What that does is checks to make sure that there is a TNE number and that the TNE number exsists. If it doesn't exsist, a message box will appear asking if you want to add it. If so, it will put the TNE number in the TNEtable. It will then find the Primary Key for the new TNE number, and using that data add the record to the junction table. You will have to requery the main form, but it should show up.

Take a look at the code behind the buttons and you should be able to duplicate it for both LOS and Service as well. Any questions, let me know.

Oh, and no, not in the UK

 

[Chaz]

Thanks - I need to head out for a while - will check it later.

Many thanks once again for your help.

 

[Chaz]

I can see what you have done but am still a bit confused.

The junction table does not allow edits - so back to square one?

Also - LOS table - didnt need a PK before so thanks, thats noted.

What is the purpose of the Type ID? Just to know what type of service it is, if needed?

Thanks once again!

 

[Scooterbug]

I can see what you have done but am still a bit confused.

The junction table does not allow edits - so back to square one?

My bad..since there is a table that stores the TNE Numbers, you dont need the Junction table in the record source for the subforms. What you can do is create a text control on the main form. You can set the visible property to False to keep it hidden. (I called it txtTneNumHolder). Add the following to the After Update event of the combo box:

Me.txtTneNumHolder = Me.cboTNEnumbers.Column(0)

Now go into the record source for each of the subforms and delete the junction table from the query. Add the following to the criteria for the field that has the TNE number:

forms!frmDisplayTneData!txtTneNumHolder

Now, when you choose a TNE number from the drop down box, you will be able to edit records/add records for each of the Types.

Also - LOS table - didnt need a PK before so thanks, thats noted.

Not a problem...good habit to get into is to create a PK first whenever you create a new table.

What is the purpose of the Type ID? Just to know what type of service it is, if needed?

Correct. This also allows you to add more services if need be in the future.


I've made the changes described above and reposted the db for you.

 

[Chaz]

Thanks. Ive used similar before - using the TNE number to 'filter' the view - need to see if that is what you are doing here.

With reporting of the tables then - seeing as reports are 'read only' maybe the junction table approach will work - need to see.

Thanks again for your help.

Just as a side note - the v2 database does not lookup anything. If you put in a TNE number, I have not yet managed to get it to 'show' any associated records of services but I understand what you are trying to show there.

 

[Scooterbug]

Just as a side note - the v2 database does not lookup anything. If you put in a TNE number, I have not yet managed to get it to 'show' any associated records of services but I understand what you are trying to show there.

Hmmm...works for me. Try TNE 155905. Try pressing Enter after you select the TNE number. The subforms refresh on the After Update event of the combo box.

 

[Chaz]

Hmmm...works for me. Try TNE 155905. Try pressing Enter after you select the TNE number. The subforms refresh on the After Update event of the combo box.

Ah, thanks - didnt notice that.

Not sure if you saw but in v1 database all LOS records were showing - not that it matters at this stage.

On the topic of having multiple items - should these be split from the table - perhaps if you look at t_services youll see how many I have - perhaps segment into logical blocks (of tables)?

 

[Scooterbug]

On the topic of having multiple items - should these be split from the table - perhaps if you look at t_services youll see how many I have - perhaps segment into logical blocks (of tables)?

I did notice that you have quite a few ([whatever_A], [Whatever_B]). When you have instances of that, you have to ask yourself "In the future, will there be more of that stuff that will have to be recorded?". If you answer yes, then it's a One to Many Relationship, which will require it's own table.

Other than that, data that relates to one object should be kept in the same table.