methods to display/update related data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On my Supplier Info Form I want the user to be able to select Equipment Types
and Manufacturers that they carry. I want to display what they currently
carry and allow the user to add, change or delete the equipment types and
manufacturers.

What methods do I use in order to accomplish this?

Tables:
Supplier Info
Manufacturer Info
Equipment Types

Junction Tables:
Supplier Detail w/SupplierID, EquipTypeID, ManID
Manufacturer Detail w/EquipTypeID, ManID

Example Data:
Equip type 1 is made by Man A
Equip type 2 is made by Man A and Man B
Equip type 3 is made by Man B

Supplier XX carries equip type 1 by Man A and equip type 2 by Man B
Supplier ZZ carries equip type 2 by Man A and equip type 2 & 3 by Man B
 
Kelly

Does your database have any historical data? If so, you probably don't want
the users actually deleting equipment types and manufacturers, since this
would "orphan" some of your historical data.

One approach that would appear to delete these (but not really) would be to
add a field to store a "No longer valid after date=" value. Then you'd
modify your queries to only include the "current" values in your comboboxes
(so users could only pick currenly-valid items). And if you modified the
queries you have feeding your forms to exclude the "old" values, you could
requery the form after "deleting" (but not really deleting) an item and that
item wouldn't show any more.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff~

Thanks for your response. I do not require any historical data. The main
purpose of the info is to match up Project Requirements (another table) with
Suppliers that meet the Equipment Types and Manufacturers called for in the
Project specifications.

You mention queries and forms. The problem I am having is I am not sure
what data I need to feed to which forms in order to meet my objective. My
main form is Supplier Info, I'm guessing I need to be using a sub-form(s) to
get the Equipment Type and Manufacturer info that I am looking for. But am I
populating those sub-form(s) with listboxes or comboboxes or cascading boxes
or what? Can you break it down at all for me to get me on the right path?
 
It sounds like you may have started with forms and are trying to get the
data to match up.

Access starts with the data, then uses queries to retrieve it, and forms
(and reports) to display it.

Start with the data... can you describe your data structure a bit more?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Oops! I looked back after I hit <Send>. What I really wasn't clear on is
what are your "objectives".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff~

Thank you so much for trying to help me with this, I've been spinning my
wheels for a week now and am about ready to chuck my laptop through my
window! I hope the following explanation gets at what you are asking.

The primary objective is to be able to match up Project Info (another table
which contains project specifications) with Suppliers that can meet those
specs and produce various reports. Following with the example data, I would
have Project XYZ which calls for a equip type 1 made by Man A, so Supplier XX
would be a match and receive an invitation to bid. Project Info has a
junction table with primary keys as: ProjectID, EquipTypeID, ManID. (I'll
need to set up the following scenario on my Project Info form as well.)

So what I was working toward was to have a tab on my Supplier Info form
where I can list the EquipTypes and ManIDs that each one carries (Supplier
Detail). I was initially thinking a list box to pull in all EquipTypes, make
a selection then a second multi-select list box shows all of the
Manufacturers that carry that EquipType. Once one or more of the listed
Manufacturers are selected those records would be added to the Supplier
Detail.

Problem #1 - I've gotten the cascading list box part but don't know how to
get my links correct so that it is updating the Supplier Detail Data.

Problem #2 - I need to display the current Supplier Detail that already
exists so that I can change it or add to it or delete from it.

I'm coming to the conclusion that the cascading list box thing may not be
the way to go but am at a total loss as to what the right way would be.
 
Sorry for the delay...

I'm still not grasping the underlying data structure... maybe it's just me
and someone else can offer ideas.

If you haven't resolved this, and are still game to proceed, I'd ask that
you provide more detailed description of your tables and relationships. For
example, if you were working with a rudimentary system to handle class
enrollment, it might have tables something like:

tblStudent
StudentID
FirstName
LastName
DOB

tblClass
ClassID
ClassTitle

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

and the relationships would be something like:

* One Student can be enrolled in multiple classes
* One Class can have multiple students enrolled

I guess the trouble I'm having understanding is that you've already settled
on the "how" (i.e., the forms), but I'm still short on the "what".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff~

I ended up hiring a consultant to help me on this. He was able to help me
with a different approach that works very well. Thank you for your attempts
at helping me.
 
Back
Top