Choose column based on clients Category (1 Viewer)

[john_gringo]

Hi

I need to make a query to calculate a price. The formula is
PoductPrice * Vat. But VAT is not the same for every product and there is some client that has a discount on VAT. Check the table below.
Some clients belong to column A values and others at column B
How to make the query to choose the right column based on clients' VAT Category?
If the client belongs to the A category should take values from a list and if on B from column B.

Thanks
Jiannis

VAT Category
Vat_ID	A	B
1	24%	17%
2	13%	9%
3	6%	4%
4	0%	0%
Client VAT Category
Client_ID	Client_Name	VAT_Category
1​	John	A
2​	Alex	B

 

[mike60smart]

Hi Jiannis

It would depend on how you have your tables related.

Can you upload a copy of your database

 

[plog]

You haven't set up your tables properly. You are using your field names to store actual data. 'A' and 'B' should not be field names, but values in fields. [Vat Category] should have 3 columns, but not the 3 you have, this should be its layout:

VATCategory
Vat_ID, autonumber, primary key--this field is correct and should stay
Vat_Category, text, this field will now hold the values 'A' and 'B'
Vat_Value, number, this field will hold the percentage now in the A and B columns

Then when you need to JOIN these tables, you JOIN them via Vat_Category to Vat_Category and the correct value goes to the client.

 

[Uncle Gizmo]

The essence of your problem is that you are thinking in terms of MS Excel instead of MS Access. The approach to solving the problem is completely different between the two platforms.

I answered a similar problem a few years ago and my answer is available on my website here:-

Pull out Percentages - Nifty Access

Pull out Percentages abinjoseph posed the question:- I have a table that I need to search to pull out data. The value needs to found based on salary and if it falls within a range and then all a percentage rate. This was my answer to the question. I am unsure if it was useful

www.niftyaccess.com

More information about process and a free tool to help you do it here:- Excel in Access

 

[john_gringo]

Thanks for the clew. Let me work on it and will post the DB

 

[john_gringo]

I think I am on the right path!!!!!!!!!
Query VatCalculator.

 

[Pat Hartman]

If you don't want to add the complexity of the a/b solution (I wouldn't). Just add a new vat row for whatever rate the customer pays. You can write yourself a note if you add a note column to remind yourself that this is not a standard rate but a discount rate for some customers.

 

[john_gringo]

If you don't want to add the complexity of the a/b solution (I wouldn't). Just add a new vat row for whatever rate the customer pays. You can write yourself a note if you add a note column to remind yourself that this is not a standard rate but a discount rate for some customers.

Isn't it what I did on the last DB I upload? I get rid of columns A and B. Do you mean something else?

 

[Pat Hartman]

My window must have been open for a while so I didn't notice it. Now that I look at it, both queries have incorrect joins. Therefore, your tables duplicate data and the relationships are not correct. Did you not notice that valCalculator returns three of everything?

Relationships are ALWAYS data field (we call this the FK or foreign key) to PK (primary key - usually an autonumber). So, if you want to join tblTrader to VatCategory, you need VatCID in the tblTrader. and you would join vatCID to vatCID. Unless I need to join twice from one table, I always just use the PK name as the FK name. An example of joining twice would be something like StateID if you have both a Billing address and a Shipping address. You would have two state FK's so they can't both be called StateID. In this case, they would be ShipStateID and BillStateID.

Your relationships are data field to data field and because multiple rows match, you are seeing duplicates. The key to a relationship is that ONE and ONLY ONE record will match the FK for all 1-1 and 1-m relationships. Only a m-m will ever return multiple matching rows.

Joins in queries are not relationships. However, the vast majority of your joins will mimic the defined relationship. You will only have a different situation if you are taking data from other sources and trying to import it and have to clean it up because it doesn't have proper FKs or PKs. If all the data is under your control, all joins will be FK to PK and will mimic an existing, defined relationship.

 

[john_gringo]

My window must have been open for a while so I didn't notice it. Now that I look at it, both queries have incorrect joins. Therefore, your tables duplicate data and the relationships are not correct. Did you not notice that valCalculator returns three of everything?

Relationships are ALWAYS data field (we call this the FK or foreign key) to PK (primary key - usually an autonumber). So, if you want to join tblTrader to VatCategory, you need VatCID in the tblTrader. and you would join vatCID to vatCID. Unless I need to join twice from one table, I always just use the PK name as the FK name. An example of joining twice would be something like StateID if you have both a Billing address and a Shipping address. You would have two state FK's so they can't both be called StateID. In this case, they would be ShipStateID and BillStateID.

Your relationships are data field to data field and because multiple rows match, you are seeing duplicates. The key to a relationship is that ONE and ONLY ONE record will match the FK for all 1-1 and 1-m relationships. Only a m-m will ever return multiple matching rows.

Joins in queries are not relationships. However, the vast majority of your joins will mimic the defined relationship. You will only have a different situation if you are taking data from other sources and trying to import it and have to clean it up because it doesn't have proper FKs or PKs. If all the data is under your control, all joins will be FK to PK and will mimic an existing, defined relationship.

Did you not notice that valCalculator returns three of everything?
But this is the case I think....
Any customer can purchase any product and based on the client's VAT category ( Low or Hi ) price will be calculated depending product's VAT category Hi VAT is 24% or 13% or 6% and low VAT is 17% or 9% or 4%). The price depends on two VAT factors.
Ex. Product A has 6% VAT drops to 4% for clients with lower VAT
Product B has 13% VAT drops to 9% for clients with lower VAT
Product C has 24% VAT drops to 17% for clients with lower VAT
Hope I make it clear.

 

