Web Development

Ask something
Question Locked

data entry in many-to-many tables via forms

0 helpful
4 replies

by kaizn

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am building a partners mgt database for my company and I am stuck with how to create a data entry form that will serve different tables in a many-to-many relationship.

I also need to to design an MS Access form interface that users can use to query the db based on different criteria

The main table is the tblPartners table which stores data of partner companies and is linked to 6 other tables in a many-to-many relationship:

One such table is the tblClients table which will store a list of different clients for each of the partners in tblPartners

One partner can have one or many clients and one client may be doing business with one or many partners.

Data Entry
-----------
1. How do I create a form that users will use to select one or more client for each partner?

Reporting
----------
2. How do I create another form that users will use to query the database to generate custom report. For example, to retrieve all partners for a particular client.

Thanks for your help. Hope I am clear.

4 replies

by robo_dev

In general, both questions are really how to construct a query to get the info you want, no?

Do you have working queries that can return the data you want? (or is that what you are asking?)

Or are you asking how to create forms?

If it's a query question, post your query and more detail about your tables (e.g. what the primary keys are), and what result is or is not returned.

by kaizn

Thanks robo_dev:
First is how to create the forms (i guess via subforms)
Second is how to create Query-based forms to return data from the dbase.

Tables:
tblPartners: Main Table
------------
PartnersID-PK
LastName
FirstName
CompanyName
EmailAddress
JobTitle
BusinessPhone
Ext
MobilePhone
FaxNumber
SuiteNo
Address
City
State/Province
ZIP/Postal Code
Country/Region

tblCertification
----------------
CertificationID-PK
CertificationType

tblPartnersCertification
-------------------------
PartnerCertID
PartnersIDFK
CertificationIDFK

tblCapabilities
---------------
CapabilitiesID
Capabilites

tblPartnersCapabilities
-----------------------
partnersCapabilitiesID
PartnersID-FK
CapabilitiesID-FK

I need an interface (form) where users can enter data into tblPartners and select itmes from tblCertification (drop-down list/combo box) and tblCapabilites (drop-down list/combo box) for each partner.

Hope it makes sense.Pls let me know. thanks

by kaizn

How do I upload file in this forum?
Thanks

This discussion is closed to new replies.