Forms & relationships

[LizJohnson]

I am creating a new database. I have had to create 4 tables. I had to create 2 tables for customer information. The reason for this is that the "parent" table has the Sales & Use (S&U) tax number which is a unique identifier. The 2nd table is the "child" table. This will have all of the same S&U numbers as the first number but will also have a 2nd unique field which is the location field. There can be multiple locations for one S&U tax number.

One the 1st table the S&U number is the primary key. On the 2nd table I have the S&U and the Location ID both set as the primary key. I've done this so that you can have multiple S&U numbers but only one location ID associated with that S&U number.

The 3rd table is where I am storing all my data information such as packs of cigarettes sold, total tax due, etc. On these table, I have set a unique identifier as an auto number, then have the S&U and Location ID as fields.

I've also set the relationship where the relationship from table 1 to table 2 is One to Many with enforced referential integrity & Cascade delete related records. The connecting link is S&U to S&U

The 2nd table to the 3 & 4th table is also One to Many with enforce referential integrity, cascade delete related records with the connecting links as S&U and also Location ID.

I have said that you cannot create an entry in table 2 unless the corresponding S&U number is in table 1.

I am trying to create a form so that I can enter new customers and also all of their information. I have pulled the S&U field from Table 1, the Location ID from table 2. When I try to tab thru the fields, once the form is created it won't let me enter any information into the location ID. It states "Cannot add record(s); join key of table "tblCustomerInfoLocations" not in recordset.

I have attached a copy of my relationship diagram with my tables.

Any help would be greatly appreciated.

 

[jdraw]

A couple of observations to consider.

Do not use embedded spaces in field or object names. You will encounter unexpected, frustrating syntax errors.

I think your CigaretteDataCollection table needs to be redesigned, but it would be helpful to readers if you described a day or week in the "business" that this proposed database is intended to support.

You have told us HOW you did something, but we'd like to hear WHAT that something (the business) is in plain English.
You know the "business" and readers do not, so give us some context to review your set up.

Good luck.

 

[LizJohnson]

I work for the Dept. of Revenue. At the end of June, every business that sells cigarettes in KY has to count every pack or stamps for cigarettes they have on hand and pay a tax increase on those cigarette packs. That is why the Data Collection data base is so large. We have 3 types of stamps that are going to have to count along with the actual packs of cigarettes.

Our system is set up with a S&U tax number. This number is unique but some stores (ex: Dollar General) may have multiple locations. In this case, they are going to let us know how much each store has, but only have to submit one payment for their entire operation. This is why I had used the multiple primary key, so that I could have the multiple S&U number as long as the location ID was different.

The tax payer is also going to have the option to pay those taxes in 3 installments, so I'm going to create a sub-form which will allow me to record the payment date and amount. I also want to keep track of the amount due, after each payment is posted. This will have to be done one the main S&U number.

If you would like me to send you some other documentation on this database, I can do that in the morning. Just let me know what you would like. Hopefully this explains why I have to create this database.

 

[LizJohnson]

I am attaching a zipped copy of the database as it is so far. Any help would be greatly appreciated.

 

[jdraw]

Liz,

See this link for some ideas to test your design/model.

3 types of Stamps and S&U tax number is important to you, but has little meaning to readers.
I want to repeat my previous caaution about field and object names with embedded spaces. They will be a source of frustration sooner or later.

You can only comment on a design when you are familiar with the business facts it is intended to support.

 

[LizJohnson]

Thank you for all your help. I am going to attach a copy of the two types of forms that a tax payer may fill out. I have to capture the information on the forms in the Access database. This will hopefully help understand the tables that I have created. I will be creating a form and a query so that the user will see the form more like the paper form. When possible, I will be putting in calculations so that Access will do the calculation. We can then use this as a way to make sure the tax payer has calculated the values correctly.

Hopefully you will be able to see the 3 different types of stamps/cigarettes that I was mentioning. Also as noted above S&U is the abbreviation for Sales & Use tax. I had noted that in a previous comment. I'm sorry that I didn't repeat it in my last comment.

Once again, thank you for your help.

 

[LizJohnson]

I've been working hard on my forms. I have created a form in order to add new tax payers. It has a sub-form to add the location. This form is working as intended. Yeah!.

I have also created a form to match the simplest form that I have to duplicate in Access which only requires two types of cigarette packs. It needs the tax payer to put in the number of packs. I have the tax rate and I have a formula in to calculate the tax due based on the # of packs entered * the tax rate. Unfortunately, my formulas are not calculating. I have built an expression on the form for these calculations.

Any idea of why the formals would not work? I have attached a new copy of my database along with the 2 forms I will be creating. The one I am trying is on the 2nd page.

Any help would be appreciated.

 

[LizJohnson]

Thank you for this help. I will create the new Stamp table and integrate that into the RollNumbers table.

This is going to be a one time counting of the cigarette packs and cigarette stamps/pads on had at 1 point in time. This will not be anything that will be a continuing thing that the tax payer is going to have to count. They will be able to make 3 payments on the amount they owe based on the count that they did at that point in time.

 

[LizJohnson]

Instead of creating a new stamp table, I decided to change to RollNumbers table to only have 2 columns. One will be labeled StampType with a list value of "Roll" & "Pad" and another labeled StampSize with a list value of "20" & "25".

Now, I have created the Value List with the row source's as noted above. Ususally, when you have a value list, you can only use those values, but the table is allowing me to enter other values. I have check "No' to allow value list edits. Any idea why this is allowing me to enter different values?

 

[LizJohnson]

Excel is not going to give me what Access can, the ability and flexibility to input large quantities of data.

I do appreciate your help Pat. I'm just trying to make this easier for everyone involved.