[Pat Hartman]

You have duplicate fields in your tables and the relationships in the query are WRONG. PERIOD. You can fix them or not but they are wrong. There are ways to make a Cartesian Product correctly if that is what you want. A Cartesian Product is tblA * tblB * tblC. i.e. ALL rows in each table are joined to all other rows so if tblA has 10 rows and tblB has 100 rows and tblC has 4 rows, you get 10 * 100 * 4 or 400 rows in the result set. As you might imagine, Cartesian Products can produce huge recordsets.

 

[mike60smart]

Hi

So a Client can have a VATType of either A or B

Then you say "High VAT is 24% or 13% or 6% and low VAT is 17% or 9% or 4%).

So if I am Client A and I purchase a Product how do you determine which VAT Rate applies seeing as there are 3 Rates available?

Can you give us an example of how you apply VAT to a Client?

 

[john_gringo]

@ Pat
Thanks for your reply it is a lesson for me. I might not explain correctly.
The query will not produce recordsets, it is only to calculate the price for a product according to customers' VAT category.
No need for a cartesian product. Need to calculate the product price per with correct VAT value standard or low.
@mike60smart
All products have a unique VAT Value, here are the three VAT categories:
ProductA 24% (for some customers will be reduced to 17%)
ProductB 13% (for some customers will be reduced to 9%)
ProductC 6% (for some customers will be reduced to 4%)

Let's say I need to sell productA that cost 100 euros to a customer1 it will be 100+24%Vat.
The same product to be sold to another customer2 which has the right for low VAT will 24% reduced to 17% for the same product. It is a "discount" on VAT for some Customers. So price formula will be 100*17%

As so will be for the other products.

 

[mike60smart]

Hi John

See if this Bound Version does what you want

 

[Pat Hartman]

You can explain all you want. Your tables have duplicate data and the joins are incorrect. That doesn't mean you might not get the answer you expect THIS time but it doesn't make your solution even close to being correct. OR, maybe you know more than I do about relational database design.

You calculate the VAT on an order, NOT ahead of time for all products. The question you didn't answer is how do you determine which company pays which VAT rate for which category of goods. Your VAT table isn't set up to facilitate this.

 

[john_gringo]

You calculate the VAT on an order, NOT ahead of time for all products. The question you didn't answer is how do you determine which company pays which VAT rate for which category of goods. Your VAT table isn't set up to facilitate this.

Sory the whole setup was wrong from the begging.Calculating the VAT on an order is what I am looking for.....Vat Rate is product based.....

 

[john_gringo]

Hi John

See if this Bound Version does what you want

Yes...this is what I was looking for....
Thanks

 

[Pat Hartman]

Sory the whole setup was wrong from the begging.

including the part about using temp tables to create the order. If you want a user to confirm when an order is complete, add a confirmedflg to the Order header and don't ship any order that isn't confirmed. In environments where there are a lot of orders being entered at one time, it is important to actually reserve inventory to you don't sell it to multiple people.

What happens if when you create the temp order, you have sufficient inventory but someone sells it before the order is copied to the order tables? One of my clients (whose name you would recognize immediately) used to have this problem and it was a nightmare to resolve. They were taking orders on line and looking at old inventory because they were not recording sales as they happened. Once the order got moved to the production tables, they had to completely reverify the order in case there was insufficient inventory and then they had to deal with that including apologize to customers. That was one of the problems I fixed for them during my multiple tours of duty there (I was a consultant). For some reason I could never fathom, they were afraid to take orders live. Not only was their solution overly complicated, it made customers angry. Customers are disappointed if you are out of what they want but will frequently add a future order but if you tell them you have what they want today and then tell them tomorrow that you don't, they are angry rather than disappointed.

 

[john_gringo]

including the part about using temp tables to create the order. If you want a user to confirm when an order is complete, add a confirmedflg to the Order header and don't ship any order that isn't confirmed. In environments where there are a lot of orders being entered at one time, it is important to actually reserve inventory to you don't sell it to multiple people.

What happens if when you create the temp order, you have sufficient inventory but someone sells it before the order is copied to the order tables? One of my clients (whose name you would recognize immediately) used to have this problem and it was a nightmare to resolve. They were taking orders on line and looking at old inventory because they were not recording sales as they happened. Once the order got moved to the production tables, they had to completely reverify the order in case there was insufficient inventory and then they had to deal with that including apologize to customers. That was one of the problems I fixed for them during my multiple tours of duty there (I was a consultant). For some reason I could never fathom, they were afraid to take orders live. Not only was their solution overly complicated, it made customers angry. Customers are disappointed if you are out of what they want but will frequently add a future order but if you tell them you have what they want today and then tell them tomorrow that you don't, they are angry rather than disappointed.

I do appreciate your help as also your concern but it is a little bit more complicated than I express here.
Orders are pre-entered associated with the customer. Then I use that form to invoice the customer.
I need to believe that I am on the right track and will not regret it.

 

[mike60smart]

In the example I supplied it it very easy to have a Command Button that produces an Invoice based on the Order Details currently displayed on the Form.
I cannot for the life of me see why you want to make it very complicated by going down the Unbound route.