I need a lot of help on figuring out this relationship..any ideas? 6

[Tasuki]

I have all these tables:

Instrument
-Manufacturer
-Purchase
-Sensors
-Deployment History
--Location
-Repair History

I want to be able to access all this data from "Instruments", but looks like I can only make one subform per table? Most of these should be One-To-One I believe, except Manufacturer is usually One-To-Many Instruments. Oh and Instruments is One-To-Many Sensors. The rest should be One-To-One with the Instruments.

I've tried doing it by one Unique ID for each table, without success. I'm just stumped now..

Any help greatly appreciated.

T

 

[Darrylles]

Hiya,

Good 'un, you add unique ID's for every table - some don't agree with this, but I always add an autonumber whether the table data will be unique or not.

Still, you haven't given enough info. here to describe your problem. I can make assumptions, but there are so many.

Please explain exactly what you want to do.

p.s. You have defined the relationships between tables yes?

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[SBendBuckeye]

I wouldn't mind helping you but you would need to send some sample data to really be able to answer properly. You can try to post here or contact via mail.

Assuming you don't need to update on the other tables and that you are carrying the various foreign keys in your instruments table and further assuming that you have defined relationships based on the foreign keys you could create a query or series of queries to pull all of the information together for you.

Good Luck!

 

[Tasuki]

Hmm, how can I explain this simpler...

This is really difficult to explain right now since, all my DB files and data is on the desk at work. But I'll try again...

---- here I go again ----

The above 7 are all tables. I will make a unique form for the instruments. The instruments form will be the main form to update all those tables above. I want from that main form to be able to access the other 6 forms to update corresponding data to the corresponding instrument. Each table contains anywhere from 4 to 10 fields.

What's the best way to setup a relationship so that each relationship is related to the instruments in some way, or maybe best way to set up the forms so that they link to the instruments form, maybe a tab form is best?

Hope that explains my situation better. It's difficult for me since I'm still a newbie to Access.

Thanks for your replies, hope to see some more.

 

[LittleSmudge]

First of the Normalisation job :-
If you have tables that are One-to-one relationships with table Instrument then you are going to have an extreemly hard job pursuading us that they ought to be in separate tables.
One-to-one links are ( almost always ) a sign of two tables that really ought to be ONE table.

Next point.
Unless you can justify why not ( in a single simple sentence ) then EVERY table should have a Primary Key.
As Darrylles said he likes AutoNumbers, ( I use AutoNumbers when the case demands it ) but there are some die hard purists that abhor their use.

If the tables Manufacturer and Sensors are each at the many end of relationships with Instruments then you will need a Foreign Key fields in both Manufacturer and Sensors.

I recommend the following nameing convention :-
Table names start with tbl eg tblInstrument
The Primary key in a table has "tbl" taken off the front and "Id" added to the end eg InstrumentId

Foreign Keys are the same name as the Primary Key that they point to except "ID" is replaced with "Ref". ( Foreign Key fields must be the same data type and size as the Primary Key they point to { Except Primary AutoNumbers which need Number, Long Foreign Keys } )

eg
tblManufacturer
ManufacturerId
InstrumentsRef
OtherFields
etc..

This convention makes it instantly clear which bit of the structure you are working on.

In the database's Relationships window establish relationships between InstrumentId and tblManufacturer.InstrumentRef and between InstrumentId and tblSensors.InstrumentRef

Bind the main form to tblInstruments
Then Create a form Bound to tblManufacturer ( called sfrmManufacurer )and a form bound to tblSensors ( called sfrmSensors ).
Back in the Main form design you can create a subform containing the sfrmManufacturer and
another subform containing the sfrmSensors.

The SubForm Wizard will help you to link them together correctly.

You can have as many subforms on the main form as you have space to lay them out.

Alternativly you can have a button that opens a full screen form for (say) Sensors and only show those tblSensor records that are related to the current Instrument if you want to.


'ope-that-'elps.

G LS

 

[Tasuki]

Well..

>If you have tables that are One-to-one relationships with table Instrument then you are going to have an extreemly hard job pursuading us that they ought to be in separate tables.

----You are right, most of the data can be placed all in one table. But from our point of view, a table filled with so much information is messy and cluttered, we want it to look more organized and be able to see everything related "one-to-one" (aside from Manufacturer and Sensors). So in this case we have pulled fields (a lot of them) and separated them in their respective tables. We want to try it this way first, but at a last resort may combine some of the tables.

>Unless you can justify why not ( in a single simple sentence ) then EVERY table should have a Primary Key.

----At the moment I do have a Primary Key for each table called ID which is the "Auto-Number". I just haven't figured out how to go about and set the relationship so that most of the tables can be "One-to-one" with the Instruments table.

