Introducing 2 new tables
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Introducing 2 new tables
I would like to know if I have done the right thing via the database in adding 2 new tables, they are [Categories] and [Commodities Categories].
The order table (Order Header) contained the pointer to the Commodities table already but the link was not there.
I have pointed the tables [Order Details] and [Order Details Current Prices] to the Commodities Categories table.
I would like to know if I have got my relationships correct.
I am confused.
The order table (Order Header) contained the pointer to the Commodities table already but the link was not there.
I have pointed the tables [Order Details] and [Order Details Current Prices] to the Commodities Categories table.
I would like to know if I have got my relationships correct.
I am confused.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Introducing 2 new tables
The relationships between Commodities, Commodities Categories and Category define a many-to-many relationship: one commodity can belong to several categories, and one category can contain several commodities.
The relationship between Order Details and Commodities Categories associates an Order Detail record with one specific combination of commodity and category.
The same holds for the relationship between Order Details CurrentPrice and Commodities Categories. Is that what you intended?
The relationship between Order Details and Commodities Categories associates an Order Detail record with one specific combination of commodity and category.
The same holds for the relationship between Order Details CurrentPrice and Commodities Categories. Is that what you intended?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
What I intended was to:
1. Assign Categories to Commodities, the Order record to hold the CommoditiesID to reference the Commodities
2. What I wanted to do was to introduce a Category to each Order Detail record based upon the Commodities record linked to the Order record.
Clear as mud??
1. Assign Categories to Commodities, the Order record to hold the CommoditiesID to reference the Commodities
2. What I wanted to do was to introduce a Category to each Order Detail record based upon the Commodities record linked to the Order record.
Clear as mud??
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
What I was asked to do by the Client was assign a Category to the Order Details record.
This to be chosen from a list of Categories assigned to the Commodity that was specified by the Order record.
This to be chosen from a list of Categories assigned to the Commodity that was specified by the Order record.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Introducing 2 new tables
You'll need to create a relationship between Orders and Commodities on CommodityID.
You can create a query that returns the categories corresponding to the CommodityID of the order on the form, and use that as row source for your combo box or list box.
You can create a query that returns the categories corresponding to the CommodityID of the order on the form, and use that as row source for your combo box or list box.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
Do I only need the PK of the Category table in the Order Details table?
Would the additional tables I need be:
Categories - this table to comprise fields:
CategoryID (A/N)
CategoryDescription
CommoditiesCategory - used to relate the Categories to Commodities, this table to have:
PK
CommodityID
Category
It will be used in the ComboBox on the OrderDetails form where the Commodities has been defined in the Order record.
Am I correct there, just interpreting what you wrote.
Would the additional tables I need be:
Categories - this table to comprise fields:
CategoryID (A/N)
CategoryDescription
CommoditiesCategory - used to relate the Categories to Commodities, this table to have:
PK
CommodityID
Category
It will be used in the ComboBox on the OrderDetails form where the Commodities has been defined in the Order record.
Am I correct there, just interpreting what you wrote.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Introducing 2 new tables
Yes, you only need CategoryID in the Order Details table; this corresponds to the primary key of the Categories table.
The table structure that you propose looks OK to me.
The table structure that you propose looks OK to me.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
Thanks Hans, I was overcomplicating it a tad there (understatement).
Is this relationship diagram correct?
Is this relationship diagram correct?
You do not have the required permissions to view the files attached to this post.
Last edited by Pat on 11 Aug 2014, 13:10, edited 1 time in total.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Introducing 2 new tables
It's very easy to overcomplicate this kind of table design - I'm prone to do so myself...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
We overlapped messages there, is that diagram ok now?
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
I would like to add a Category to Categories table and also add a record to the Commodities Categories table. Can I use the one query to add to 2 tables at once?
I am envisaging pressing a button from the Main Form (called Add an Order and Details), this form holds the CommodityID to bring up a maintenance form to add Commodities Categories and maybe the Categories table as well?
Do I need to add Categories into the Categories table first by a maintenance form, then by another form add records to the Commodities Categories using the CommodityID from the main form?
I cannot seem to get my head around this.
I am envisaging pressing a button from the Main Form (called Add an Order and Details), this form holds the CommodityID to bring up a maintenance form to add Commodities Categories and maybe the Categories table as well?
Do I need to add Categories into the Categories table first by a maintenance form, then by another form add records to the Commodities Categories using the CommodityID from the main form?
I cannot seem to get my head around this.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
I have got around this by adding an INSERT INTO for the Commodities Categories table.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Introducing 2 new tables
OK. The following was the reply I had composed in the meantime. It shows an alternative approach.
You need to add a record to the Categories table first, and then add a new record to the Commodities Categories table.
You could use two forms for this, but you could also add both records in the On Click event procedure of a single button, e.g. (warning: air code!):
You need to add a record to the Categories table first, and then add a new record to the Commodities Categories table.
You could use two forms for this, but you could also add both records in the On Click event procedure of a single button, e.g. (warning: air code!):
Code: Select all
Private Sub cmAdd_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCategoryID As Long
Set dbs = CurrentDb
' Add record to Categories table
Set rst = dbs.OpenRecordset("Categories", dbOpenDynaset)
rst.AddNew
rst!Category = Me.txtNewCategory ' get value from form
' Remember CategoryID for later use
lngCategoryID = rst!CategoryID
rst.Update
rst.Close
' Add record to Commodities Categories table
Set rst = dbs.OpenRecordset("Commodities Categories", dbOpenDynaset)
rst.AddNew
rst!CommodityID = Me.CommodityID ' get value from form
rst!CategoryID = lngCategoryID
rst.Update
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
Thanks Hans, I will leave it as it is. Good to know the extra ways of doing things.
Is the form you are suggesting above a bound form?
Is the form you are suggesting above a bound form?
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Introducing 2 new tables
Not necessarily. The text box txtNewCategory would be unbound, but CommodityID could be bound or unbound.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Introducing 2 new tables
I may change it to 2 forms, one to add Categories the other to align Categories to Commodities ( this being a main form (Commodities) the subform being Categories aligned to the main form Communities.
May have 2 tabs one for each function.
Do you see problems with having two tabs to cater for this?
May have 2 tabs one for each function.
Do you see problems with having two tabs to cater for this?
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands