Solved Tables and multivalued fields (1 Viewer)

[Teri Bridges]

Okay Let me work on this. Again thanks for the guidance.

Ok, I have been working on this. The issue I am having is getting the data I want. I have created all the forms and am linking the master and child fields.
The primary key is an auto-generated number which means nothing to the user. The catalog ID number is what they use i.e., QTC-101.
If I link the form by Catalog ID the form is not continuous, If I link by Primary Key it is, but then the Catalog ID number does not automatically come over.

Any suggestions?

 

[Teri Bridges]

Ok, I have been working on this. The issue I am having is getting the data I want. I have created all the forms and am linking the master and child fields.
The primary key is an auto-generated number which means nothing to the user. The catalog ID number is what they use i.e., QTC-101.
If I link the form by Catalog ID the form is not continuous, If I link by Primary Key it is, but then the Catalog ID number does not automatically come over.

Any suggestions?

OMG, I got it working. I had selected the linking fields in the wrong order. Sorry

 

[Pat Hartman]

Several of your relationships in the earlier picture are incorrect. Relationships are ALWAYS - the PK of the 1-side to a data field (may be part of a multi-field PK) on the many-side. Your relationship do not always point to the PK of the 1-side. Please post the current view of the database so we can check to see if that is now fixed.

There is no reason to ever have an autonumber in a table UNLESS it is to be used as the PK. If you have keys that are not autonumbers, that is not wrong, technically but you should NEVER do that if there is even the remotest possibility of the value of the PK changing.

 

[Teri Bridges]

Several of your relationships in the earlier picture are incorrect. Relationships are ALWAYS - the PK of the 1-side to a data field (may be part of a multi-field PK) on the many-side. Your relationship do not always point to the PK of the 1-side. Please post the current view of the database so we can check to see if that is now fixed.

There is no reason to ever have an autonumber in a table UNLESS it is to be used as the PK. If you have keys that are not autonumbers, that is not wrong, technically but you should NEVER do that if there is even the remotest possibility of the value of the PK changing.

Okay, here are my changes. Thank you Pat for looking.

 

[Pat Hartman]

ChangeLog_tbl to Course_tbl_1 is wrong
Course_tbl to Events_tbl is wrong

 

[Teri Bridges]

ChangeLog_tbl to Course_tbl_1 is wrong
Course_tbl to Events_tbl is wrong

I removed the removed the relationships and removed the CourseID from the events table. Thank you for checking.

 

[gemma-the-husky]

Multivalue fields are achieved by access managing the nuts and bolts of a many to many relationship behind the scenes. It's not hard to do manually, and you have more control if you do it yourself. Also, it's hard to upsize an access table using a MVF to a different database such as SQL Server, so you can get locked in to Access.

I've never actually used one, so I'm not sure if that still holds.

 

[Pat Hartman]

None of the abomination data types are upsizable

 

[GPGeorge]

Multivalue fields are achieved by access managing the nuts and bolts of a many to many relationship behind the scenes. It's not hard to do manually, and you have more control if you do it yourself. Also, it's hard to upsize an access table using a MVF to a different database such as SQL Server, so you can get locked in to Access.

I've never actually used one, so I'm not sure if that still holds.

Also, it's hard impossible to upsize an access table using a MVF to a different database such as SQL Server,

Fixed it for you.

 

[gemma-the-husky]

Also, it's hard impossible to upsize an access table using a MVF to a different database such as SQL Server,

Fixed it for you.

Thanks George. I wasn't sure if things had changed, and I don't like to assume.

 

[GPGeorge]

Thanks George. I wasn't sure if things had changed, and I don't like to assume.

I was probably being too cute for my own good.

Seriously, the more I see people struggling with these "interface elements embedded in tables", the less friendly I feel about them. People who truly understand them and how they work can do so safely, of course.

Unfortunately, the people more likely to try to use them also tend to be less familiar with the nature of the beast and end up misusing them. It's safer to avoid the trap, IMO.