A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Link Items in Same Table



 
 
Thread Tools Display Modes
  #1  
Old March 11th, 2010, 10:23 AM posted to microsoft.public.access.gettingstarted
TheScullster
external usenet poster
 
Posts: 53
Default Link Items in Same Table

Ok so I have a table which contains contract numbers and associated data
records.
How do I create a table and populate it to store link information between
selected records in the above table?


Table structure

Ac-id - PK autonumber Account ID Field
Ct-code number Conract Number
Ac-title text Contract description
Ca-code text customer address


So what I want to do is record any commonality between 2 or more records in
the above table.
For instance it may be that 2 or more contracts occur on the same site.
So I need a mechanism to store the contract "links" and also a memo field to
provide information on the way in which the contracts are connected.


Any advice/starter info appreciated

Phil




  #2  
Old March 11th, 2010, 02:21 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Link Items in Same Table

It sounds as if you need at least one additional table.

LinkedContracts
AccountID number type long
AssociatedAccountID number type long
Association text (or memo)

This table would have the two accounts and the reason they are associated.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

TheScullster wrote:
Ok so I have a table which contains contract numbers and associated data
records.
How do I create a table and populate it to store link information between
selected records in the above table?


Table structure

Ac-id - PK autonumber Account ID Field
Ct-code number Conract Number
Ac-title text Contract description
Ca-code text customer address


So what I want to do is record any commonality between 2 or more records in
the above table.
For instance it may be that 2 or more contracts occur on the same site.
So I need a mechanism to store the contract "links" and also a memo field to
provide information on the way in which the contracts are connected.


Any advice/starter info appreciated

Phil




  #3  
Old March 11th, 2010, 02:55 PM posted to microsoft.public.access.gettingstarted
TheScullster
external usenet poster
 
Posts: 53
Default Link Items in Same Table


"John Spencer" wrote

It sounds as if you need at least one additional table.

LinkedContracts
AccountID number type long
AssociatedAccountID number type long
Association text (or memo)

This table would have the two accounts and the reason they are associated.

Thanks John
I'd kind of got this far (in my head) but I'm not sure how to "link" this
table to the existing one.
Is your AccountID field a foreign key linked to ac-id from the existing
table?
Also, is your AssociatedAccountID field a foreign key linked to a different
ac-id from the existing table?

Existing table

Table structure

Ac-id - PK autonumber Account ID Field
Ct-code number Conract Number
Ac-title text Contract description
Ca-code text customer address


Finally what would be the best mechanism to allow for user selection of the
associated records and populating the new table?

Thanks for your help

Phil


  #4  
Old March 13th, 2010, 02:05 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Link Items in Same Table

Sorry,
I lost this thread.

I keep pondering on this. I don't really like this structure but I am stuck
on coming up with a better structure.

This table is linked to the existing one on two fields. I would probably link
to the AccountID field in most cases. Then use a form plus subform structure
to enter the information.

When you need to extract information for reports, you would need to have two
references to the existing table in the query. You would link one reference
to AccountID and the other reference to the AssociatedAccountID.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

TheScullster wrote:
"John Spencer" wrote

It sounds as if you need at least one additional table.

LinkedContracts
AccountID number type long
AssociatedAccountID number type long
Association text (or memo)

This table would have the two accounts and the reason they are associated.

Thanks John
I'd kind of got this far (in my head) but I'm not sure how to "link" this
table to the existing one.
Is your AccountID field a foreign key linked to ac-id from the existing
table?
Also, is your AssociatedAccountID field a foreign key linked to a different
ac-id from the existing table?

Existing table

Table structure

Ac-id - PK autonumber Account ID Field
Ct-code number Conract Number
Ac-title text Contract description
Ca-code text customer address


Finally what would be the best mechanism to allow for user selection of the
associated records and populating the new table?

Thanks for your help

Phil


  #5  
Old March 16th, 2010, 06:02 PM posted to microsoft.public.access.gettingstarted
TheScullster
external usenet poster
 
Posts: 53
Default Link Items in Same Table


"John Spencer" wrote

Sorry,
I lost this thread.

I keep pondering on this. I don't really like this structure but I am
stuck on coming up with a better structure.

This table is linked to the existing one on two fields. I would probably
link to the AccountID field in most cases. Then use a form plus subform
structure to enter the information.

When you need to extract information for reports, you would need to have
two references to the existing table in the query. You would link one
reference to AccountID and the other reference to the AssociatedAccountID.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks John

I think I follow your instructions here.
One element that is not clear is the means of creating the record entries in
the new table.
Yes this probably sounds like the dumbest of basic questions, but so far my
understanding has been limited to data entry into a single table via a form.
What we are trying to achieve here is to create an entry in our new table
linking to a record in an existing table.
How is that best achieved?
I pondered a "create link" button with some code behind to capture the
appropriate contract record from the form you describe and then present the
user with a text box to add the memo.
How this is done is almost a mystery.

