Tax

[Infinite]

Hello, I have a tblShowCosts and in that has the Event ID and the Show tax rate. In my tblShowSales I want access to take the subtotal and times it by that shows tax. Not sure how to tell access if the Show name = such-and-such then times it by the Tax Rate. This sounds easy, and im sure it is, im just not sure how to. Thanks!

 

[Uncle Gizmo]

So the Tax Rate is identified by event ID?

 

[Infinite]

Yes it is.

 

[Uncle Gizmo]

What do you mean by "subtotal"

 

[Infinite]

the "Sub" "Total" As in either Quantity Times Item Price Or whatever I want. The subtotal (asides from a field name) has nothing to do with this really. I just need to tell access that if the Event ID (or name) = such-and-such then to times the subtotal by that Event IDs tax rate that I input.

 

[Uncle Gizmo]

So you also have an event ID field in your table tblShowSales?

 

[Infinite]

Yes. I do.

 

[Uncle Gizmo]

Is this event ID field in table tblShowSales, a combobox and filled by values from the table tblShowCosts?

 

[Infinite]

It is not a combo box, it is also filled with the event IDs, not the Show name, which the tblShowSales has (a Show name, not EventID)

 

[Infinite]

So, you have any idea on how to do that?

 

[Uncle Gizmo]

I don't understand how you change or add or update the Tax ID.

 

[Uncle Gizmo]

Reviewing your question after a good night's sleep I make the following observations.

I don't believe I have enough information yet to make a reasonable answer.

My other concern is that it appears you are trying to update fields in a table, which is not recommended. It would be better to do your calculation in a query and show the results on a form.

 

[Infinite]

OK, I should probably reexplain what im trying to do. I have a tblShowSales, and in that I have a table named EventID (But it is just the ShowName), and that is a drop down menu, that gets its information from qryAdminEvents. In qryAdminEvents, there is fields, 2 of them are EventID and ShowName. Another one of them is the Field named "Tax". The Tax field gets its information from the tblShowCosts, and in that field there is lots of tables, and 2 of them are EventID (actually the eventID) and the field Tax. There is also a field named "Gross Sales" in the tblShowSales, and what I want to do is times the Tax (As long as the EventID (or name, becuase it is that)) and times it by the Tax of that show. Now, remember, each ShowName is different. Now, is there anything else that you would need to help me out? If so just say it.

FYI: I would rather not give you my database, as it has lots of information I would rather not be on the web currently. If you must have it to help I could delete it and send you the copy I could, but id rather not.

Thanks for your help!

My other concern is that it appears you are trying to update fields in a table

I was, but you are correct, so ill just stick with a query to do that, but I still need to know how to do that.

I don't understand how you change or add or update the Tax ID.

in the tblShowCosts I have the tables tax, and eventID, when I update that information on my frmEvents, it updates it in the tblShowCosts. Does that explain it at all?

 

[wiklendt]

FYI: I would rather not give you my database, as it has lots of information I would rather not be on the web currently. If you must have it to help I could delete it and send you the copy I could, but id rather not.

1) How about you just post a screenshot of your relationship view.
&
2) Next, in plain english, explain what you need your database to do - so, leave database-speak out of it and just tell us what you want to do (e.g., "we run events and need to bill customers for each event. customers choose what additions to have for each event like: meeting room 1 + meeting room 2 + catering for lunch + catering for dinner + all for 3 days. all these things have to be itemised and subtotaled without tax first, and at the end the invoice must list a final calculation for all taxes to then make a total cost") or whatever your actual process is.

These two things will give us a clearer idea of what your database *can* do, and will give both us and you a clearer idea of what you *want* it to do.

 

[Infinite]

Uhh...Why would you need a screenshot of my relationship view?
Just wondering.

2nd) I need to be able to see the tax for each item.

I will explain it again.

I just sold a 22 Cowboy pistol and the show OFAST - 4 2015. The tax for OFAST that year was 8 percent. The 22 Cowboy Pistol sold for $9.97. The tax was?...That is what I need to find out, but on a larger scale, because I have thousands of sales to do that for, and I cant do that manually, so I would need a query or calculated field for that. Just incase you need it (im betting you do) I have attached a image of my relationships tab. Thanks for staying with me and helping me through this!

Now, about that information and the image. The tax for the shows are saved in tblShowCosts. And that is linked to the tblEvents which is linked to the tblShows and that is linked to the tblShowSales. And the eventID in tblShowSales should really be changed to ShowNameID or something like that, as it is just the show ID dropdown. The 2nd image is the dropdown relationship, which should also help.

The image qryAdminEvents is the relationship view of the query. And here is the sql also, just incase.

