Question Need some help to fix my database design

[carriepedersen]

Hello there

I'm going a little crazy trying to create this access database. What I am trying to do is to show taxability of all products for each state. Right now all of the information was in one table, but its very ugly (items and states duplicated numerous times) and hard to manage. I'm just not sure how to create this. I have about 87 products and I want to be able to show whether each of those it Taxable or Exempt in every state and then provide additional information. Example of some of the information.

Product -
Product Code -
State -
Tax Status - T or E
Regulation -
Notes -

Any ideas are greatly appreciated.

Thank you!
Carrie

 

[Beetle]

From your description you appear to have a many-to-many relationship between states and products, so you need (at a minimum) three tables. For example;

tblProducts
********
ProductID (Primary Key)
Description
other attributes of a product

tblStates
*******
StateID (Primary Key)
StateName
other attributes for each state

tblStateProducts (the junction table)
*************
ProductID (Foreign Key to tblProducts)
StateID (Foreign Key to tblStates)
TaxStatus
other attributes that are specific to the relationship between product and state

In tblStateProducts you could use ProductID and StateID as a compound Primary Key, or you could add another field for a surrogate PK using an Autonumber.

 

[carriepedersen]

Hello and Thank you very much for getting back to me so quickly!! That helps a lot and I think I got my three tables setup. Question though; do you know of any way to automatically add all states for each product automatically? I'm not sure if there is any easy way to do that. Each product is going to have each state and the tax status in the tblstateproducts. I'm not sure if there is a way to add this without having to copy and paste this 50 times for each product.

Thanks again!!

Carrie

 

[Beetle]

This could be done with some VBA code using a couple of recordset loops and an update query, but I would need to know more details about your current table setup (including your original table and your new tables) before I could be specific. By details I mean relevant field names, the PK field of each table and the data types (i.e. Text, Number, Autonumber).

If you just want a general idea of one way to approach it so you can tinker around with it yourself, below is some sample code based on the following table structure;

tblProducts (already populated with Product info)
********
ProductID (Autonumber PK)
ProductName
etc.

tblStates (already populated with State info)
*******
StateID (Autonumber PK)
StateName
etc.

tblStateProducts (not yet populated)
************
ProductID (FK to tblProducts)
StateID (FK to tblStates)

The code is below

Note: If you are going to attempt this, do so on a backup copy of your application.

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Set rs = CurrentDb.OpenRecordset("tblProducts")
Set rs2 = CurrentDb.OpenRecordset("tblStates")
With rs
    .MoveFirst
    Do While Not .EOF
        With rs2
            .MoveFirst
            Do While Not .EOF
            strSQL = "Insert Into tblStateProducts (ProductID, StateID) " _
                   & "Values(" & rs!ProductID & ", " & rs2!StateID & ");"
            CurrentDb.Execute strSQL, dbFailOnError
            .MoveNext
            Loop
        End With
    .MoveNext
    Loop
End With

So assuming you had 50 products and 50 states, the above would insert 2500 new records into the junction table.

 

[stopher]

Another way is to create a special type of query called a Cartesian product.

- create a new query (is the query editor)
- add both the products table and the states table but do not join the tables
- select the field you require from each table

If you run this query you will see that it produces every combination of product and state. You can convert the query to an append query to add to your junction table.

hth
Chris

 

[carriepedersen]

Thank you very much!! I tried putting that code in and it doesn't work. I tried to name my tables the same as you mention. I'm not sure why the code doesn't work. Below are some of the screenshots of each of my tables. Sorry I'm not so great at the Code stuff.

Thanks again!!

 

[Beetle]

What do you mean by "it doesn't work"? Do you get an error message? Anything at all?

Also, Stopher had a good suggestion about using a cartesian product query, so you may want to try that method if you haven't already.

 

[carriepedersen]

I didn't get anything at all. No error message at all. I don't know what I am doing wrong. I haven't tried the other way. I guess I will give it a shot. Sorry guys I'm kind of teaching myself as I go.

I appreciate your help!

Thank you!

 

[carriepedersen]

Stopher

Your suggestion worked great!! Only problem is that I don't see if I add or delete a product it doesn't show up in my junction table. It won't allow me to delete a product and it doesn't show one that I added. Any ideas?

Thanks again!!

Carrie

 

[Beetle]

It won't allow me to delete a product

