two records to represent one relationship

[JohnLute]

I use tblProfilesAssociations.sfrmProfilesAssociations.cbProfilesAssociations to record relating profiles. cbProfilesAssociations is bound to tblProfiles.txtProfileID. It works like this: when a txtProfileID is selected, a record in tblProfilesAssociations is created. This works fine. However, it's only one record and I need it to be recorded twice

sfrmProfilesAssociations is used in many forms. Therefore, if a record is created in one form, it also needs to be created so that when the related form is opened the record will appear in sfrmProfilesAssociation as well

To help clarify - here's the course of database events
-Open frmFinishedGoods
-Create record 12345
-Create record in sfrmProfilesAssociations 205055 (corrugated)
-Open frmPKCorrugated
-Retrieve record 205055
-Examine sfrmProfilesAssociations. No record of being associated with 12345

I hope that more clearly illustrates. I suppose what I need is some AfterUpdate event as well as a Delete event because I'll also want to delete the related record should one of them require deleting

BIG thanks in advance!

 

[Jeff Boyce]

John

The use of the "tbl" prefix is usually reserved for a table. You have
posted in the tablesdbdesign newsgroup.

Your description starts off suggesting that you working with a form. By
your reference, it would seem that you are trying to use a subform.

There is rarely a need to create "blank" records in Access tables. You
mention:

sfrmProfilesAssociations is used in many forms. Therefore, if a record is

created in one form, it also needs to be created so that when the related
form is opened the record will appear in sfrmProfilesAssociation as well.

If both (?all three?) of your forms/subforms refer to the table (or query)
that includes the newly-added value, they will all display it -- it isn't
necessary to add it more than once.

To help clarify - here's the course of database events:
-Open frmFinishedGoods.
-Create record 12345.
-Create record in sfrmProfilesAssociations 205055 (corrugated).
-Open frmPKCorrugated.
-Retrieve record 205055.
-Examine sfrmProfilesAssociations. No record of being associated with 12345.

I hope that more clearly illustrates. I suppose what I need is some

AfterUpdate event as well as a Delete event because I'll also want to delete
the related record should one of them require deleting.
While you have provided considerable description of 'how' you are trying to
do something, I'm not very clear on what you want (other than two copies of
the same record). Perhaps if you describe the underlying business need,
rather than the specific technical approach...?

 

[JohnLute]

Thanks, Jeff

You inquired
While you have provided considerable description of 'how' you are trying t
do something, I'm not very clear on what you want (other than two copies o
the same record). Perhaps if you describe the underlying business need
rather than the specific technical approach...

What I want is sfrmProfilesAssociations to show relating profiles when profiles and their unique forms are opened. This is a rather odd design so I'll try to explain

tblProfiles houses basic profile info for many TYPES of profiles (or records). Types are CG, BG, FG, FM, etc. All of these types have the potential to relate to one another. tblProfilesAssociations and sfrmProfilesAssociations were designed to house and display those relations

Each profile type also has a unique form. I use frmProfiles as a template and simply rename it accordingly for each type (frmPKCorrugated - for CG, frmPKBags - for BG, etc.) sfrmProfilesAssociations is also part of the template. In short, the forms are used as a means of data entry and data review. For example, opening up frmPKCorrugated to a certain record will show all of it's associated profiles as well as permit more to be selected. Furthermore, it's setup so that the selections can be double-clicked to open their specific forms

Ultimately, I need to be able to make a selection in sfrmProfilesAssociations and have it update itself to show this relation in it's related profile. Also, it would need to delete it's related profile should a selection be deleted

It sounds a little nuts but this is the function I'm looking for

Thanks for your help

----- Jeff Boyce wrote: ----

Joh

The use of the "tbl" prefix is usually reserved for a table. You hav
posted in the tablesdbdesign newsgroup

Your description starts off suggesting that you working with a form. B
your reference, it would seem that you are trying to use a subform

There is rarely a need to create "blank" records in Access tables. Yo
mention

sfrmProfilesAssociations is used in many forms. Therefore, if a record i

created in one form, it also needs to be created so that when the relate
form is opened the record will appear in sfrmProfilesAssociation as well

If both (?all three?) of your forms/subforms refer to the table (or query
that includes the newly-added value, they will all display it -- it isn'
necessary to add it more than once

-Open frmFinishedGoods
-Create record 12345
-Create record in sfrmProfilesAssociations 205055 (corrugated)
-Open frmPKCorrugated
-Retrieve record 205055
-Examine sfrmProfilesAssociations. No record of being associated wit 12345

AfterUpdate event as well as a Delete event because I'll also want to delet
the related record should one of them require deleting


While you have provided considerable description of 'how' you are trying t
do something, I'm not very clear on what you want (other than two copies o
the same record). Perhaps if you describe the underlying business need
rather than the specific technical approach...

 

[Jeff Boyce]

John

Again, your description covers 'how' (using sfrmProfilesAssociations, ...),
not 'what'.

If you turned off your PC and tried to describe the underlying business need
to someone, say, your mother, what terms would you use?

