CODEWITHSUNDEEP
formsms-accessdropdownpopulate
FishFold
FishFold

Reputation: 51

MS Access: Query to create From based on different tables, how to save different data fields into a master table?

I have used Access' Query Wizard to help build a form based on different tables. I have the following tables:

Retailer
- RetailerID
- RetailerName

Product
- ProductID
- ProductName

My objective is to create a Form with RetailerID and ProductID as dropdown lists to populate two empty fields, RetailerName and ProductName. However, to do this, I had to create another separate table with fields RetailerID and ProductID as "Lookup" to pull data from Retailer table and Product Table. I then used a Query that gets data from all 3 tables.

The Query takes the following:
- RetailerID, ProductID (3rd table I created for the dropdown list functionality)
- RetailerName (Retailer table)
- ProductName (Product table)

Only by doing this, I can store the records of RetailerID, RetailerName, ProductID, ProductName in the Datasheet view of the query. This is a problem if I want to include a Date/Time column for each row of data.

Am I doing something wrong/inefficient by creating a 3rd table? What is the proper way of querying multiple tables?

Upvotes: 0

Views: 52

Answers (1)

C Perkins
C Perkins

Reputation: 3882

I was specifically wondering if the date applies to either the product, or the retailer, or the combination row. I'll infer that you mean the combined data row. In that case,

  1. Add the field to the "3rd" table along with the RetailerID and ProductID.
  2. Ensure the new field is selected in the form's RecordSource query.
    • You initially built the form using the Wizard, but now navigate to the form's Property Sheet and Record Source property. Click on the [...] button to edit the form's query.
  3. Add a TextBox control to the form and bind it to the new field by specifying the ControlSource property. You can alternatively add the new control by dragging from the "Add Existing Field" bar in form Design View.

Upvotes: 0

Related Questions