Need help on table relationships 1

[ChrisHaynes]

Hi, I have 2 separate tables, the main table has a list of all the computers in the company along with details about them.

I then have a scanner table which lists all the scanners and contains 'Make', 'Model' etc. along with a 'Department' field and 'PC Name' drop-down field (to show what computer the scanner is connected to)

On the form linked to the scanner table, the user selects the department, and then the computer that the scanner is connected to from the PC Name drop-down box (I have a query which lists all the pc's in the selected department, which populates the PC Name drop-down box)
This data is then stored in the scanner table.

The problem is...

When I change a computer name in the main table which I often have to do, and there is a record in the scanner table containing this PC, this does not get updated in the scanner table, which ends up with the data being incorrect.

I have looked at the relationship 'Referential Integrity' option. But it won't allow me to do this because of the way my forms and tables work.

Because of this, I am thinking my best bet is to use DLookup.

I already have a before update message box on each of the forms which let's the user decide whether to save the changes or not. I was thinking of adding a dlookup function when the user clicks yes, to update the data in the other table which will then syncronise the data.

Does anyone know a code to update the record in the other table?? Or any other solutions to this problem??


Any help on this would be great!


Thanks guys,

Chris.

 

[fneily]

You could continue along your lines, but you can see the problems it's creating. Also, you should definitely study Normalization. Dept and Computer name should not be in the scanner table. Just answer a basic question: if the computer is stolen and the department is dissolved, does the scanner disappear? No. So they do not belong there.
I would have four tables: Computer, Scanner, Dept, ComScan
Computer has computer info
Scanner has scanner info
Dept has two fields, DeptID and Name
ComScan is a junction table. It will look like:
ComScanID (Primary Key)
ComputerID
ScannerID
DeptID

ComputerID, ScannerID, DeptID are primary keys of the other tables.
As you can see, all analysis now drops out. And if you change any name, it's just changed in one table.
REMEMBER - Access is a RELATIONAL database, not an Excel spreadsheet.

 

[ChrisHaynes]

Thanks for your help fneily. I see your point I do have separate tables for:

-Department (containing Department and Department code)
-Computers (contaning computer name, model, spec, department, software etc)
-Scanners (containing, department, computer name, model etc)

I think I need to look at primary keys etc. I am quite new to access so have just been teaching myself basics and obviously missed one of the main ones!

So you think I should just have a table containing scannner info, without the computer and department info??

Because the reason for including these details is to make each scanner unique by including the computer name that the scanner is attached to. Is this the wrong way to go about the situation??



Thanks for the useful info,

Chris.

 

[fneily]

That's what the junction table I mentioned does, plus more. You sound like one scanner to one computer. Maybe one day, two computers next to each other will share the same scanner. Now you have a one-to-many situation. The junction table will allow for this. I've even seen business' where two department heads sit next to each other so they also share one scanner. And departments sharing computers. It depends on your physical setup. But, again the junction table allows for this.
Notice in your design, you're listing(or typing) computer name twice. No reason to do this. Department you have it listed three times. No reason for this. Let's look at the junction table: c = computer, s = scanner, d = deparment
comscan1 d1 c1 s1
comscan2 d1 c2 s2
comscan3 d2 c3 s3
comscan4 d2 c4 s4 but now you can also have
comscan5 d1 c5 s1
comscan6 d2 c6 s3
comscan7 d3 c7 s2

So I need to know which computers are hooked to s3. Through a simple query connecting the scanner table to the junction table which in turn is connected to the computer table, I'll get c6, c3. I want to know which departments use s2. Again, a simple query and I get d3, d1.
Etc.
Again, changing names of any equipment will only be in one table because everything is connected through the keys.
By the way, this is not an easy concept. But that's the way Access works.
That's my opinion.

 

[ChrisHaynes]

Thanks fneily. That really helps my understanding of how tables work and I can see how to make my database easier for the user. I'll have a go at this technique and let you know if I have any problems.




Thanks again for your help,
It's much appreciated.


Chris.

 

[ChrisHaynes]

Hi again fneily,

Could I ask a favour and let me send you a copy of my database for you to check out? Because I don't think that this technique would be possible to incorporate into my database??

Thanks,
Chris

 

[fneily]

Unfortunately, Tek-Tips doesn't allow to post email addresses - they wipe out the post, discovered this the hard way.
Don't know why table relationships can't be incorporated into your design.
How about this way: I'm going to ASSUME(and this is the BIG assumption) you have ONE scanner for ONE computer. And ONE computer belongs to ONE department.
Then there is a one-to-many relationship with scanner-to-computer and one-to-many relationship scanner-to-department.
So the Scanner table contains the scanner info PLUS the primary key to Depttable and Primary key to ComputerTable. (not computer name or department name)
The Dept just the department info. The Computer table just has the computer info.
So you don't need Department info in the computer table because you can go through the scanner table to get that - computer to scanner to dept.
So this keeps your basic design. And again, you update names in only one table.
That is if my assumption is correct.

 

[fneily]

Then there is a one-to-many relationship with scanner-to-computer and one-to-many relationship scanner-to-department."
Change that to one-to-one's.

 

[ChrisHaynes]

Thanks for your reply again fneily,
The only reason I have department in the scanner table is to filter and populate the computer name box. Not all computers have scanners, so if a PC didn't have a scanner, how would I know what department the PC is in??


Thanks,
Chris.

 

[fneily]

See, my assumption was wrong then. I assumed one scanner, one computer. The criteria changed. That's why the other way I presented works better. You could have a junction table where you could have one computer, no scanner, one department. Or any combination thereof.
You're going to have to sit down, figure out the different combinations for your situation, and future configurations, then build the tables from there. Using normalization and junction tables make the connections fall right out.

 

[ItIsHardToProgram]

The best way to go with this is a many-to-many relationship, just in case anything change. It is easily done, you place a table, in middle of ur other tables. That table containing Compid, depID and ScanID. These ids are most likely numbers, per computer, so each comp/scan/dep have there own number. you only have to link comp id to the main table, and etc.... same with all, you apply referencial integrety, and ur good, u might also want to place a primary key in the main table, but you can not put any id as primary key, since its already defined in ur other table. Once your relation is done, you only do a query that shows, comp etc... and forms that allow you to write new computer relationships, so u can create new ones, or change old ones.... any way I think thats where your getting at and what you want, if you did not already do it. If I am not clear there are MANY facts... just google it or search here.

 

[ChrisHaynes]

ItIshardToProgram, thanks, I havn't thought of that. I am going to place a table in the middle like you say and change everything from there. I think I'll do a copy of the db first because I've got a feeling this is going to be messy!!

Thanks again,

Chris.

 

[fneily]

First of all, Itishardtoprogram reiterated my first post.
Also he states, "The best way to go with this is a many-to-many relationship". Then he explains, exactly what I did, to create a Junction table to produce one-to-many's. I don't want Chris to think that ANY relational database likes many-to-many relationships. They don't. You get absurd answers from them. When you have a many-to-many situation as I think this is, you create multiple one-to-many's to resolve it.

 

[jojigirl]

What I want to know is:

1. How do you form the relationships? Primary table to junction, and then junction to related table? Or just a one to one on the joining to the related?

2. Also does this create more indexes?

If done properly, this should allow you to enter new scanners, computers or departments in anyone of the tables?
And then your forms can pull from many tables?

I'm having a time with my contact management dbs.

I have a
Colleges table with ID
Contacts table with unique id
Contact Type with program contact as primary
Juction with all three primaries

But I can't make the right connections some how, as my form won't let me make entries, even though I can add to the table. It's in the form somehow.

The form has all the fields from the contact and only the college name from the college table. I've thrown in college id from both tables as a precaution, but upon entering it says it changes to the Table? you requested would create duplicate values in the index, primary or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redifine the index to permite duplicate entries.

Any suggestions?

Thanks.

 

[jojigirl]

And another thing... I read that in the junction table you identify all three as a primary. Is that correct?

Thanks again.

 

[fneily]

In a many-to-many situation, there is no primary table and related table. THAT'S THE PROBLEM. So by introducing a junction table, you create two one-to-many relationships.
Customers to Junction to Product. The junction table contains AT MINIMUM, the primary keys of the other two tables. They are NOT the primary key of the junction table. They are FOREIGN keys. The junction table has it's own primary key. Also any fields common to Customers and Products are kept in the junction table such as date-of-sale, quantity. You connect the tables in queries. Then base your form on that recordset. You may have to use DAO/ADO to add/edit the data.

You do not have an Updatable query. Please See:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[jojigirl]

Thanks for the reference, fneily! I'll pore over it tonight and see how far I get.