SELECT tblEvents.EventID, ([ShowName] & " " & Format([StartDate],"mm-yyyy")) AS ShowNames, tblEvents.StartDate, tblEvents.EndDate, tblEvents.StateID, tblEvents.ShowID, tblShows.ShowName, IIf([tblShows]![HomeAway]=-1,"Home","Away") AS HomeAway, IIf([tblShows]![InsideOutside]=-1,"Inside","Outside") AS InsideOutside, tblShowCosts.ShowZipCode AS [Zip Code]
FROM tblShows RIGHT JOIN (tblShowCosts RIGHT JOIN tblEvents ON tblShowCosts.[SCEventID] = tblEvents.EventID) ON tblShows.ShowID = tblEvents.ShowID
WHERE (((([ShowName] & " " & Format([StartDate],"mm-yyyy"))) Like "*" & [Forms]![frmEvents]![txtSearchS] & "*")) OR (((tblEvents.StateID) Like "*" & [Forms]![frmEvents]![txtSearchS] & "*")) OR (((tblEvents.EventID) Like "*" & [Forms]![frmEvents]![txtSearchS] & "*"))
ORDER BY tblEvents.StartDate DESC;

 

[RainLover]

First of all go o OPTIONS and click the box to prevent the showing of the System Tables. They are Read Only and you can do nothing with them.

Second changed the name of your Primary and Foreign keys to better describe what they are. Instead of ID try PK or FK.

Place the PK at the top of the Table so we always know where it is. Follow that by any FK.

Next print out a copy of the Relationships and pin it to the wall which will give you a map of what is what and where it fits in.

Finally I must say that I do not understand your design. Doing the thing above may help.

 

[GaryPanic]

weird
Is the tax static - ie - the same say 8 % or does it differ per item

Boots @ 8%
Shirts @ 15%
If its the same - easy peesey - if it floats all over the place then that's a little trickier

is it per items per sale or what

so in your imaginary shopping basket
Boots cost 100 * 5 pairs = 500 (do you want tax here per item )* 8% =40 total 540.00
Shirts cost 25 * 5 pairs = 125
total 625 - do you want tax here on your sub total



- you should have a tax table
If its static tax then you could ahve a look up field - if the tax changes per item per year then you will need to record the tax rate (I would suggest per item )

so in 2013 boots @ 100 + tax rate 8%
and if the tax rate changed in 2014
boots @ 100 + tax rate 13%

if the tax floats in relation to the items Boot tax @ 8% and shirts @ 10% then you need some kind of product code to dlook up the tax rate applicable and store the rate (or the tax id code in the table - not a lot of difference either way)

 

[Infinite]

No, the tax is per show, not per item.

 

[wiklendt]

Uhh...Why would you need a screenshot of my relationship view?
Just wondering.

Thanks for uploading those.

Generally, a picture is worth 1000 words. Specifically, relationship view will show us if there are any issues with your base design that you might not be aware of and might be affecting your problem. It helps us visualise how you are putting your data together and whether it is possible to obtain the calculations you are asking for - also will help determine the best way to get the calculation you want.

From your screenshot i can see several issues which may be giving you grief with your calculations (others have seen problems too - which is why some posters have started to give you database design advice), but first i'll wait for your reply to the more recent questions about your tax calculation [edit: you beat me to it!] that other people have posted and we can begin to work from there on the actual calculation-side.

I would, at this point, try to avoid making you make drastic changes to the design because it sounds like you have been using this database for a while and have lots of data. this often makes it quite a pain to re-design. however, i would strongly suggest perhaps even creating a new database for future shows with improved design (the forums can help with basic good design practices) - when you can get around to it, of course!

Agnieszka.

 

[wiklendt]

No, the tax is per show, not per item.

OK. So i just want to make sure that when you mention "Tax" - every "tax" related field in your tables refer to this one tax that is applied at every show to items you sell? just because you have a Tax field in multiple places except the one place you would actually need it. (see my annotations on your relationship view, attached/below).

Some questions from the annotations: is there a difference between a show and event? just wondering why you have separate tables for the two.

The "tax" field in your tblShowSales: are you currently manually adding the TaxRate to this field for calculating the tax paid/payable on each sale? i assume this is the bit you want to 'automate' - if so, you need to have the TaxRate field in the show/event table (because you just said the TaxRate is directly associated with the show, not the items). Then access can do the work for you via queries instead of 'hardcoding' for every single sale.

Hope that helps?

(correction on my annotation: the Tax *is* stored in a table, but as "TaxRate" in the show/event table - you only would need to store this rate once, then you can use it in calculatins - but you need to store it in the correct place, which is with the show/event.)