Relationships question (1 Viewer)

Fira_g

Registered User.
Local time
Today, 05:07
Joined
Oct 17, 2019
Messages
60
He everyone,

I am new to Access and trying to learn by reading, watching and practice.

I have a question regarding relationships between tables.

Lets say I am providing service delivering Items from one location to another.
I have created 4 tables: tblCities, tblStates both linked to tblLocations and tblFromTo.

In the tblFromTo table I have FromLocationID and ToLocationID fields which I want to link to the tblLocation.


Could you advise what would be the best way and practice to do that?
 

Attachments

  • RelationAccess.jpg
    RelationAccess.jpg
    49.2 KB · Views: 106

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:07
Joined
Aug 30, 2003
Messages
36,118
You add the locations table twice, and link each of the fields to a different instance of it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:07
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! Should not be a problem. Simply add another copy of tblLocations on to the Relationship window, Access will add a _1 to its name, and then link the other field to it like you did the first one.
 

Fira_g

Registered User.
Local time
Today, 05:07
Joined
Oct 17, 2019
Messages
60
pbaldy and theDBguy thank you for you quick reply.

I did that before posting here but the data I entered in the tblFromTo table did not appear on the linked tables.
Maybe I've done something wrong somewhere else?
 

Attachments

  • FromToAccess.jpg
    FromToAccess.jpg
    70.8 KB · Views: 103
  • LocationAccess.jpg
    LocationAccess.jpg
    93.1 KB · Views: 103

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:07
Joined
Aug 30, 2003
Messages
36,118
I'm not sure what you're saying. You entered 1 & 2 for the first record and 3 & 2 for the second. Those values are all in the locations table.
 

Fira_g

Registered User.
Local time
Today, 05:07
Joined
Oct 17, 2019
Messages
60
I created locations first and then then added Item in tblFromTo with dates and locations.

After that it should show in expanded tblLocation the Id of from the tblFromTo with the item and dates?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:07
Joined
Oct 29, 2018
Messages
21,358
pbaldy and theDBguy thank you for you quick reply.

I did that before posting here but the data I entered in the tblFromTo table did not appear on the linked tables.
Maybe I've done something wrong somewhere else?

Hi. Simply establishing a relationship doesn't automatically create records for you. Maybe you can post a demo version of your db.
 

Fira_g

Registered User.
Local time
Today, 05:07
Joined
Oct 17, 2019
Messages
60
I have uploaded the file.
Again thank you for all the help.
 

Attachments

  • testrelate.zip
    33.9 KB · Views: 110

theDBguy

I’m here to help
Staff member
Local time
Today, 02:07
Joined
Oct 29, 2018
Messages
21,358
Sorry, got busy yesterday and didn't get a chance.
Hi. Sorry for the delay, I finally got a chance to review your database file. There is nothing wrong with your relationships. Unfortunately though, you won't be able to use subdatasheets or even a form/subform setup to view the related records. It seems using subdatasheets or subforms are limited to an AND matches. They don't seem to work when you want an OR match. What do I mean by that? Well, let's say you have the ID=1 in tblLocations. You can only view a matching record in the child table (tblFromTo) if both FromLocationID=1 AND ToLocationID=1. You can't do something like FromLocationID=1 OR ToLocationID=1. You can test this by changing both the From and To LocationIDs in the table to the same one. You will then see a result when you view tblLocations.
 

Fira_g

Registered User.
Local time
Today, 05:07
Joined
Oct 17, 2019
Messages
60
Yes, I played with it over weekend: it adds values from"FromLocatoin" field but the "ToLocatoin" to the tblLocation table.

That basically means that I would not be able to query by From location of the tblLocation.

Is there any way around it except creating another Location table?
 

Attachments

  • testrelate.zip
    69.6 KB · Views: 106

theDBguy

I’m here to help
Staff member
Local time
Today, 02:07
Joined
Oct 29, 2018
Messages
21,358
Yes, I played with it over weekend: it adds values from"FromLocatoin" field but the "ToLocatoin" to the tblLocation table.

That basically means that I would not be able to query by From location of the tblLocation.

Is there any way around it except creating another Location table?
Hi. No, it doesn't mean that. It's just a limitation of the display. As I said, to display child records, an AND match is required. What you have/need is an OR match. Again, your relationship is fine. Just continue using it. When you're ready to create a report or queries or some forms, let us know if you get stuck.
 

Fira_g

Registered User.
Local time
Today, 05:07
Joined
Oct 17, 2019
Messages
60
I got it, thank you.

One more question regarding the frmFromTo from the file attached.

The source for the From and To is a query that concatenates few field.
When choosing the location it shows ID along with the address, but when selected it displays the ID only.
How can i configure it to show the full address from the combobox?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:07
Joined
Oct 29, 2018
Messages
21,358
I got it, thank you.

One more question regarding the frmFromTo from the file attached.

The source for the From and To is a query that concatenates few field.
When choosing the location it shows ID along with the address, but when selected it displays the ID only.
How can i configure it to show the full address from the combobox?
In my copy of frmFromTo, I don't see any dropdown controls. If you're talking about using a Combobox to select the From and To locations, you can adjust the following properties appropriately: Bound Column, Column Count, Column Widths. After making a selection from the dropdown, the combo can only display the first visible column from the Row Source property. If you want to also display other columns on the same form, you can use unbound textboxes and then use the Column() property to refer to the other columns of the combobox.
 

Fira_g

Registered User.
Local time
Today, 05:07
Joined
Oct 17, 2019
Messages
60
Attached the file I was referring to.
 

Attachments

  • testrelate.zip
    81.9 KB · Views: 106

Fira_g

Registered User.
Local time
Today, 05:07
Joined
Oct 17, 2019
Messages
60
Still no luck to get the combobox to display the location instead of the ID only.
I am using a query that concatenates the address, city and stat in to one string, as a row source.

I apologies if my questions sound dumb, i just started learning
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:07
Joined
Oct 29, 2018
Messages
21,358
Still no luck to get the combobox to display the location instead of the ID only.
I am using a query that concatenates the address, city and stat in to one string, as a row source.

I apologies if my questions sound dumb, i just started learning
Check out the attached...
 

Attachments

  • testrelate.zip
    37.7 KB · Views: 106

Users who are viewing this thread

Top Bottom