tblPartners
PartnersID - PK
ManagerID - FK
LastName
FirstName
CompanyName
EmailAddress
JobTitle
BusinessPhone
Ext
MobilePhone
FaxNumber
SuiteNo
Address
City
State/Province
ZIP/Postal Code
Country/Region
WebPage
CompanyQuals
Notes
MainTel
MainFax
tblManager
ManagerID - PK
Manager
tblBusinessSize
BusinessSizeID - PK
BusinessSize
PartnersIDFK
tblCertification
CertificationID - PK
CertificationType
tblPartnersCertification
CertificationID - FK
PartnersID - FK
tblNAICSCode
NAICSCodesID - PK
NAICSCode
Description
tblPartnersNAICSCode
NAICSCodesID - FK
PartnersID - FK
tblCapability
CapabilityID - PK
Capability
tblPartnersCapability
CapabilityID - FK
PartnersID - FK
tblClients
ClientsID - PK
Office
Description
tblPartnersClients
ClientsID -FK
PartnersID - FK
tblContractVehicle
ContractVehicleID - PK
ContractVehicle
tblPartnersContractVehicle
ContractVehicleID - FK
PartnersID - FK
1) I need to design a form where For each Partner (tblPartner), a user can select one or multiple field from:
* tblCertification
* tblNAICSCode
* tblCapability
* tblClients
* tblContractVehicle
2) I need a report where users can query the database via a form-like interface
Note: tblPartnersCertification, tblPartnersNAICSCode, tblPartnersCapability, tblPartnersClients, tblPartnersContractVehicle are all Junction tables
I have done several experiments with subforms and read countless many-to-many advise but still frustrated, I need a savior.
Thanks
EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorials/aspnet/8a2ea78b-f1e3-45b4-93ef-32b2d802ae17/wpf-datagrid-custom-pagin.aspx
ImplementingM2MRelationship.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=342
(Method 2 only)
ReallyBadDesignDecisions.doc
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=291
(there are two databases, one shows you how NOT to design a form with
multiple many-to-many relationships and the other shows you how to fix it.)
ChooseReportFieldsRows.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=384
This sample very roughly illustrates how to create a query programmatically
which chooses the FIELDS to be reported on a report based on the selected
values of a multi-select list box and then allows the user to specify the
ROWS to be reported.
Also, Duane Hookom's "Query By Form"
http://www.access.hookom.net/Samples.htm
Hopefully I haven't just added to your frustration.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
<Saji Ijiyemi> wrote in message news:200910114224...@gmail.com...
I think this form setup would work for you --
Main form - tblPartners.PartnersID (not visible), other partner information
Subform Certification - CertificationID (not visible), CertificationType
(Combo)
Master/Child links - tblPartners.PartnersID,
tblPartnersCertification.PartnersID
Subform NAICSCode - NAICSCodeID (not visible), NAICSCode (Combo), Description
Master/Child links - tblPartners.PartnersID, tblPartnersNAICSCode.PartnersID
Subform Capability - CapabilityID (not visible), Capability (Comobo)
Master/Child links - tblPartners.PartnersID, tblPartnersCapability.PartnersID
etc...
--
Build a little, test a little.
Thanks all.
I've created the necessary tables, forms and subforms I needed. This
is a great forum and Mike Infiesto has been of great assistance. What
I did was in line with what KARL explained, though I have
BusinessSizeID inside my tblPartners, hope it'll give the same result.
The next two challenge is to:
1) Tweak my subform so to prevent users from selecting duplicate field
from each attributes (certification, client, Capabilities, etc).
Currently if I select "Certification 1" from tblCertification, If I
clikc on the next field on the subform, I would still be able to
select "Certification 1" for the same PartnerID. I want prevent
duplicates and possibly have a MsgBox alerting users that they have
already selected "Certification 1"
2) Create a form-based report that will allow users combine different
criteria and generate a report on the fly based on their criteria. I
was thinking I could use the same form design?
Thanks.