Key Field Definition and Display

[chappie2]

Based on what I believe was good advice, I created my database with something I don't fully understand and it is now coming back to haunt me.

I have two tables: tblGrantSum and tblCTrack. The primary key of each is an autogenerated number. Each table also has a Grant# field, which is the link between the tables. (This should be a one to one link, but I have had difficulty getting Access to report it as one-to-one.)

When tblCTrack was created, the Grant# field was shown as a lookup Combo Box with the following Row source:

SELECT DISTINCTROW [tblGrantSum].[GrantSumID#], [tblGrantSum].[Grant#] FROM [tblGrantSum];

First can someone explain exactly what this statement says?

Secondly, how do I best display the value of tblCTrack.DLCDGrant# field in a form? It always seems to look for or show the GrantSumID# instead of the Grant# value. I would like a button in the GrantSum form to either open the correct record or ask if the user wants to create a new record.

I think all of this would be simple if I had not used the combo box structure above, but . . . that would have led to other complications.

Any help appreciated.

Lloyd

 

[TheAceMan1]

How are ya chappie2 . . . . .

[blue](This should be a one to one link, but I have had difficulty getting Access to report it as one-to-one.)[/blue]

Since what your after is dependant on [blue]Square One![/blue], table relationships, bear this in mind:

[blue]in order toinstantiate a one to one relationship, you have to link two primary keys with the same name & data type between two tables.[/blue]

Try that first, and then let us know what ya need!

See Ya! . . . . . .

 

[chappie2]

AceMan,

Thanks for the input. I can't change the primary key without doing serious damage to the application. For now, let's just say that the two tables are linked and that there will be only one CTrack record for each GrantSum record (without defining the link as a one-to-one relationship). That may not be recommended, but is how I think I need to move forward.

From that perspective, can you respond to the more detailed issues I raise?

Thanks,

Lloyd

 

[TheAceMan1]

[blue]SELECT DISTINCTROW [tblGrantSum].[GrantSumID#], [tblGrantSum].[Grant#] FROM [tblGrantSum];

First can someone explain exactly what this statement says?
[/blue]

This means, the lookup combo has two fields available for display, namely [blue]GrantSumID#[/blue] & [blue]Grant#[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Sorry chappie2 . . . hit submit by mistake . . . .

[blue]SELECT DISTINCTROW [tblGrantSum].[GrantSumID#], [tblGrantSum].[Grant#] FROM [tblGrantSum];

First can someone explain exactly what this statement says?
[/blue]

1) This means, the lookup combo has two fields available for display, namely GrantSumID# & Grant#.
2) DistinctRow means: Data based on entire duplicate records are omitted.
3) The Field/Fields displayed in the CB are dependant on the [blue]Column Count[/blue] & [blue]Column Widths[/blue] properties.
4) The first non-zero column in [blue]Column Widths[/blue] is the column entered into the textbox portion of the combobox.
5) The [blue]Bound Column[/blue] property determines which column data is returned when the combobox is referenced.

[blue] have two tables: tblGrantSum and tblCTrack. The primary key of each is an autogenerated number. Each table also has a [purple]Grant# field, which is the link between the tables[/purple].[/blue]

This type of relationship is called [purple]Indeterminate[/purple], and it signals Access has no Idea what to do with it. [purple]If this is not corrected your gonna have trouble all the way down the line[/purple].

[blue]Secondly, how do I best display the value of tblCTrack.DLCDGrant# field in a form?[/blue]

Until you get the table relationship right, not only is displaying tblCTrack.DLCDGrant# academic, but you'll may have problems here as well.

Now, you may have enough data thats stopping you from making any changes to the relationship, but if ya don't correct it now, wait till tomorrow!

What you need to do is post the fields in the table and explain what it is you want to do. Other than that, I'm not gonna help someone go down the wrong path . . . . .

See Ya! . . . . . .

 

[chappie2]

Thanks for pushing me AceMan. It may be painful but needs to be done! Let's go back to the beginning.

My application has three main tables. A GrantSum table for summary information about all grants, CTrack to track detailed information about competitive grants and FTrack to track similar information for formula grants.

I decided on this "division" because 1) I wanted all the summary information in one place (GrantSum) and 2) the processes we use on the two types of grants are very different, so I split them between competitive and formula. In this structure, each grant in the GrantSum table will have a single tracking record in either the CTrack or FTrack table, depending on which type of grant it is.

This would all work with our commonly used DLCDGrant# field as the primary key, but I only want to assign this grant number to awarded grants, not to all applications we receive. Therefore, I have gone to autonumbers on each of the three tables.

So my current structure is:

tblGrantSum; primary key GrantSumID#; key DLCDGrant#

tblCtrack; primary key CTrackID#; key DLCDGrant#

tblFtrack; primary key FTrackID#; key DLCDGrant#

The database currently shows the relationships between tblGrantSum and the tracking tables as one-to-many between GrantSumID# and DLCDGrant#.

(As I was writing this, I realized that if I use the tracking tables to track applications (as opposed to awarded grants), I will not have a DLCDGrant# to use and will need to link between the autogenerated keys - but how do I do that in a way that doesn't force users to link up to seemingly unrelated autonumbers?)

So, I can either find a better way to link the tables with the autogenerated keys or have a DLCDGrant# assigned to all new records and use it as the primary key for all three tables. Or . . . .

Thanks for any perspective you can offer.

Lloyd

 

[TheAceMan1]

OK chappie2 . . . . .

I have engagements that will take up my time tonight, and tomorrow, work is gonna be more that a full day. So I can't give the necessary credence until tomorrow evening.

In the meantime, if you could send me the DB with a few sample records as you have it now, it would save alot of uneccessary back & forth (see my profile).

Understand if ya can send it, any correspondance is to be carried on here, at Tek-Tips and deservedly so . . .

If anything should break . . . . you'll hear from me before then!

See Ya! . . . . . .