>I recommend the following nameing convention :-
Table names start with tbl eg tblInstrument
The Primary key in a table has "tbl" taken off the front and "Id" added to the end eg InstrumentId

----I really like this naming convention of yours, a lot better to read and understand the relationships that will be setup.

>In the database's Relationships window establish relationships between InstrumentId and tblManufacturer.InstrumentRef and between InstrumentId and tblSensors.InstrumentRef

----Can there really be two refs connected to one InstrumentId?

Subforms is another good idea, but I want to stay away from it for this database. I'm really more interested in bringing up a full screen form for sensors that will only bring up the record for the current instrument being looked at. How would you go about doing this.

I know how to bring up another form from a button, but it usually starts at the first record in that table. Is there a way to popup the form with it's corresponding data?

Here's how my relationships with the tables have been setup thus far:

tblINSTRUMENT tblSENSORS
-ID* -- 1 to MANY -- -ID
-Name tblMANUFACTURER
-Manufacturer -- MANY to 1 -- -Name*
-Model Number -Address
-Serial Number -City

tblPURCHASE

tblDEPLOYMENTHISTORY

tblREPAIRHISTORY

tblLOCATION

The rest of the tables are all "strays" and should be "One-to-one" in some way with the 'tblINSTRUMENTS'. The * (asterisk) means that they are primary keys.

Thanks for all the excellent replies, I know this is really confusing since I'm trying to say it the way I see it (especially since I'm learning it from scratch myself). But thanks all the same. All replies have been considered, and I'm trying to learn from your advice.

T

 

[Darrylles]

Hi again,

Littlesmudge is right about the one to one's -> should all be in one table (I would like to bet that there are few 1 to many's in there anyway which will require 'splitting', but in a 1 to many sense).

Records that are split into many records that are all essentially the same record is a cluttered, messy and unorganised way to store a record.

I use 'pk' for the autonumber primary key and 'tablename_fk' for the foreign key in the many table.

The naming convention idea applies to ALL objects - textbox txt ,commandbutton cmd, checkbox chk, label lbl, form frm, query qry, combo cmb, listbox lst etc etc.
It does make code much easier to understand at a glance.

To link your '1 to 1' table simply create a long integer field in the 'other' table and define a relationship from it to the 'one' (main) table.
I don't agree with it - but it will work.

[Can there really be two refs connected to one InstrumentId?] Yes - of course.
A Country has many Counties, it also has many film stars,
(stupid example - but still a fact).

[tt]
tblCountry tblCounty
pk 1----- pk
name | | name
| |--> Country_fk
|
| tblFilmStars
| pk
| Filmstar_name
|----> Country_fk
[/tt]
By using the Country pk, you can pull out all Counties in that Country and (or) all FilmStars in that Country.

To bring up a form from a parent form that shows a child record related to the 'main' form record, then paint a command button and answer the wizard's questions to open up a new form - check carefully what it asks you to supply (record links etc).
Check the code syntax of the created form (and button) to understand how it does it.
Here is where 'correct' relationships are essential and you
will no doubt have problems because you have incorrectly split tables up.

Your bed mate - there really IS logical reason behind our 'madness'.

Kind regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[LittleSmudge]

Okay Tasuki - I got one of your One-to-Many relationships the wrong way round earlier - I see now ( and the names make a lot more sense that way round. )

I'll really back up Darrylles' excellent comment that
"Records that are split into many records that are all essentially the same record is a cluttered, messy and unorganised way to store a record."

You said
".. .. So in this case we have pulled fields (a lot of them) and separated them in their respective tables. We want to try it this way first, but at a last resort may combine some of the tables."
Please - Save yourself the pain and frustration, the messy form links and the potentially unstable data links - do it RIGHT the first time. - ONE table, all of the fields in the table bearing the same relation to each other.
You can easily pull SOME of those fields onto one form and other fields onto another form. Mixing and matching fields as they suit the purpose of the form is simple once they are all in the same table. And linking them to the relevant collection of records in the other tables is so much more reliable as well.


Having said all that - looking JUST at the names of the tables - I'd suspect that both tblDelpoymentHistory and tblRepairHistory will be on the many end of one-to-many links to the tblInstrument won't they. You will "Deploy" an instrument more than once ( hence you need a 'history' ) and each "Deployment" should be a record in that table. Fields like OutDate, InDate, DeployedTo etc


The structure you end up with is more like :-

