Linking seperate tables to seperate records

[dans107]

I'm at work and I'm revamping some Access databases we have.

I've got 2 databases onto a more manageable database, in the form view, used to input data and the like.

Well I have several tables that I want to add to different records.

For example, there are 500 records. On record 8 I want to link table 1, on record 50 I want to link table 2, so on and so forth.

However, I can only figure out how to change the link so that all of them change - is there a way to change individual ones?

I apologise if that doesn't make sense, but any help would be great appreciated.

Dan

 

[neileg]

Nope, no sense at all.

You can't add tables to records, only records to tables.

Have another go at explaining

 

[dans107]

:lol:

Right -

I have a form, yeah? And I've made a hyper link, so when you click on it, it will take you to whatever the hyper link is?

So far, so good.

Now, I have several tables that I want to link to the hyper link, so when the hyper link is clicked, the table is brought up?

Got it so far?

Well I know how to link a table, and I can get it to work when I click the hyper link. My problem is that this hyper link is the same for all records, whereas I want it different.

See if this helps -

Record 1 - Table 1
Record 2 - Table 2
Record 3 - Table 3

That means I want record 1 of the form, to have a hyper link to table 1.

This is what I'm actually getting -

Record 1 - Table 1
Record 2 - Table 1
Record 3 - Table 1

Is that explained a little better?

Dan

 

[neileg]

:lol:
Right -
I have a form, yeah? And I've made a hyper link, so when you click on it, it will take you to whatever the hyper link is?
So far, so good.

No. A hyper link will normally take you to an object outside of the database, perhaps a documnet or an image or a web address.

Now, I have several tables that I want to link to the hyper link, so when the hyper link is clicked, the table is brought up?
Got it so far?

No. If you want to go to the contents of an Access table, you need to create another form that displays the table contents and instead of a hyper link, you need to open the form.

Well I know how to link a table, and I can get it to work when I click the hyper link. My problem is that this hyper link is the same for all records, whereas I want it different.

See if this helps -
Record 1 - Table 1
Record 2 - Table 2
Record 3 - Table 3

That means I want record 1 of the form, to have a hyper link to table 1.
This is what I'm actually getting -
Record 1 - Table 1
Record 2 - Table 1
Record 3 - Table 1

What exactly are Record 1, Record 2, Record 3? Are these records in a table that you are displaying in a continuous form? If so, when you add an object to a continuous form, it is exactly the same object on each form. If you want to hold a different hyperlink for each record, you need to hold this in your table. This way, the continuous form will display a different link for each record because it comes from that record.

Is that explained a little better?
Dan

Don't know! Does my answer feel any better?

 

[dans107]

my head hurts.

In record 1 of the form I have, I have a hyperlink - when I click on it, it opens up a table of details to do with the person who is on the record.

Now, this is fine, exactly what I want.

Except, I need a different hyperlink for every record, and I don't know how to do that?

How about that?

Dan

 

[neileg]

my head hurts.

Mine too!

In record 1 of the form I have, I have a hyperlink - when I click on it, it opens up a table of details to do with the person who is on the record.

Now, this is fine, exactly what I want.

Except, I need a different hyperlink for every record, and I don't know how to do that?

A hyperlink is a specific kind of data type within Access. Do you mean that this is really a hyperlink, is is it something that behaves like a hyperlink? Does it open a table or does it open a form that shows the table contents?

You can see the application, I can't. It's hard for me to distinguish bewteen what you think is happening, and what is actually happening. It may be that the 'hyperlink' is actually opening a second form, and it is the field that links the data in the first form and the data in the second form that is going wrong.

To be honest, it would be usual to handle this kind of display by having a form to hold whatever it is you are showing for each record, and a subform to show the detail that you are pulling from another table.

Can you post a stripped out version of the database, or at least a screen grad of the form you are talking about.

 

[dans107]

I'm going home now, but I've e-mailed two screen shots to my home e-mail address.

One of them is the screen I'm viewing, the other is what I want to appear.

Hopefully it'll become more clear.

The hyperlink works by the way.

I assume it's a hyperlink...I clicked "insert hyperlink", and it came up. I click, it opens something new.

By the way, I live in Teesside (about 50 minutes down the road from you ).

Talk to you later when I get in.

Dan

 

[dans107]

Sorry I didn't get back earlier, I didn't get the chance at home.

Screengrab 1 = what I'm seeing. The red line shows the "hyperlink" - this is what I want to be able to click on, to bring up screengrab 2.

Screengrab 2 = the table I want to appear when clicking on the "hyperlink".

Now imagine there were Screengrabs 3 and 4, which would be record 2 and table 2 - these are also what I'm wanting linked together.

I hope that in some way eases my problem so you can help!

I heard something about Macros maybe able to help me? I've never used that function so I don't know a whole deal about it .

Dan

 

[neileg]

A smog monster! Never mind, I'll still help!

Right, first things first. You don't want a hyperlink here. Even if it seems to give you the right answer for record 1, it will always lead you to exactly the same answer.

I am suspicious that your database structure is all to hell, so you might have to address that before you get what you want. I also suspect that you're pretty new to database design. Here's a first stab at what you might need.

You need a table for offenders
tblOffender
OffenderID Primary Key
Forename
Surname
Address
etc

A table for offences
tblOffences
OffenceID Primary Key
OffenderID Foreign key from tblOffender
OffenceLocation
etc

A table for Exclusions
tblExclusion
ExclusionID Primary Key
OffenderID Foreign key from tblOffender
ExclusionDetails
etc

Your main form would be based on a query that pulls together the offender and the offences (assuming there can be more than one offence per offender)
Another form, or a subform would extract the Exclusions from the exclusions table that relate to that offender.

Sorry, just realised I have a meeting to go to. Will get back to you later.

 

[neileg]

OK back at my desk.

Perhaps I need to point out that tables are for storing data. Only the most elementary applications would use tables to display data. Where you want to extract some of the data from a table, you use a query. To get data from the user you need a form, and to display the data to the user you need a form or a report.

The idea of linking Record 1 to Table 1 should be reinterpreted. What you need is to run a query that picks up the OffenderID from your main form and extracts the data from the Exclusion table that has the same Offender ID. You then display the data the query has found in a form. This way, the query and form are always the same, but the content is controlled by the OffenderID.

The three table structure I set out is fine so long as you only have one offender per offence, since that's how your form shows it in the screengrab. It allows for more than one offence per offender, and more than one exclusion per offender. However, it may be that there is more than offender per offence. This would require a more complex structure.

I'm attaching a sample that should show you what I mean.

 

[dans107]

I did it!

But, it was really easy

I'm going to check you way out in a while though, incase I can improve mine .

Thanks for the help,

Dan

 

[Mile-O]

I'm going to check you way out in a while though, incase I can improve mine .

If you want to understand Neil's way (which is correct) then you should do a search on, and some reading up on, database normalization.