Re-assigning links from subform to form

[Rolfee]

Hi. I am new to Access and have progressed quite nicely in building a
telemarketing database, but I have hit a hurdle that I cannot get over.
Database consists of 72 company records with multiple contacts per
company record. Total of 268 records.
I have built a subform within the main form so that I can view the
multiple contacts per company record. I (think) the link that Access
defaulted to for linking the subform is causing the problem, but I
cannot work out how to change it. Currently, I have a master form
showing all 268 records, and a subform showing only a single contact.
The link seems to be the unique company ID field (sequential 1-268),
but I think it should link to the company name.
What I am aiming for is to have the 72 company records shown in the
master form and the contacts per company record shown in the subform.
I hope that makes sense!
Can anyone tell me how to change the link, please?

 

[Guest]

Hi,

I can help with this one -
click on the subform so it has the subform control is selected, then press
the properties tab on the toolbar up top, them you will see LinkChildFields
and LinkMasterFields in the list of properties, click on the right edge ..
and get the same dialog as when you inserted the subform. Hard to remember,
but invaluable!

Lisa

 

[Rolfee]

Hi Lisa.

Thanks for your feedback. I did reply the same day, but it does not
appear to have been posted.

Based on your advice, I have now fixed the problem 50% in that the
contacts subform is now showing the multiple contacts per company.
However, the master form is still showing all 268 records - not just
the 72 company records. This means that I have to click through the
company record (e.g.) four times before I get to the next company
record.

Any idea what is missing?

Am I right in thinking that I can set master- and sub-forms from the
same table?

Thanks and regards,

Ed.

 

[John Vinson]

Based on your advice, I have now fixed the problem 50% in that the
contacts subform is now showing the multiple contacts per company.
However, the master form is still showing all 268 records - not just
the 72 company records. This means that I have to click through the
company record (e.g.) four times before I get to the next company
record.

Any idea what is missing?

Am I right in thinking that I can set master- and sub-forms from the
same table?

Typically, the mainform would be based on the "ONE" side table, and
the subform on the "MANY" side table. It sounds like your mainform is
based *on a query* joining the Company table to the Contacts table.
Don't! Just base the mainform on the company table, and the subform on
the contacts table.

John W. Vinson[MVP]

 

[Rolfee]

Thanks John.

So have I set it up wrong from the start, by having both company
records and contact records in the same table?

I did this, as I need to report all changes back in the same format as
it was provided, which was an Excel spreadsheet. I thought that keeping
the Access table and Excel spreadsheet records could only be
accomplished if all data was in the one table. Am I wrong here? And, if
so, how can I fix it without re-designing the whole table and form from
scratch (it took me ages!).

Refering back to your message, I realise that I have not approached
this with the 'ONE' and 'MANY' sides of a table in mind. Looks like
I've got some reading to do, but would appreciate it if you had a quick
answer to my last point regarding fixing it using currnet form and
changing some links to tables.

Thanks again,

Ed.

 

[John Vinson]

Thanks John.

So have I set it up wrong from the start, by having both company
records and contact records in the same table?

Yes.

Each Table should contain information about only one kind of Entity.

A company is an entity - a real-life thing with its own attributes. A
contact is a *different kind* of entity, with a different set of
attributes.

If you include the contact records in the same table as that contact's
company, you're forced to store all of the company information
repeatedly - wasting space, and (more important) risking data
anomalies. Suppose you change the CompanyName in one record, and not
in the other three?

If you're going to use a relational database... use it as a relational
database!

I did this, as I need to report all changes back in the same format as
it was provided, which was an Excel spreadsheet. I thought that keeping
the Access table and Excel spreadsheet records could only be
accomplished if all data was in the one table. Am I wrong here? And, if
so, how can I fix it without re-designing the whole table and form from
scratch (it took me ages!).

You are in fact wrong here, unfortunately. A relational database IS
NOT A SPREADSHEET, and does not work like a spreadsheet; a good
spreadsheet design may be a very bad relational table design and vice
versa.

It is NOT necessary to have all the data in one table in order to edit
it, display it, or even export it back to Excel. That's what queries
are for - you can have the *two* tables, and create a query joining
them (pulling Company attributes from the Company table fields, and
Contact attributes from the Contact table fields), and export this
Query into Excel.

Refering back to your message, I realise that I have not approached
this with the 'ONE' and 'MANY' sides of a table in mind.

Then you're not using Access as it was designed. That distinction is
*absolutely fundamental* to any productive use of Access, or any other
relational database program.

Looks like
I've got some reading to do, but would appreciate it if you had a quick
answer to my last point regarding fixing it using currnet form and
changing some links to tables.

You cannot fix your current form without fixing the tables. Sorry.

You may not have to change all that *much* - if you create the two
tables, one for each type of entity, you can run Append queries to
migrate the data from your spreadsheet into them, and probably change
only the Recordsource properties of the Forms to point to the
appropriate table. If you have only Company related fields on the
mainform, and only Contact related fields on the subform, it should be
pretty straightforward.

John W. Vinson[MVP]