This is likely due to the way you have defined your relationship. If you have Referential Integrity enforced, and you don't have Cascade Deletes selected, then you would not be able to delete a product if the junction table has related records.

it doesn't show one that I added

Are you saying that if you add a new product to tblProducts you are expecting that related record(s) would automatically appear in tblStateProducts? If so, that will not happen. The related records will always have to be created either through a manual data entry process via a form, or by running an append query of some sort.

 

[carriepedersen]

So there is no way once a product is added to update all of the states for that product? I am going to have to enter all of the states manually for new products?

I'm hoping that there might be something that would work so when new products are added they include all states. I think that if the user has to manually enter each state all the time then its going to be a longer process.

Any thoughts?

Thanks so much!

Carrie

 

[Beetle]

So there is no way once a product is added to update all of the states for that product? I am going to have to enter all of the states manually for new products?

Not necessarily. As I said, it would have to be done either manually or via an append query. The latter method could be executed via code behind a command button on a form (or via some other method). This would give the appearance of being "automatic" to the user, but you still have to write a little code to make it happen.

If you can post a stripped down copy of your db with any sensitive data removed I can post back a working example if you like.

Edit: If you do, post in the older .mdb (A2003 or older) format.

 

[carriepedersen]

Hello there

Attached is a very stripped down example of my database. I appreciate your help with all of this! I think once I get this piece figured out I am on a roll. Thanks again!

Ok well I thought I was going to be able to send you a copy of database, but I am getting the below error when trying to upload it. I'm not sure how to fix this. Any thoughts?

"Your submission could not be processed because a security token was missing."


Thank you!

Carrie

 

[Beetle]

OK, I've been busy today and I haven't had much time to check the forum, so sorry for the delay.

Since you can't upload your db right now I'm going to attach a small example db so you can see how this can be done with a little code and an append query. It's just a rough example I threw together but hopefully it will help you make some progress in your application.

If you open the Products form and navigate to a record with no states listed in the sub form, then click the Add States button it will add 50 new records in the junction table based on the current ProductID. The newly added records will appear in the subform. If you click the Add States button for a Product that already has related States, you will get a message box informing you of that.

 

[carriepedersen]

I REALLY appreciate you getting back to me. Sorry to keep bothering you with this. I think what you have done in just a few minutes will help me tremendously!!

I am going to give this a try in my database. Thanks again for all of your help! I will let you know if this works out for me.

Thank you very much!!

Carrie

 

[carriepedersen]

Its beautiful and works great!!! Thank you VERY much again!! You have helped me very much! Quick question; the way this is setup I'm thinking I don't need the append query. Am I wrong in thinking that? Sorry I'm not too good when it comes to the codes.

Thank you!
Carrie

 

[Beetle]

Actually, the code in the example I posted does run an append query. It's not a stored query, so it's not something you would see in your query objects list, it's just executed in code. I should have commente the code but I was in kind of a hurry at the time. Here is the code from the example with comments added so it hopefully be a little easier for you to follow whats going on.

Private Sub cmdAddStates_Click()
 
'Declare the variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
Dim strSQL As String
 
'Create a recordset of States
Set db = CurrentDb
Set rs = db.OpenRecordset("tblStates")
 
'Create a message to be used in the message box
strMsg = "Do you want to add all states for this product?"
 
'Save the current record if it has been edited (i.e. it is "dirty")
If Me.Dirty Then Me.Dirty = False
 
'Check if there are already States in the junction table for this Product
If Not IsNull(DLookup("ProductID", "tblStateProducts", "ProductID=" & Me!ProductID)) Then
    'If so display a message box
    MsgBox "States have already been added for this product."
Else
    'Message box to verify the user wants add all States for this product
    If MsgBox(strMsg, vbYesNo + vbQuestion, "Add Records") = vbYes Then
        'If Yes then move through the recordset of States
        'insering a record for each State in the junction table
        'using the ProductID of the current record
        With rs
            'Make sure we are on the first record in the recordset
            .MoveFirst
            'Loop through the recordset
            Do While Not .EOF
                'SQL for the append query
                strSQL = "Insert Into tblStateProducts (ProductID, StateID) " _
                       & "Values(" & Me!ProductID & ", " & rs!StateID & ");"
                'Execute the append query
                db.Execute strSQL, dbFailOnError
                'Move to the next record
                .MoveNext
            Loop
        End With
        'Requery the sub form to show the new records
        Me.sfrmStates.Form.Requery
    End If
End If
 
End Sub