Database update queries not working

[TomAtNMR]

All,
Please see the attached DB created by another user which I am trying to edit to suit my needs.


I am having 2 different issues which YouTube cannot seem to solve

1. I have a tax form which should show all of the cities in which the database has customers and then I will fill in the tax rates for the city/county of each customer. However, I think I went wrong when I tried to make a query to show the cities/counties of each customer and the tax rate shown in the taxes table. I need the taxes form to auto-populate each customers city but this is not working. It only works if you take the city/county info and fill it in on the taxes table.
2. I am also trying to create a form to update the pricing of each piece of equipment. If multiple customers have the same model of equipment, I want to update the price of one and that price be consistent with all customers having the same model.

Thanks in advance to any tips that will help, I am very new to access.database.7z.zip

I have renamed the attached file from.7z to .zip. (for some reason the upload was failing as a 7z)
This should be changed back to .7z before unpacking.

[isladogs]

Hi & welcome to the forum.
Many people, including myself, don't use 7z compression so won't be able to assist.
Suggest you upload again as a standard zip file using Windows compression

[mike60smart]

Hi

Unable to open the Zip file - I think you have zipped the file while it was open.

Can you close the database and then zip again?

[mike60smart]

Hi
Ignore my last comment. I now have the file opened.
Which Form are you having the trouble with ?

[ssanfu]

Here is the dB zipped with WinZip

[davegri]

I am having 2 different issues which YouTube cannot seem to solve

1. I have a tax form which should show all of the cities in which the database has customers and then I will fill in the tax rates for the city/county of each customer. However, I think I went wrong when I tried to make a query to show the cities/counties of each customer and the tax rate shown in the taxes table. I need the taxes form to auto-populate each customers city but this is not working. It only works if you take the city/county info and fill it in on the taxes table.
2. I am also trying to create a form to update the pricing of each piece of equipment. If multiple customers have the same model of equipment, I want to update the price of one and that price be consistent with all customers having the same model.

Please be more specific.
For 1. What is the tax form name? What is the query name? What is the query used for? What doesn't work about it? Please NAME your forms, queries and tables. We don't want to guess.
For 2. Have you started the form? What is it named? Where (what form) do you want to use to update the price of one customer? What form will show that all customer prices are the same after you update one?

As you see, the information provided in your post is not very helpful for us to envision your problem, let alone solve it,.

[Micron]

Many people, including myself, don't use 7z compression so won't be able to assist.

I haven't used any other utility since Jesus was in diapers. So much easier and still will do standard zip files - either unpacking or compressing. What's not to like?
Anyway, lot's of good help that is half way to the finish line so I'll bow out of this race.

[TomAtNMR]

Oops, sorry.
For 1. What is the tax form name? - frmTaxes
What is the query name? - QryTaxes
What is the query used for? - County tax rates change yearly, so the form will be used to go in, see the city/county of each customer in the companies table (tblCompanies) and we can update the tax rate for their county. The updating is a manual process, but the form should show the city/county for each customer.
What doesn't work about it? - In the form I created, it only allows me to add the city and counties to the tblTaxes rather than pull the info from tblCompanies.

For 2. Have you started the form? - I have re-added the form into the attached
What is it named? - FrmUpdatePricing
Where (what form) do you want to use to update the price of one customer? - FrmUpdatePricing
What form will show that all customer prices are the same after you update one? - frmEquipment
I created an update query (qryEquipPricing) and the form is loaded by qryAllEquip. (although the combobox to show the equipment models only shows 1 model)
I would like to select a model of equipment from the EquipModel combobox, type the new price into the new price box (tbNewPrice) and press the UpdatePricing button to update all of the prices for the model selected in the combobox to the new price.

new attachment database.zip

[mike60smart]

Hi

You say in your explanation for 1 "see the city/county of each customer in the customer table"

But you don't have a Customers Table ?

[TomAtNMR]

fixed original post

[kd2017]

