Relating Fields and Combo/Lookup

[chappie2]

I am trying to understand two issues regarding relationships.

I have created a grants database with a number of tables; GrantSum as the "one" table and Grantee, Payments, Products, etc. as the "many". Each table has an autogenerated ID# as the primary key and an indexed field called Grant#. Initially I simply linked the tables with the Grant# field. Someone suggested that I ought to define the Grant# field in the child tables as a combo box with a lookup through the ID#s to the grantsum table.

The Row Source info looks like this: SELECT DISTINCTROW [tblGrantSum].[GrantSumID#], [tblGrantSum].[DLCDGrant#] FROM [tblGrantSum];

That seems to muddle things to me. An ID# shows up when I expect a Grant#. It just seems to add complexity. I guess the purpose is to assure that records created in the many table are linked to a GrantSum record - improve data entry. Your thoughts?

Secondly, I want to create one-to-one links between the GrantSum table and two different tracking tables, CTrack and FTrack. (Some grants use one process, some another.) I was able to get the one-to-one definitions when the links were made with the Grant# fields, but can't seem to do it through the combo box structure. Should I be able to? Is there a better approach?

Thanks.

Lloyd

 

[lespaul]

In your combo box you need to have the bound column set to the ID field and (i'm guessing here) the 'display'(?) column set to the Grant# field. That will help your first issue.

Not sure what you're asking for in the second issue.

Leslie

 

[chappie2]

Where is a "display" option set - I don't find it in viewing the field in table view? Is this related to the fact that the field in the table is defined as a combo box while I placed a text box on the form? This still seems more cumbersome than valuable in an application that will have a couple of hundred records a year.

The second part related to defining one-to-one relationships. I was able to get them to show on the links in the relationships view before I moved to combo boxes, but cannot since the move. Should I be able to select a relationship and get it to report a one-to-one relationship when I am using this combo box structure to link the tables?

Thanks. Lloyd

 

[lespaul]

I'm sorry I thought your combo box was on a form. I would NEVER allow users into the tables, even with combo boxes. ALWAYS use forms to interface with data.

And it's not 'display' which is why I said I was guessing there and added a ? after it.

Again, sorry I've never heard of using a combo box structure to link tables.

Leslie

 

[chappie2]

Don't know if this update will be seen - I have just returned from a canoeing trip.

I think I have caused the confusion by my lack of familiarity and precision with Access terms. I do not intend to allow users to access tables.

The issue is the definiton of the Grant# field in "secondary" tables. Initially, I simply created a Grant# field in each table and then linked the tables on the field.

The suggestion was that I use the "Display Control" option under Lookup to specify a combo box and thereby tie the Grant# of the secondary table to the primary table's autogenerated ID#. This seems to "work", but the Grant# field now is defined as a Long Integar type in the secondary table. It is a text field in the parent table.

It is this step that seems cumbersome and unnecessary, since the Grant# is our primary reference to grant records, and they are used all of the time.

It would seem much more direct to simply link Grant# in the two tables as I had initially planned. What are the limitations of making this type of link?

Have I made anything clear or is it as muddy as the river was a few days ago?

Thanks,

Lloyd

 

[lespaul]

Hope you had fun canoeing! We took a weekend trip to Denver and had a GREAT time!

Ok, if in your MAIN Grant table you have:

tblGrants
GrantID (PK - Autonumber)
other information


tblGrantOtherTables
GrandID (FK to tblGrants - must be a long integer)

then the relationships should be able to be set between those two fields.

But then you say GrantID is a text field?

If you want to relate these tables together then you need to have either an Autonumber in the main table and long integers in the others to link. If you want to have a text field as the GrantID then it will need to be text fields in all the other tables.

Does that help?

Leslie

 

[chappie2]

Thanks Leslie. I think we are getting close. Here is the structure I started with.

tblGrantSum
GrantID (PK - Autonumber)
Grant# (text)

tblPayments
PaymentID (PK - Autonumber)
Grant# (text)

I then simply linked the tables on Grant#.

The suggestion was that I redefine the Grant# field in tblPayments through the Lookup tab, combo box Display Control and Record Source to actually tie it to Grant ID in tbl GrantSum through Grant#. This results in the Grant# field in tblPayments being a Long Integer type (like an Autonumber).

It seems to me that my initial design was much cleaner and will be much more understandable than the suggestion.

Lloyd

PS: It was a good canoe trip, but way too much water for relative rookies and we bailed out early!!

 

[lespaul]

Lloyd, Sorry you had to bail early, that's a drag! We went on the "white water rafting" ride at Six Flags in Denver and were SOAKED, now my kids want to go on a REAL white water rafting trip!

Anyway, I would set the tables up like this:

tblGrantSum
GrantID (PK - Autonumber)
Grant#

tblPayments
PaymentID (PK - Autonumber)
GrantID (FK - Long Integer)

and now you can link on GrantID!



Leslie

 

[chappie2]

Thanks Leslie.

Your suggestion makes sense. But the GrantID is an autogenerated number that will not be routinely used or, I fear, easily remembered. I think users will struggle with getting the correct number in the records in the child tables.

That is why using the Grant# (which reflects a grant type, year and number) made more sense to me.

This sort of leads me back to the suggestion I was given originally: Create a lookup (in the field definition of child table) between GrantId and Grant# to 1) make the link through the PK of the parent table to the child table and 2) make it look like the Grant# users are familiar with.

I guess I need to ponder this a bit more and make a decision. Thanks for all of your help.

Lloyd

 

[lespaul]

It doesn't need to be anything that the users see at all!!! It's a number, it allows you to access the GRANT# easily.

You have a form, the user enters the GRANT#

you run a query:

SELECT * FROM tblGrantSum INNER JOIN tblPayments on tblPayments.GrantID = TblGrantSum.GrantID WHERE GRANT# = WHATEVER THE USER ENTERED



Leslie

 

[chappie2]

Thanks again Leslie. I guess I need some help with database theory - and rudimentary theory at that.

How is the value of GrantID in the Payments table populated?

A user will be required to select or fill-in a value for Grant# in order for Access to identify the correct GrantID and use that to get data from records in both tables.

Why not just place Grant# in the Payments table and use it to make the link to GrantSum?

Lloyd

 

[lespaul]

If you want to use the Grant# as the PK, you can do that too, but then you don't need the GrantID (autonumber).

Leslie