The problem I'm having (and may be only me) is that I cannot visualize what
your underlying data looks like. Most Access development starts with data
and proceeds to how it is visualized/displayed (i.e., forms and reports).

It would help me understand what you are trying to do if I better understand
why...

 

[Lynn Trapp]

John,
Pardon me for jumping in here but it seems that Jeff and I are both having
the same problem understanding WHAT you are trying to do. For one thing,
what is a profile? What are you trying to store in your database? What real
world entities are you referring to when you want to store these "profiles"?
It looks to me like you are using some kind of business language that you,
and your company, may be very familiar with but that doesn't make sense to
the outside world.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Why?
Because - at the form level - we need to open a particular profile's form and:
1. See all of it's related profiles.
2. Select/delete related profiles.
3. Navigate to related profiles.
4. Upon selecting new related profiles also have a record generated for

the related profile (two records for one relationship).

 

[JohnLute]

What I'm trying to do is listed below in 1-5

tblProfiles is the heart and soul of my database. This houses "profile" info for many TYPES or entities as you've called them. These types are CG (corrugated), BG (bags), ED (egg & dairy), FA (formulas), FG (finished goods) etc., etc. Profile info consists of three required fields

txtProfileID (primary
txtVersio
txtTyp

Other profile data includes origin, revision, and discontinuation info. tblProfiles then relates to tables that house more specific data for each type

Profiles, therefore relate to one another. For example a finished good (FG) may relate to a CG, BG, ED, FA, etc., etc

I understand this is rather unusual

----- Lynn Trapp wrote: ----

John
Pardon me for jumping in here but it seems that Jeff and I are both havin
the same problem understanding WHAT you are trying to do. For one thing
what is a profile? What are you trying to store in your database? What rea
world entities are you referring to when you want to store these "profiles"
It looks to me like you are using some kind of business language that you
and your company, may be very familiar with but that doesn't make sense t
the outside world

--
Lynn Trap
MS Access MV
www.ltcomputerdesigns.co
Access Security: www.ltcomputerdesigns.com/Security.ht

Why
Because - at the form level - we need to open a particular profile's for and
1. See all of it's related profiles
2. Select/delete related profiles
3. Navigate to related profiles
4. Upon selecting new related profiles also have a record generated fo

the related profile (two records for one relationship)

 

[rpw]

Hi John

If I may butt-in here and offer a suggestion/example on how to describe the details of your business to the people that can help you. I'll take the info you've already posted and add my own 'descriptions' (artistic embellishments) to it - you should post the actual details

The business is Packaging. Package components are classified by Type. This table stores the various Types (aka Profiles

tblProfile
ProfileID (PK
ProfileCode 'CG, BG, FG, F
ProfileName 'corrugated, bag, etc..

Because one package can use more than one profile, the first profile can be related to any one or more of all of the other profiles, here is the junction table to store the relationships between profiles

tblProfileAssociatio
ProfileID
ProfileID
Reason 'explains the reason for the profile relationship..

I have one form for each of the different types because ....... each one is different (somehow). I then have a sub-form that lists all of the ProfileAssociations for the one Profile displayed on the main form

Anyway, this might be more along the lines of what Jeff was asking for - the table structure plus a description to explain the table so that he might understand the data structure better

hope this helps you to get hel

rp

----- JohnLute wrote: ----

Why
Because - at the form level - we need to open a particular profile's form and
1. See all of it's related profiles
2. Select/delete related profiles
3. Navigate to related profiles
4. Upon selecting new related profiles also have a record generated for the related profile (two records for one relationship)
5. By deleting a related profile also delete the related profile's record

I have 1-3 conquered. 4&5 are the issue

Hope this helps

----- Jeff Boyce wrote: ----

Joh

Again, your description covers 'how' (using sfrmProfilesAssociations, ...)
not 'what'

If you turned off your PC and tried to describe the underlying business nee
to someone, say, your mother, what terms would you use

The problem I'm having (and may be only me) is that I cannot visualize wha
your underlying data looks like. Most Access development starts with dat
and proceeds to how it is visualized/displayed (i.e., forms and reports)

It would help me understand what you are trying to do if I better understan
why..

 

[rpw]

sorry, i see you got it already - you can ignore this, I'll butt out now....

----- rpw wrote: ----

Hi John

If I may butt-in here and offer a suggestion/example on how to describe the details of your business to the people that can help you. I'll take the info you've already posted and add my own 'descriptions' (artistic embellishments) to it - you should post the actual details

The business is Packaging. Package components are classified by Type. This table stores the various Types (aka Profiles

tblProfile
ProfileID (PK
ProfileCode 'CG, BG, FG, F
ProfileName 'corrugated, bag, etc..

Because one package can use more than one profile, the first profile can be related to any one or more of all of the other profiles, here is the junction table to store the relationships between profiles

tblProfileAssociatio
ProfileID
ProfileID
Reason 'explains the reason for the profile relationship..

I have one form for each of the different types because ....... each one is different (somehow). I then have a sub-form that lists all of the ProfileAssociations for the one Profile displayed on the main form

Anyway, this might be more along the lines of what Jeff was asking for - the table structure plus a description to explain the table so that he might understand the data structure better

hope this helps you to get hel

rp

----- JohnLute wrote: ----

Why
Because - at the form level - we need to open a particular profile's form and
1. See all of it's related profiles
2. Select/delete related profiles
3. Navigate to related profiles
4. Upon selecting new related profiles also have a record generated for the related profile (two records for one relationship)
5. By deleting a related profile also delete the related profile's record

I have 1-3 conquered. 4&5 are the issue

Hope this helps

----- Jeff Boyce wrote: ----

Joh

Again, your description covers 'how' (using sfrmProfilesAssociations, ...)
not 'what'

If you turned off your PC and tried to describe the underlying business nee
to someone, say, your mother, what terms would you use

The problem I'm having (and may be only me) is that I cannot visualize wha
your underlying data looks like. Most Access development starts with dat
and proceeds to how it is visualized/displayed (i.e., forms and reports)

It would help me understand what you are trying to do if I better understan
why..

 

[JohnLute]

Thanks for "butting in" - I appreciate it and can use all the help I can get! You did a nice job clarifying. Actually, I'm in the food industry and my database includes and relates, ingredients, formulas, packaging, finished goods, suppliers, facilities, QA procedures, etc., etc

Crazy I know but it's actually coming together

----- rpw wrote: ----

sorry, i see you got it already - you can ignore this, I'll butt out now....

----- rpw wrote: ----

Hi John

If I may butt-in here and offer a suggestion/example on how to describe the details of your business to the people that can help you. I'll take the info you've already posted and add my own 'descriptions' (artistic embellishments) to it - you should post the actual details

The business is Packaging. Package components are classified by Type. This table stores the various Types (aka Profiles

tblProfile
ProfileID (PK
ProfileCode 'CG, BG, FG, F
ProfileName 'corrugated, bag, etc..

Because one package can use more than one profile, the first profile can be related to any one or more of all of the other profiles, here is the junction table to store the relationships between profiles

tblProfileAssociatio
ProfileID
ProfileID
Reason 'explains the reason for the profile relationship..

I have one form for each of the different types because ....... each one is different (somehow). I then have a sub-form that lists all of the ProfileAssociations for the one Profile displayed on the main form

Anyway, this might be more along the lines of what Jeff was asking for - the table structure plus a description to explain the table so that he might understand the data structure better

hope this helps you to get hel

rp

----- JohnLute wrote: ----

Why
Because - at the form level - we need to open a particular profile's form and
1. See all of it's related profiles
2. Select/delete related profiles
3. Navigate to related profiles
4. Upon selecting new related profiles also have a record generated for the related profile (two records for one relationship)
5. By deleting a related profile also delete the related profile's record

I have 1-3 conquered. 4&5 are the issue

Hope this helps

----- Jeff Boyce wrote: ----

Joh

Again, your description covers 'how' (using sfrmProfilesAssociations, ...)
not 'what'

If you turned off your PC and tried to describe the underlying business nee
to someone, say, your mother, what terms would you use

The problem I'm having (and may be only me) is that I cannot visualize wha
your underlying data looks like. Most Access development starts with dat
and proceeds to how it is visualized/displayed (i.e., forms and reports)

It would help me understand what you are trying to do if I better understan
why..

 

[Lynn Trapp]

John,
I don't think it's quite so unusual, because I work for a food (ice cream
...... hehehe) company myself. However, I'm still very unclear about what a
profile is. I thought perhaps rpw had gotten the idea when he referred to it
as Types, but now you suggest that a Profile can have many types. So I'm
lost again. Could you post your table structure as rpw suggested?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

What I'm trying to do is listed below in 1-5.

tblProfiles is the heart and soul of my database. This houses "profile"

info for many TYPES or entities as you've called them. These types are CG
(corrugated), BG (bags), ED (egg & dairy), FA (formulas), FG (finished
goods) etc., etc. Profile info consists of three required fields:

txtProfileID (primary)
txtVersion
txtType

Other profile data includes origin, revision, and discontinuation info.

tblProfiles then relates to tables that house more specific data for each
type.

Profiles, therefore relate to one another. For example a finished good

(FG) may relate to a CG, BG, ED, FA, etc., etc.

 

[rpw]

Wow, it sounds like you are tackling a very large, convoluted project - good for you! You've come to the right place for help. These ladies and gentlemen have helped me on several smaller problems.

Because I have more experience at asking for help than providing it, I'm going to avoid offering any solutions. But I'd also like to say that as I read this thread I'm confused by a couple of bits of information you've posted, and I'm going to guess that there may be some problems with table design. From what I've read here so far, it seems that only by seeing all of the tables (that relate to this one problem you are trying to solve) will anyone be able to help you.

So then maybe you do want to post your table structure (in the format I suggested) for Lynn Trapp, Jeff Boyce, and/or any other expert to look at. (maybe don't reply to this post, but to Lynn's with the info

In your description, you may also want to include the relationship between tables (just in case it's not obvious). For example

tblIngredient
IngrdID (PK
Produc
Descriptio
(other fields, etc....

Ingredients have a 1:M (one-to-many) to formula

tblFormul
FormulaID (PK)
IngrdID (FK
FormulaNam
(other fields, etc....

Again, I certainly don't wish to offend you by suggesting that there may be a design flaw. But I'm confused with your tblProfiles, and maybe by expressing my confusion you will see the need for providing some additional information. TblProfiles are profiles of what exactly? In there you want to relate Finished Goods to Corrugated, Corrugated to Bags, Eggs and Dairy to Finished Goods, etc. And your description leaves open the possibility that Finished Goods might even be related to Finished Goods. But I don't see any foreign keys listed in your description (like there is in the above example) so how are profiles to be related to one another

Another comment, in your reply to me you have various topics listed. On the surface, they sound like they are all well thought out and well defined subjects. Finished Goods sounds like it would be the logical junction table for grouping together ingredients, formulas, and packaging. But then your description of tblProfiles also seems to be a junction table for the same (and more) so, I must ask: what is the tblProfile REALLY for? As I scan through this thread for more info, your Profile table makes less and less sense to me. It seems as though each profile is a different rendition of a TYPE - So a corrugated might have many different versions, a formula might have many different versions, a finished good might have many different versions, and so on. But doesn't that really mean a new record in the appropriate table

(Continuing on my rant, sorry.) I'm also confused about your step number 4: "Upon selecting new related profiles also have a record generated for the related profile (two records for one relationship)."

I would assume that this means that when you are in the form that displays the Profiles and you create a new profile record, if there happens to be a new TYPE that is entered, have the record for the TYPE created for you?

Ok, new profile being created.... Type in version..."v123456-ABC"..... Type in Type...."Chicken".... Computer responds...."Hey! there ain't no chicken in the list, let me add that record! Boom - Done!"

But where is it supposed to be writing this record to - the formula table, the egg and dairy table, where

This whole number 4 problem seems like it should be handled by two combo boxes and some NotIinList events, but it doesn't sound like the table structure is there to allow it to happen. Of course, no one here can know for sure unless you post the info...

Hope this helps you to get the help you need...

rp




----- JohnLute wrote: ----

Thanks for "butting in" - I appreciate it and can use all the help I can get! You did a nice job clarifying. Actually, I'm in the food industry and my database includes and relates, ingredients, formulas, packaging, finished goods, suppliers, facilities, QA procedures, etc., etc

Crazy I know but it's actually coming together!

----- rpw wrote: -----

sorry, i see you got it already - you can ignore this, I'll butt out now.....

----- rpw wrote: -----

Hi John,

If I may butt-in here and offer a suggestion/example on how to describe the details of your business to the people that can help you. I'll take the info you've already posted and add my own 'descriptions' (artistic embellishments) to it - you should post the actual details.

The business is Packaging. Package components are classified by Type. This table stores the various Types (aka Profiles)

tblProfiles
ProfileID (PK)
ProfileCode 'CG, BG, FG, FM
ProfileName 'corrugated, bag, etc...

Because one package can use more than one profile, the first profile can be related to any one or more of all of the other profiles, here is the junction table to store the relationships between profiles.

tblProfileAssociation
ProfileID1
ProfileID2
Reason 'explains the reason for the profile relationship...

I have one form for each of the different types because ....... each one is different (somehow). I then have a sub-form that lists all of the ProfileAssociations for the one Profile displayed on the main form.

Anyway, this might be more along the lines of what Jeff was asking for - the table structure plus a description to explain the table so that he might understand the data structure better.

hope this helps you to get help

rpw

----- JohnLute wrote: -----

Why?
Because - at the form level - we need to open a particular profile's form and:
1. See all of it's related profiles.
2. Select/delete related profiles.
3. Navigate to related profiles.
4. Upon selecting new related profiles also have a record generated for the related profile (two records for one relationship).
5. By deleting a related profile also delete the related profile's record.

I have 1-3 conquered. 4&5 are the issue.

Hope this helps!

----- Jeff Boyce wrote: -----

John

Again, your description covers 'how' (using sfrmProfilesAssociations, ...),
not 'what'.

If you turned off your PC and tried to describe the underlying business need
to someone, say, your mother, what terms would you use?

The problem I'm having (and may be only me) is that I cannot visualize what
your underlying data looks like. Most Access development starts with data
and proceeds to how it is visualized/displayed (i.e., forms and reports).

It would help me understand what you are trying to do if I better understand
why...

 

[Jeff Boyce]

Thanks to rpw and Lynn while I was away at my "day job".

John

My sense is still that I don't have a clear picture of how you've structured
your data, let alone what the underlying "entities" are that you are
attempting to model.

How you get Access to do something depends on what you've told it about your
data.

How you get folks in the 'groups to offer meaningful suggestions for "how"
requires that they have a vision of what you know all too well.

The readers in the 'groups are more than happy to share their ideas and
experiences, but I think you'll get more pointed assistance if you can
expand your description beyond any specific use you've already made of
Access.

 

[JohnLute]

I'm not sure what specific piece of info is requested. Please allow me to recap everything here. With any luck, the info you're looking for will present itself

First off, I'm using forms as a means of entering data and navigating. The data I'm entering is specification info for many types such as CG (corrugated), FG (finished goods), CP (cups), FA (formulas), etc., etc. Users will simply access these forms for specification info and use combo boxes, etc. to navigate from one spec (form) to another

(p) = Primary Ke

tblProfiles.txtProfileID(p
This table stores profile info for many profile types. For example, profile 205055 is a CG (corrugated) type; profile 12345 is a FG (finished good) type; 400100 is a CP (cup) type; G500 is a FA (formula) type

Profiles relate to each other. For example, finished good 12345 is composed of 205055, 400100, and G500

tblProfilesAssociation
This table consists of profile relations (associations). It contains two fields that are a combined primary key
txtProfileAssociation
txtProfileI

sfrmProfilesAssociations.cbProfilesAssociation
This subform serves the purpose of associating profiles. It is used on all profile type forms. For example, FG, CG, FA, CP, etc., types all have their specific forms and all use this subform. cbProfilesAssociations is bound to tblProfilesAssociations.txtProfilesAssociations

This design works perfectly fine. I can go into any form type and make a selection from cbProfilesAssociations. However, the associated profile does NOT show the record. What I want to do is make a selection and have the same record automatically be created for the associated profile

For example, I'm in the FG form with the 12345 record opened. I select 205055 from cbProfilesAssociations. Super. No problem. Except when I open the CG form and the 205055 record - there's no record in sfrmProfilesAssociations of it being associated with 12345

Following this very simple scenario, I beleive an AfterUpdate code is required to tell Access to create a record of 12345 being associated with 205055

Hope this helps


----- Jeff Boyce wrote: ----

Thanks to rpw and Lynn while I was away at my "day job"

Joh

My sense is still that I don't have a clear picture of how you've structure
your data, let alone what the underlying "entities" are that you ar
attempting to model

How you get Access to do something depends on what you've told it about you
data

How you get folks in the 'groups to offer meaningful suggestions for "how
requires that they have a vision of what you know all too well

The readers in the 'groups are more than happy to share their ideas an
experiences, but I think you'll get more pointed assistance if you ca
expand your description beyond any specific use you've already made o
Access

 

[JohnLute]

Thanks so much for your interest. My project is rather unusual. It's not something you can use an Access wizard to construct! I understand your apprehension that something might be awry with my design. Allow me to lay it out here

As unusual as my design might be, it's actually quite simple

tblProfiles is the heart of the database. This stores "birth/revision/death" info for all types of profiles from finished goods to packaging, formulas, ingredients, QA procedures, etc., etc. It has a total of 14 fields. 3 of these fields are required
txtProfileID (primary key
txtVersio
txtType (CG, BG, FA, FG, ED, etc.

Stick with me a minute and you'll see how this allows for a one-to-many relationship. In other words, I have one profile but many profile types

I've built frmProfilesTemplate which sources tblProflies. I simply use this form as a template and copy/paste/rename it for the type I want. For example, frmPKCorrugated for type CG; frmFinishedGoods for type FG; frmFormulas for type FA; frmPKBags for BG; etc

Then I build subforms into these forms that source other tables. For example, CG (corrugated) has tblPKCGPhysicalAttributes, tblPKCGMaterialAttributes, tblPKCGPerformanceAttributes, tblPKFinishingAttributes, tblePKCGAdditionalAttributes. These tables are in a one-to-one relationship with tblProfiles.txtProfileID. Same design holds true for all the various types

There you have it

Am I nuts or what


----- rpw wrote: ----

Wow, it sounds like you are tackling a very large, convoluted project - good for you! You've come to the right place for help. These ladies and gentlemen have helped me on several smaller problems.

Because I have more experience at asking for help than providing it, I'm going to avoid offering any solutions. But I'd also like to say that as I read this thread I'm confused by a couple of bits of information you've posted, and I'm going to guess that there may be some problems with table design. From what I've read here so far, it seems that only by seeing all of the tables (that relate to this one problem you are trying to solve) will anyone be able to help you.

So then maybe you do want to post your table structure (in the format I suggested) for Lynn Trapp, Jeff Boyce, and/or any other expert to look at. (maybe don't reply to this post, but to Lynn's with the info

In your description, you may also want to include the relationship between tables (just in case it's not obvious). For example

tblIngredient
IngrdID (PK
Produc
Descriptio
(other fields, etc....

Ingredients have a 1:M (one-to-many) to formula

tblFormul
FormulaID (PK)
IngrdID (FK
FormulaNam
(other fields, etc....

Again, I certainly don't wish to offend you by suggesting that there may be a design flaw. But I'm confused with your tblProfiles, and maybe by expressing my confusion you will see the need for providing some additional information. TblProfiles are profiles of what exactly? In there you want to relate Finished Goods to Corrugated, Corrugated to Bags, Eggs and Dairy to Finished Goods, etc. And your description leaves open the possibility that Finished Goods might even be related to Finished Goods. But I don't see any foreign keys listed in your description (like there is in the above example) so how are profiles to be related to one another

Another comment, in your reply to me you have various topics listed. On the surface, they sound like they are all well thought out and well defined subjects. Finished Goods sounds like it would be the logical junction table for grouping together ingredients, formulas, and packaging. But then your description of tblProfiles also seems to be a junction table for the same (and more) so, I must ask: what is the tblProfile REALLY for? As I scan through this thread for more info, your Profile table makes less and less sense to me. It seems as though each profile is a different rendition of a TYPE - So a corrugated might have many different versions, a formula might have many different versions, a finished good might have many different versions, and so on. But doesn't that really mean a new record in the appropriate table

(Continuing on my rant, sorry.) I'm also confused about your step number 4: "Upon selecting new related profiles also have a record generated for the related profile (two records for one relationship)."

I would assume that this means that when you are in the form that displays the Profiles and you create a new profile record, if there happens to be a new TYPE that is entered, have the record for the TYPE created for you?

Ok, new profile being created.... Type in version..."v123456-ABC"..... Type in Type...."Chicken".... Computer responds...."Hey! there ain't no chicken in the list, let me add that record! Boom - Done!"

But where is it supposed to be writing this record to - the formula table, the egg and dairy table, where?

This whole number 4 problem seems like it should be handled by two combo boxes and some NotIinList events, but it doesn't sound like the table structure is there to allow it to happen. Of course, no one here can know for sure unless you post the info....

Hope this helps you to get the help you need....

rpw






----- JohnLute wrote: -----

Thanks for "butting in" - I appreciate it and can use all the help I can get! You did a nice job clarifying. Actually, I'm in the food industry and my database includes and relates, ingredients, formulas, packaging, finished goods, suppliers, facilities, QA procedures, etc., etc.

Crazy I know but it's actually coming together!

----- rpw wrote: -----

sorry, i see you got it already - you can ignore this, I'll butt out now.....

----- rpw wrote: -----

Hi John,

If I may butt-in here and offer a suggestion/example on how to describe the details of your business to the people that can help you. I'll take the info you've already posted and add my own 'descriptions' (artistic embellishments) to it - you should post the actual details.

The business is Packaging. Package components are classified by Type. This table stores the various Types (aka Profiles)

tblProfiles
ProfileID (PK)
ProfileCode 'CG, BG, FG, FM
ProfileName 'corrugated, bag, etc...

Because one package can use more than one profile, the first profile can be related to any one or more of all of the other profiles, here is the junction table to store the relationships between profiles.

tblProfileAssociation
ProfileID1
ProfileID2
Reason 'explains the reason for the profile relationship...

I have one form for each of the different types because ....... each one is different (somehow). I then have a sub-form that lists all of the ProfileAssociations for the one Profile displayed on the main form.

Anyway, this might be more along the lines of what Jeff was asking for - the table structure plus a description to explain the table so that he might understand the data structure better.

hope this helps you to get help

rpw

----- JohnLute wrote: -----

Why?
Because - at the form level - we need to open a particular profile's form and:
1. See all of it's related profiles.
2. Select/delete related profiles.
3. Navigate to related profiles.
4. Upon selecting new related profiles also have a record generated for the related profile (two records for one relationship).
5. By deleting a related profile also delete the related profile's record.

I have 1-3 conquered. 4&5 are the issue.

Hope this helps!

----- Jeff Boyce wrote: -----

John

Again, your description covers 'how' (using sfrmProfilesAssociations, ...),
not 'what'.

If you turned off your PC and tried to describe the underlying business need
to someone, say, your mother, what terms would you use?

The problem I'm having (and may be only me) is that I cannot visualize what
your underlying data looks like. Most Access development starts with data
and proceeds to how it is visualized/displayed (i.e., forms and reports).

It would help me understand what you are trying to do if I better understand
why...

 

[Lynn Trapp]

For example, I'm in the FG form with the 12345 record opened. I select
205055 from cbProfilesAssociations. Super. No problem. Except when I open
the CG form and the 205055 record - there's no record in
sfrmProfilesAssociations of it being associated with 12345.
John,
It appears to me like you are trying to create is something similar to a
Bill of Materials. Bills of Materials are Hierarchical and, therefore, a FG
would be a parent to many different raw materials, but you would never be
able to see the parent FG as a child record of the raw material. That looks
like what you are expecting. If 205055 is a child of 12345, then it's not
possible for 12345 to also be a child of 205055.

I might also mention that a Bill of Materials application is extremely
difficult to model and is not going to happen overnight. One thing you
should do is shut off your computer and get out several pieces of paper.
Draw out your concept for what you want to do on paper and see if it still
makes sense in a relational database world.

Let me give you a hint. Most Bills of Material applications use a self join
in a single table. Thus, you would have a primary key AND it's foreign key
would be in the same table. Thus, 12345 might be the BOM_ID for finished
good 12345 and it's Parent_ID would be null. BOM_ID 205055 would have 12345
as it's Parent_ID, and so forth. This can get really complicated because you
can end up with one Finished Good having another Finished Good, or multiple
Finished Goods, as it's child, and they can go down several levels.
Although, it would be very wise to limit the number of levels at some point.

 

[rpw]

Hi JohnLute,

I have gone through the entire thread and these are the tables that I have been able to gather from your postings.

tblProfile
txtProfileID
txtVersion
txtType

tblProfilesAssociations
txtProfileAssociations (multiple PK with txtProfileID)
txtProfileID (extablishes relationship with tblProfiles)

tblPKCGPhysicalAttriibutes

tblPKCGMaterialAttributes

tblPKCGPerfomanceAttributes

tblPKFinishingAttributes

tblPKCGAdditionalAttributes

You indicate that there are many more to your project, yet you do not list them. However, for the people here to understand the UNDERLYING DATA STRUCTURE, they really must see ALL of the pertinent tables.

For example, you mentioned at one point that there are various different subjects in your business: Ingredients, formulas, packaging, finished goods, suppliers, facilities, QA procedures, etc., etc. Of those subjects listed, suppliers, facilities, and QA procedures DO NOT relate to the problem you posted. Ingredients, formulas, packaging, and finished goods ARE related to the problem because they are all part of the “profile†in one way or another. So please post those table structures.

****************************************************************************

Your naming conventions are unusual – a form is prefixed with “tbl†rather than “frmâ€, and TABLE fields are prefixed with “txt†which is usually used for text fields on FORMS. Plus, you have indicated that you will duplicate a form for each individual TYPE of profile. (While it is not unusual to use a template from which to create new forms, duplicating a form for each record IS unusual.) These bits of information make me wonder if you are storing the data for TYPE in each individual form rather than in a table.

********************************************************************************

You stated: “Then I build subforms into these forms that source other tables. For example, CG (corrugated) has tblPKCGPhysicalAttributes, tblPKCGMaterialAttributes, tblPKCGPerformanceAttributes, tblPKFinishingAttributes, tblePKCGAdditionalAttributes. These tables are in a one-to-one relationship with tblProfiles.txtProfileID. Same design holds true for all the various types.â€

tblProfiles holds ALL profiles and the associated TYPE, yet you have decided that for each txtProfileID there is a one-to-one relationship with these various tables that describe CG (corrugated).

This type of structuring your data may be the root cause of your difficulties and the reason why you use terms like “unusualâ€, “crazyâ€, and “nuts†to describe the application.

rpw

 

[JohnLute]

Thanks, Lynn. Yes - this is something of a BOM. As you've noted, I do have a table with the primary key and foreign key self-joined. That would be

tblProfilesAssociation
txtProfileID (foreign and primary
txtProfilesAssociations (primary

These are the only two fields

I'm not sure that what you're describing fits my design. For example, I CAN make the parent ID of the child appear in the child record - I just have to physically go into the child's record and select it

I'm simply trying to have this happen automatically upon making/deleting a selection

Again, I can already do what I want but not automatically

Whew - I think we're starting to get on the same page

It seem to me this a matter of an AfterUpdate event...

----- Lynn Trapp wrote: ----

For example, I'm in the FG form with the 12345 record opened. I selec

205055 from cbProfilesAssociations. Super. No problem. Except when I ope
the CG form and the 205055 record - there's no record i
sfrmProfilesAssociations of it being associated with 12345


John
It appears to me like you are trying to create is something similar to
Bill of Materials. Bills of Materials are Hierarchical and, therefore, a F
would be a parent to many different raw materials, but you would never b
able to see the parent FG as a child record of the raw material. That look
like what you are expecting. If 205055 is a child of 12345, then it's no
possible for 12345 to also be a child of 205055

I might also mention that a Bill of Materials application is extremel
difficult to model and is not going to happen overnight. One thing yo
should do is shut off your computer and get out several pieces of paper
Draw out your concept for what you want to do on paper and see if it stil
makes sense in a relational database world

Let me give you a hint. Most Bills of Material applications use a self joi
in a single table. Thus, you would have a primary key AND it's foreign ke
would be in the same table. Thus, 12345 might be the BOM_ID for finishe
good 12345 and it's Parent_ID would be null. BOM_ID 205055 would have 1234
as it's Parent_ID, and so forth. This can get really complicated because yo
can end up with one Finished Good having another Finished Good, or multipl
Finished Goods, as it's child, and they can go down several levels
Although, it would be very wise to limit the number of levels at some point

 

[Lynn Trapp]

John,
I think we may be getting the right track now. However, I think to do what
you want, if it turns out to be possible, you will need to set the value for
the field that you want in the BeforeUpdate event. If you can see the
parent_id when the form is open, then you should be able to use the
BeforeUpdate event to validate that and set the value then. Use the
BeforeUpdate event because you can do your validations and cancel if
something goes wrong.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thanks, Lynn. Yes - this is something of a BOM. As you've noted, I do have

a table with the primary key and foreign key self-joined. That would be:

tblProfilesAssociations
txtProfileID (foreign and primary)
txtProfilesAssociations (primary)

These are the only two fields.

I'm not sure that what you're describing fits my design. For example, I

CAN make the parent ID of the child appear in the child record - I just have
to physically go into the child's record and select it.

 

[JohnLute]

YIKES

Honestly, I really thought my first couple posts were clear and that my problem was a simple matter of an AfterUpdate event. Somehow this has gotten strung out way beyond it's intentions

My naming conventions are entirely proper - it's my typing that's not. I mistakenly used tbl for frm

"You indicate that there are many more to your project, yet you do not list them. However, for the people here to understand the UNDERLYING DATA STRUCTURE, they really must see ALL of the pertinent tables.

I have listed these more than once and you've even listed them below
tblProfil
txtProfileI
txtVersio
txtTyp

tblProfilesAssociation
txtProfileAssociations (multiple PK with txtProfileID
txtProfileID (extablishes relationship with tblProfiles

I just don't know how more clearly I can communicate this. The two tables above are the pertinent tables to the problem. I appreciate your help but if everyone could please just stick to these two table I think we can tackle this

With this in mind, here is my original post
I use tblProfilesAssociations.sfrmProfilesAssociations.cbProfilesAssociations to record relating profiles. cbProfilesAssociations is bound to tblProfiles.txtProfileID. It works like this: when a txtProfileID is selected, a record in tblProfilesAssociations is created. This works fine. However, it's only one record and I need it to be recorded twice.

sfrmProfilesAssociations is used in many forms. Therefore, if a record is created in one form, it also needs to be created so that when the related form is opened the record will appear in sfrmProfilesAssociation as well.

To help clarify - here's the course of database events:
-Open frmFinishedGoods.
-Create record 12345.
-Create record in sfrmProfilesAssociations 205055 (corrugated).
-Open frmPKCorrugated.
-Retrieve record 205055.
-Examine sfrmProfilesAssociations. No record of being associated with 12345.

I hope that more clearly illustrates. I suppose what I need is some AfterUpdate event as well as a Delete event because I'll also want to delete the related record should one of them require deleting


----- rpw wrote: ----

Hi JohnLute

I have gone through the entire thread and these are the tables that I have been able to gather from your postings

tblProfil
txtProfileI
txtVersio
txtTyp

tblProfilesAssociation
txtProfileAssociations (multiple PK with txtProfileID
txtProfileID (extablishes relationship with tblProfiles

tblPKCGPhysicalAttriibute

tblPKCGMaterialAttribute

tblPKCGPerfomanceAttribute

tblPKFinishingAttribute

tblPKCGAdditionalAttribute

You indicate that there are many more to your project, yet you do not list them. However, for the people here to understand the UNDERLYING DATA STRUCTURE, they really must see ALL of the pertinent tables.

For example, you mentioned at one point that there are various different subjects in your business: Ingredients, formulas, packaging, finished goods, suppliers, facilities, QA procedures, etc., etc. Of those subjects listed, suppliers, facilities, and QA procedures DO NOT relate to the problem you posted. Ingredients, formulas, packaging, and finished goods ARE related to the problem because they are all part of the “profile†in one way or another. So please post those table structures

***************************************************************************

Your naming conventions are unusual – a form is prefixed with “tbl†rather than “frmâ€, and TABLE fields are prefixed with “txt†which is usually used for text fields on FORMS. Plus, you have indicated that you will duplicate a form for each individual TYPE of profile. (While it is not unusual to use a template from which to create new forms, duplicating a form for each record IS unusual.) These bits of information make me wonder if you are storing the data for TYPE in each individual form rather than in a table

********************************************************************************

You stated: “Then I build subforms into these forms that source other tables. For example, CG (corrugated) has tblPKCGPhysicalAttributes, tblPKCGMaterialAttributes, tblPKCGPerformanceAttributes, tblPKFinishingAttributes, tblePKCGAdditionalAttributes. These tables are in a one-to-one relationship with tblProfiles.txtProfileID. Same design holds true for all the various types.â€

tblProfiles holds ALL profiles and the associated TYPE, yet you have decided that for each txtProfileID there is a one-to-one relationship with these various tables that describe CG (corrugated).

This type of structuring your data may be the root cause of your difficulties and the reason why you use terms like “unusualâ€, “crazyâ€, and “nuts†to describe the application.

rpw

 

[JohnLute]

Thanks, Lynn. I think we're getting closer. However, what I'm looking for is something to happen AFTER I make a selection. For example, if I select 205055 as being associated with 12345 then I need the record of 205055 being associated with 12345 to be created. It seems to me a simple matter of having a record duplicating itself in reverse. However, I'm a complete novice when it comes to building Before/AfterUpdates. This example looks just like this in tblProfilesAssociations:

txtProfilesAssociations | txtProfileID
205055 | 12345
12345 | 205055



----- Lynn Trapp wrote: -----

John,
I think we may be getting the right track now. However, I think to do what
you want, if it turns out to be possible, you will need to set the value for
the field that you want in the BeforeUpdate event. If you can see the
parent_id when the form is open, then you should be able to use the
BeforeUpdate event to validate that and set the value then. Use the
BeforeUpdate event because you can do your validations and cancel if
something goes wrong.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thanks, Lynn. Yes - this is something of a BOM. As you've noted, I do have

a table with the primary key and foreign key self-joined. That would be:

txtProfileID (foreign and primary)
txtProfilesAssociations (primary)

CAN make the parent ID of the child appear in the child record - I just have
to physically go into the child's record and select it.