|
| 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. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 1st, 2009, 07:22 PM
posted to microsoft.public.access.forms
|
|||
|
|||
Data Entry into Many-to-Many forms in Access 2007
I am developing a partners database for a my company 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...tom-pagin.aspx 
|
|
#2
October 1st, 2009, 08:26 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Data Entry into Many-to-Many forms in Access 2007
You don't really tell us what you've tried or where your frustration comes
from. But I'd like to suggest a few samples from my website that might help. ImplementingM2MRelationship.mdb http://www.rogersaccesslibrary.com/f...ts.asp?TID=342 (Method 2 only) ReallyBadDesignDecisions.doc http://www.rogersaccesslibrary.com/f...ts.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/f...ts.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/...UBED1=ACCESS-L Saji Ijiyemi wrote in message ... I am developing a partners database for a my company 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...tom-pagin.aspx
|
|
#3
October 1st, 2009, 08:55 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Data Entry into Many-to-Many forms in Access 2007
Your --
tblBusinessSize BusinessSizeID - PK BusinessSize PartnersID - FK will allow only one partner for a particular size business. You need a union table like the other attributes. 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. "Saji Ijiyemi" wrote: I am developing a partners database for a my company 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...tom-pagin.aspx
|
|
#4
October 6th, 2009, 12:56 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Data Entry into Many-to-Many forms in Access 2007
On Oct 1, 3:55*pm, KARL DEWEY
wrote: Your -- tblBusinessSize BusinessSizeID - PK BusinessSize PartnersID * - FK * * * *will allow only one partner for a particular size business.. You need a union table like the other attributes. 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. "Saji Ijiyemi" wrote: I am developing a partners database for a my company 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...e3-45b4-93ef-3... 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