Thanks

Phil


  #6  
Old March 18th, 2010, 02:09 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Link Items in Same Table

Phil:

Inserting a row into the LinkedContracts table is quite easily achieved by
means of a subform based on this table. This subform would be embedded in a
form based on the Contracts table with the LinkMasterFields property set to
Ac-id and the LinkChildFields property set to AccountID. The subform would
contain two controls, a combo box bound to AccountID and a text box bound to
Association.

The combo box would be set up as follows:

RowSource: SELECT [Ac-id], [Ct-code] FROM [Contracts] ORDER BY [Ct-code];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

However, its important to understand that the relationship between the two
contracts linked in this way is directional, so the subform will only show
those contacts which have been linked to the current contact in the parent
form, not any contacts to which the current contact in the parent form has
been linked. Similarly if you create a report based on a query which joins
Contacts to LinkedContracts on AccountID and LinkedContracts to another
instance of Contracts on AssociatedAccountID the report will only show the
links in the one direction. You can get it to show links in both directions
by creating another query which joins Contacts to LinkedContracts on
AssociatedAccountID and LinkedContracts to another instance of Contracts on
AccountID, and then basing the report on a union query of these two queries.

This is fine if you are only interested in immediate links between contracts,
but it gets more complicated if you want to return all links down the line,
e.g. if Contract A links to Contract B which in turn links to Contracts D and
E. This is not too difficult if the number of steps is finite, but otherwise
it’s akin to the classic ‘bill of materials’ problem, which requires a more
elaborate solution. You’ll fund a demo of ways of handling this in the file
attached to my post at:

http://community.netscape.com/n/pfx/...apps&tid=23133


Ken Sheridan
Stafford, England

TheScullster wrote:
Sorry,
I lost this thread.

[quoted text clipped - 14 lines]
The Hilltop Institute
University of Maryland Baltimore County


Thanks John

I think I follow your instructions here.
One element that is not clear is the means of creating the record entries in
the new table.
Yes this probably sounds like the dumbest of basic questions, but so far my
understanding has been limited to data entry into a single table via a form.
What we are trying to achieve here is to create an entry in our new table
linking to a record in an existing table.
How is that best achieved?
I pondered a "create link" button with some code behind to capture the
appropriate contract record from the form you describe and then present the
user with a text box to add the memo.
How this is done is almost a mystery.

Thanks

Phil


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201003/1

  #7  
Old March 18th, 2010, 03:32 PM posted to microsoft.public.access.gettingstarted
TheScullster
external usenet poster
 
Posts: 53
Default Link Items in Same Table


"KenSheridan via AccessMonster.com" wrote

Inserting a row into the LinkedContracts table is quite easily achieved by
means of a subform based on this table. This subform would be embedded in
a
form based on the Contracts table with the LinkMasterFields property set
to
Ac-id and the LinkChildFields property set to AccountID. The subform
would
contain two controls, a combo box bound to AccountID and a text box bound
to
Association.

The combo box would be set up as follows:

RowSource: SELECT [Ac-id], [Ct-code] FROM [Contracts] ORDER BY
[Ct-code];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least
as
wide as the combo box.

However, its important to understand that the relationship between the two
contracts linked in this way is directional, so the subform will only show
those contacts which have been linked to the current contact in the parent
form, not any contacts to which the current contact in the parent form has
been linked. Similarly if you create a report based on a query which
joins
Contacts to LinkedContracts on AccountID and LinkedContracts to another
instance of Contracts on AssociatedAccountID the report will only show the
links in the one direction. You can get it to show links in both
directions
by creating another query which joins Contacts to LinkedContracts on
AssociatedAccountID and LinkedContracts to another instance of Contracts
on
AccountID, and then basing the report on a union query of these two
queries.

This is fine if you are only interested in immediate links between
contracts,
but it gets more complicated if you want to return all links down the
line,
e.g. if Contract A links to Contract B which in turn links to Contracts D
and
E. This is not too difficult if the number of steps is finite, but
otherwise
it's akin to the classic 'bill of materials' problem, which requires a
more
elaborate solution. You'll fund a demo of ways of handling this in the
file
attached to my post at:

http://community.netscape.com/n/pfx/...apps&tid=23133



Wow - thanks for such a comprehensive response Ken.
Also for pointing out the vagueries/intrecacies of the undertaking.
I believe that nested links will not be required, but bi-directional links
will. So my final solution will be based on your penultimate paragraph.
As I have been a while away from all this, I will start from the beginning
of your response and try to create the different options that you have
outlined.

Thanks again - your assistance is greatly appreciated

Phil


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.