tblManufacturer
| . .ManufacturerId
| Address1
| Address2
| Address3
| Town
| County
| PostCode
|
| tblInstrument tblSensors
| InstrumentId. . | SensorId
| InstName | . . .InstrumentRef
| . .ManufacturerRef | SensorName
ModelNumber | Info1
SerialNumber | Info2
PurchaseDate |
PurchasePrice |
PurchaseFrom |
|
tblDeploymentHistory |
DelpoymentHistoryId |
InstrumentRef . . . . . . . |
OutDate |
BackDate |
DeployTo |
etc .. |
|
tblRepairHistory |
RepairHistoryId |
InstrumentRef . . . . . . . |
RepairDate
WorkDone
etc ..

( I know all of the | lines are not going to come out in the right place because you can't do [ bold ] and [ u/line ]
within [ code ] [ / code ] blocks.
Just imaging all of the | lines near the middle of the page as one continuous vertical line. )
G LS

 

[Tasuki]

Darrylles: Your post is extremely helpful, I don't know the right and wrong about building databases since I'm learning it through books by myself (and from all you expert's advice). And when I go through books, I only look for what I need . Well, I feel dumb now and have resorted to placing the One-To-One's all in one table.

LittleSmudge: Yes you are right about the History tables (my mistake). I have resorted to using your structure and adding the other two tables into the Instruments table. Thanks a lot, I'm going to see how it goes.

I have a question from the structure, the InstrumentID is linked as "One-To-Many" relationships to InstrumentRef of tblSensors, tblDeploymentHistory, and tblRepairHistory. Is there any use for the SensorID / DeploymentHistoryID / RepairHistoryID now? Also the Refs are not primary keys right?

Also, tblPurchases should be "One-To-Many" tblInstruments since there can be an order for many instrument on one purchase form, what's the best way to link this to the instruments? Have a 'PurchaseRef' field in tblInstruments and have it connected to the 'FormID#' (not an autonumber) in the tblPurchase, sort of like the tblManufacturer?

Well, all your advice and tips have helped me a lot. Thanks and keep up the great work.

 

[Tasuki]

Hmm, what is the DataType for a REF when an ID is an autonumber? Access only allows one autonumber per table, so how can a Ref link to an ID?

Is it just a number which will automatically have the same number as the ID number that it is linked to?

Another dumb question probably, but thanks if you can help.

T

 

[LittleSmudge]

Tasuki - you said
I have a question from the structure, the InstrumentID is linked as "One-To-Many" relationships to InstrumentRef of tblSensors, tblDeploymentHistory, and tblRepairHistory. Is there any use for the SensorID / DeploymentHistoryID / RepairHistoryID now? Also the Refs are not primary keys right?

The xxxRef s are NOT PrimeKeys they are Foreign Keys ( Foreign Keys are the things that point from the Many end to the One record in the One end of the relationship )

As was mentioned very much earlier
Unless you can justify why not ( in a single simple sentence ) then EVERY table should have a Primary Key.
As Darrylles said he likes AutoNumbers, ( I use AutoNumbers when the case demands it ) but there are some die hard purists that abhor their use.
SensorID / DeploymentHistoryID / RepairHistoryID ARE those PrimeKeys.


Next
Also, tblPurchases should be "One-To-Many" tblInstruments since there can be an order for many instrument on one purchase form, what's the best way to link this to the instruments? Have a 'PurchaseRef' field in tblInstruments and have it connected to the 'FormID#' (not an autonumber) in the tblPurchase, sort of like the tblManufacturer?

You're really getting the hang of this aren't you.
Have a 'PurchaseRef' field in tblInstruments - YES
BUT Then link it to the PurchaseId field in tblPurchase

The "#" character means a date delimiter to Access users. Avoid using it in object names as it becomes extreemly confusing when you try to debug code in the future.

Where has FormId come from ? I've not seem that one about before. As a general comment FORMs don't need Ids, it is the records that the form displays that need Ids. So using a term like FormId is not a good descriptive name for whatever you are using it for. As your experience grows, I can assure you that the desire ( need ) for descriptive names will become all too apparent !


Next
Hmm, what is the DataType for a REF when an ID is an autonumber? Access only allows one autonumber per table, so how can a Ref link to an ID?

I wondered how long it would take before THAT question surfaced
And you're quicker than many !
The Foreign Key ( Ref ) equivalent of an AutoNumber is Number-LongInteger - No other number type will do.


Keep going - your doing great.

G LS

 

[Tasuki]

Everything is working great thanks to your guidance and advice (both of you). This isn't the appropriate question to ask in this forum but since you've helped me so much through this problem and are more familiar with it, I'll ask it here first before moving it to the the right forum.

I have made a form for the "Instruments" and placed 3 SubForms on it (namely: Sensors, Deployment/Repair History) and it works great!

But my supervisor, wants me to try a 'linked' form, I first tested making an Instrument Form linked to the Sensors Form. I removed the ID and REFs from the forms, because we don't want the inputters to touch that (this was also removed in the Subforms Form which worked perfectly). Now when I try to input data into the Sensors Form, I get this error:

"You cannot add or change a record because a related record is required in table 'Instruments'."

I realized that the only way to get this to work correctly is to leave in the REF Field and manually input the REF number that corresponds to the ID in the instrument. Is there a way to get it to place it in there itself as soon as I enter new data?

T

 

[WildHare]

T, your LINKED forms will want to be based on queries that return the MANYs of the specific machine thats on your ONE form.

DON'T remove the REF/ID fields from the forms - if you don't want users changing them (probably a good idea..) then either make the fields INVISIBLE or LOCK them against edit. But you need them on the Linked form so that ACCESS can...well...LINK the two forms.

The easiest way to do this is to first design QUERIES that will return the data that you want, and then design forms to display that data.

When you go back to the main form and insert the control (a command button?) to open the "linked" form, and follow the wizard along, just make sure you check the "Open the form and display SPECIFIC data" or whatever it is - this will cause Access to write the correct criteria/filtering code to put behind the button. If you do it right, when you view "Machine X" on the main form, and then open linked form "Y", you should see ONLY those records that relate to "Machine X" on form "Y".


Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Tasuki]