I'm reviewing your data model and I'm not quite following.
1) What is the purpose of [tblLocations]?
2) Can you remove the field [EquipOwner] from [tblEquipment]? You can identify an equipments owner via it's contract
3) sounds like you need another EquipmentModel table that describes the models, their pricing, etc. that the [tblEquipment] can reference instead of the [EquipModel] field
4) Re: your first question. Your query is using the [tblCompanies] address info but shouldn't it be relying on the [tblTaxs] address info? What if the corresponding county/city fields in these two tables differ from one another by mistake?
5) One contract can only be for a single piece of equipment?
6) Should [tblEquipment].[ContractRate] be calculated from [tblContracts].[ContractAmount]/([...ToDate]-[...FromDate]) ?

[mike60smart]

Like you kd2017 I am baffled by the relationships

[TomAtNMR]

I see you are baffled by my lack of knowledge.
1) there is no purpose for [tblLocations]. This db was not created by me, I am just editing it to fit my needs. The original creator likely made that table with good intentions, I am most likely not using the db correctly.
2) I am also not using [tblContracts] If a customer is in this db, then they have a contract.
3) I can create a models table that details each model - this should not contain any serial number info correct?
4) I do not want to use the county/city info from [tblTaxs]. I would like the [frmTaxes] to show the county/city info from [tblCompanies] and I would add the applicable tax rate info for that city/county.
5) one contract is for one customer and all the equipment that customer owns. Except in cases where there is a parent company who owns other companies. In this case one contract is for all equipment owned by the parent company and each subsidiary of that parent.
6) The total contract rate is a sum of each equipment rate plus tax and minus the companies discount. I have figured out how to do this part in the report. I will add a multiplier when the [tblEquipment].ContactPeriod = 2 or more in the report to get the total contract rate.

[kd2017]

I see you are baffled by my lack of knowledge.

No judgment

1) there is no purpose for [tblLocations]. This db was not created by me, I am just editing it to fit my needs. The original creator likely made that table with good intentions, I am most likely not using the db correctly.

I thought the tblCompanies looked like something I've seen from the generic MS Access templates delete tblLocations

2) I am also not using

[tblContracts] If a customer is in this db, then they have a contract.

Delete it!

3) I can create a models table that details each model - this should not contain any serial number info correct?

I would have a Manufacturer table -> a model table that store info about the model -> a equipment table that has a row for each individual piece of equipment for things like purchase price, purchase date, serial #, etc.

4) I do not want to use the county/city info from

[tblTaxs]. I would like the [frmTaxes] to show the county/city info from [tblCompanies] and I would add the applicable tax rate info for that city/county.

I would push back on this a bit. What if you have to companies from the same county/city? Then you would be duplicating information. Otherwise there is no point in having a separate tax rate table to begin with, just put those fields in the companies table

5) one contract is for one customer and all the equipment that customer owns. Except in cases where there is a parent company who owns other companies. In this case one contract is for all equipment owned by the parent company and each subsidiary of that parent.

Looks like we need some more overhauling of the model. You might be going down the road of a recursive relationship which is over my head and as I understand it hard to deal with in MS Access. Either way though it sounds like after you delete the contracts table you would link equipment directly to a company with CompanyID_FK in the equipment table.

6) The total contract rate is a sum of each equipment rate plus tax and minus the companies discount. I have figured out how to do this part in the report. I will add a multiplier when the [tblEquipment].ContactPeriod = 2 or more in the report to get the total contract rate.

my q #6 is moot since you're not using the contracts table

For *example*

[mike60smart]

Hi Tom

Still baffled.

You say :- "I am also not using [tblContracts] If a customer is in this db, then they have a contract."

If you are NOT using tblContracts how can you then say "If a customer is in this db, then they have a contract."

You have stated you are not using tblContracts but also say:-
"one contract is for one customer and all the equipment that customer owns."

I believe you need to step back and draft out on paper the process of doing Data Input for 1 Company.

Then explain this to us.