Introducing 2 new tables

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Introducing 2 new tables

Post by Pat »

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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Introducing 2 new tables

Post by HansV »

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?
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

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??

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Introducing 2 new tables

Post by HansV »

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.
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Introducing 2 new tables

Post by HansV »

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.
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

Thanks Hans, I was overcomplicating it a tad there (understatement).
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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Introducing 2 new tables

Post by HansV »

It's very easy to overcomplicate this kind of table design - I'm prone to do so myself...
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

We overlapped messages there, is that diagram ok now?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Introducing 2 new tables

Post by HansV »

Yes, I think so.
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

Thank you

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

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.

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

I have got around this by adding an INSERT INTO for the Commodities Categories table.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Introducing 2 new tables

Post by HansV »

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!):

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

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

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?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Introducing 2 new tables

Post by HansV »

Not necessarily. The text box txtNewCategory would be unbound, but CommodityID could be bound or unbound.
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

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?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Introducing 2 new tables

Post by HansV »

No, that's fine.
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: Introducing 2 new tables

Post by Pat »

Thank you