Hey..

>DON'T remove the REF/ID fields from the forms - if you don't want users changing them (probably a good idea..) then either make the fields INVISIBLE or LOCK them against edit. But you need them on the Linked form so that ACCESS can...well...LINK the two forms.

----Even if I have them on the linked form, locked or unlocked, the user still needs to input the corresponding REF # to the ID #, rather than having the user input it, I want Access to automatically input that number if new data is inputted into the linked form. I've tried this for the subforms form with the REF and ID removed and when I enter new data, the corresponding number is inputted by Access itself. Of course you can't see this unless you look at the tables.

>When you go back to the main form and insert the control (a command button?) to open the "linked" form, and follow the wizard along, just make sure you check the "Open the form and display SPECIFIC data" or whatever it is - this will cause Access to write the correct criteria/filtering code to put behind the button.

----Yes, this is what I did, but... what happens if the linked data is empty, and the user needs to input it. Access should know when to put in the corresponding REF so that they link up correctly the next time it is viewed, this shouldn't be a task for the Data Entry person to do, as ID and REF should work together?

(ie. The data entry person is adding a new record #221, she/he completes entering the data on the main form, opens the linked form to a blank form. She/He begins entering the last bits of data but when she tries to save she gets this error:

"You cannot add or change a record because a related record is required in table 'Instruments'."

But, when she enters the REF number as 221, it lets her/him save and it is correctly linked the next time it is viewed.)

Is this something that has to be done for every single new record?

My take (I'm still new at this so I can be wrong) on this is that, Access should know when to input the REF # when a new form is filled. Since it worked for my subforms Form, it's got to work for the linkedform Form. Right?

Thanks again,

T

 

[WildHare]

If sounds like your joining query has the wrong linking key field being pulled in.

Consider this:
Main ONESIDE Table: Keyed on FOO
etc etc etc...

Sub MANYSIDE table: KEY FRED
ForeignKey : FKFOO
etc etc etc...

I just built a sample db to make sure it worked just fine. I placed the ONESIDE table in the detail of the form.
Then I dragged the MANYSIDE table to the form below it, and linked them in the second step of the drop-process.

I entered 122 as a new RECORD key value for the ONE SIDE. As soon as I tabbed out, the FKFOO field in the datasheet had 122 in it.

Do you need a copy?

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Tasuki]

If you can send it to e-mail: taasukii@hotmail.com.

Hope it's not too big.

Thanks!

 

[WildHare]

I am posting it to my website... go here:

http://www.jmhare.com/wildhare2.htm

It'll be there in a few minutes. (5:45PM EDT 08/06/02)


Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Tasuki]

Thanks Jim, for the trouble you went through putting that up, but.. that's not the problem I'm having.

My Subform Form works perfect, just like your demo.

It's my Linkedform Form that doesn't work. I want it to work exactly as the demo but on a linked form.

Is it possible to do that?

Your site has excellent examples for building a database, I'll bookmark it in case I need something.

Thanks,

T

 

[WildHare]

Ah, yes - my goof.

I will have something for you later this morning, if no one else pushes their oar in before I get back.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[lebanoncedars]

HI there,
Let say I want to blink that text on my form for 5 second then stops then another form opens automatically right after this form where the blinking text is.

Any Help?
Thanks
Lebanoncedars