Tax

[Infinite]

is there a difference between a show and event?

Yes, there is, the Shows are a show, and events are "Events" that happen at those shows.

the Tax table in tblShowSales is just a Tax for my own ideas. That tax field has nothing to do with what I want, that is just imported tax. Once again, nothing to do with the tax that I want.


That Tax Rate I would rather stay were it is, as I the tax rate may change per year, so if I had it in the tblEvents has a thing per show, then it wouldn't work. Though, I could put it in the tblEvents. That would work, but putting it there or in the tblShowCosts, nothing would change. Not really.


Anything else to help to get the query that I want?

 

[wiklendt]

Yes, there is, the Shows are a show, and events are "Events" that happen at those shows.

I think i know what you mean. Something like "The Melbourne Gardening Show" and it will have an event each year - hence your event table being basically dates.

That Tax Rate I would rather stay were it is, as I the tax rate may change per year

Are you now talking about the tax you do want? I'm not sure "stay where it is" means - it doesn't exist anywhere except your tblShowSales, as far as i can see, because you've already said the tblShowCosts is not the one you want.

So if you sell 100 of the same 'cowboy guns' at the same show in the same year, you have to put the taxrate in 100 times because the only place you are able to put it in is at the point of sale. However, if you were to put the taxrate in a table that depicts the actual show at the time you are there, it's 'set and forget' just once each show. As far as i can determine, this IS your tblEvent table - because it is the only one with date restrictions.

so if I had it in the tblEvents has a thing per show, then it wouldn't work.

Why not?

Though, I could put it in the tblEvents. That would work

Wait, didn't you just say it wouldn't work?

, but putting it there or in the tblShowCosts, nothing would change. Not really.

...wait, you said tblShowCosts tax was a different, unrelated tax? Also, when you say it will "change nothing" - what would it be changing from, how are you making these calculations at the moment? (tblShowCosts looks to me like you are recording your expenses for trading at these shows, rather than things to do with the sale items themselves or the show specifically).

Let me see that i understand your process. from what you've written this is what i gather:
You are selling items at these shows and the tax you are interested in is the tax percentage required to markup your goods at these events on top of their base price. This tax is dependent on the year/date of the show (aka "event") you are setting up your stall at.

You want the tax to be automatically calculated for you at sale time, so that you can quote a total cost to the customer. So, you need to connect your tblShowSales to an event first - but you haven't got that, you've connected your sales to a show, not an event (even though your field in tblShowSales is called "EventID", it's not really that). Once you connect to an event *that has a defined tax rate*, then you can get access to calculate it automatically for you in a query.

Because: i might go to the annual melbourne garden show every year, but my tax rate that is use is not dependent on the fact that it is the garden show, it is dependent on the year that i attend. 20 years ago, the tax at the garden show would have been a lot smaller than this year's garden show.

So your sale is not related the show - you are not going to a show - you are going to an event. [Edit - you seem to have your expenditure (tblShowCosts) associated with tblEvents, so it would seem logical to me that you should also have your Income (tblShowSales) associated with tblEvents also].

Unless i am not understanding what you mean by "event".

 

[GaryPanic]

Still is it one tax rate per show ?(per year)
if its one tax rate per year then you just need to store the tax rate
so in 2014 -2015 year the tax is 6%
for 2015-2016 the tax is 10% (if it changes)

you could have on your underlying table - taxrate - default amount and then simplies ..- you just change it once the tax changes

Breakdown

Show - major event say "Melbourne Garden Show"
Event1 (within Show - - How to prune apple Trees)
Event2 (within Show -- How to dig a trench for your Roses)
etc...

 

[RainLover]

Show - major event say "Melbourne Garden Show"
Event1 (within Show - - How to prune apple Trees)
Event2 (within Show -- How to dig a trench for your Roses)
etc...

Could someone explain the meaning of the word "Show"

I am not familiar with its use.

 

[wiklendt]

Could someone explain the meaning of the word "Show"

I am not familiar with its use.

It's like an expo - usually a single place and time where commercial trades can show off their goods. Doesn't usually go longer than a week but some are long standing and can go on for ever: like "The Royal Easter Show", where kiddies can go on pony rides, meet lots of animals, regular people bring baked goods for judging, other rides, etc etc. There are also lot of trades there usually too, like tractor companies, fertiliser manufacturer, etc. this is also a common place to buy 'fair' types of food, like fairy floss (USA: "cotton candy").

Whether this is the same 'show' that the OP is talking about i don't know, but this is what a "Show" is down under.

 

[RainLover]

All very interesting. Just to be sure I think the OP should explain.

If a show happens every few years the show rate cannot be changed for previous periods. It must have a new rate applied for this current period. Also a rate must be applied to every other transaction. Example, could be a Car Show. Both of these in period 2105 would also require a rate that covers all base rates/ transactions for that period. You could end up with lots of different items and hence different rates.

I am not sure if I am correct, We are comparing the USA. with Australia and things are done differently there compared to here in Aus.

 

[Infinite]

Could someone explain the meaning of the word "Show"

OK, you wanted me to explain, so this is what I would think of a show. We whent to the Kid Fest show. That is not a show, that is a "Event". The Kid Fest is a Event. We went to it on 2013. Now, we have a show. The Kid Fest 03-2013 Show. We went to it in 2014, that is a show. Its now the Kids Fest 03-2014 Show. Is that about what you were thinking wiklendt?

 

[wiklendt]

OK, you wanted me to explain, so this is what I would think of a show. We whent to the Kid Fest show. That is not a show, that is a "Event". The Kid Fest is a Event. We went to it on 2013. Now, we have a show. The Kid Fest 03-2013 Show. We went to it in 2014, that is a show. Its now the Kids Fest 03-2014 Show. Is that about what you were thinking wiklendt?

I'm sorry, I cannot make sense of what you've written. Do you mean I had the right idea but the wrong way around? Like:
Event: kid fest
Show: 03-2013 (for march 2013?)
Show: 03-2014
Show: 03-2015

All shows were part of the kid fest event, but each show of the kid fest event is in a different year.?
Can you give some example data from those two tables?

 

[Infinite]

Events are really events, more of...shows, I guess. We have shows, and those shows we do multiple times a year sometimes. So, there kinda shows, not events. But, instead of having a single show ID for each show, we just have a ShowID, and a EventID. That way its easier to keep track. I am afraid though that we have gotten waaaay of track. Do you really need to know the difference between a show and a event? I mean ya, that would be nice to know, But, this post was started to figure out how to get my Tax.

 

[wiklendt]

Events are really events, more of...shows, I guess. We have shows, and those shows we do multiple times a year sometimes. So, there kinda shows, not events. But, instead of having a single show ID for each show, we just have a ShowID, and a EventID. That way its easier to keep track. I am afraid though that we have gotten waaaay of track. Do you really need to know the difference between a show and a event? I mean ya, that would be nice to know, But, this post was started to figure out how to get my Tax.

We need to know difference because it will impact where the best place is to put your tax. Did you read my previous post asking why it would not work in events?

Your explanation does not work. Here is how you sound:
"Yes cat and dog is different because cat is a cat and dog is not a cat but it is like a cat but a dog is a dog and we have more dogs."
From such an explanation all we know is that they are different, but we don't know how and we still don't know what is a dog and what is a cat.

What we need to know is what IS a show and what IS an event. Explain it to us *without* using the word "show" to define "show". How do the two relate to each other? Give some sample data that makes it obvious.

This is very important because it can make the tax data easy or very very hard to use if it is put in the wrong place.

 

[RainLover]

@ Wiklendt you are doing a great job. However please tell me "Do you really understand what is what."

I am still lost and am about to give in unless I get a better understanding.

_________________________________________________________________________________________

@ INFINITE

What defines a SHOW, and what defines an EVENT.

If I had one list of Shows and Events that I need to sort into either Shows or Events.

How do I know where each belongs.

What is the difference between each and how do I tell them apart.

_________________________________________________________________________

Also you need to fix your relationships. You need to Enforce Referential Integrity. Please do that and post a new pic. BTW "DATE" is a reserved word. You cannot use it. If you don't understand then do a Google on Reserved words.

 

[wiklendt]

@ Wiklendt you are doing a great job. However please tell me "Do you really understand what is what."

I am still lost and am about to give in unless I get a better understanding.

No, i'm not really sure what's what yet, but we'll get there. It may be that Infinite think it's too obvious or too irrelevant to explain it properly, or perhaps Infinite believes the explanations are adequate.

@Infinite, you have to remember that we know nothing about your project except what you tell us. The fact that all of us are trying to understand the difference between what you call an "event" and "show" means there must be some importance in it. Also, you mentioned in a previous post that tax is related to:

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?

I'm going to run with this and see if i can integrate this information with what we already know. But it would really help if we could also see some example data from tblShows and tblEvents.

1) "OFAST - 4 2015" i am assuming this is what you are calling a "show" but this does not make sense because your tblShows does not have any date fields, it is your tblEvents that has dates. The way you already have your tables and relationships designed: the 'show' would be "OFAST", but the 'april 2015' is one of the show's "events" (you said earlier your shows have multiple events: this is what your relationship shows us in your screenshot too) - and you have start and end dates in tblEvents, so this would makes sense. so, sounds like you take a show and apply dates to it so that the show has date limitations to make an event of the show. so, your EventID in tblShowSales should actually have a direct relationship with the EventID in tblEvents.

2) "that year" - meaning the tax depends on the event (because an event has date limitations in tblEvents), so a tax field for your events should be in tblEvents. tblEvents has a ShowID, which is already link it to tblShows, so access will know which "show" (name) you mean when you select a particular "event" (dates).

3) Also, i note that your expenses (tblShowCosts) comes off tblEvents - this is what you should do with your incoming (tblShowSales), as i mentioned in (1).

4) Once you do that, certain queries will make it easy to apply the correct tax percentage to your prices - we can help you further there. but first you need to do the base work in your relationships and tables.

5) As has been mentioned, the word "date" (which you have in tblShowSales, and is a little off-topic) is a 'reserved word' meaning that that word is special in access for it to work properly and using that word to name your field will cause headaches in future. RainLover is right, you should google 'reserved words' to gain a better understanding of them and also to learn what other words are reserved. We are mentioning this because we have all, at one point or another, suffered from naively using a reserved word, and we want to spare you the same.

 

[wiklendt]

after sleeping on it and considering all information on hand, i have fashioned a solution for you based on your current design. however, be warned that your current design has confused even the experts on this forum and i had troubles with a basic mock-up (only the fields i needed) of your design. I have also made MANY assumptions, some of which i know will not match your current data - remembering we have seen none of your table designs.

This is a query SQL that you can make by creating a query in design view, then switch to "SQL" view and paste this directly in. if you get a mismatch error, this mean you have text on one side and a number on the other side of a relationship and you will have to amend your table structure and/or data to make it work. There is much that does not make logical sense in your structure and i had to deviate from good design practices to get even this basic calculation to display.

I did both calculations because i'm not sure exactly which you want. i suspect you want TaxPart, but now you have both.

SELECT tblShowSales.SaleID, tblShowSales.Date, tblItems.Model, tblItems.Price, tblEvents.StartDate, tblEvents.EndDate, tblShowCosts.TaxRate, [Price]*(1+[TaxRate]/100) AS TaxUp, [Price]-5/100 AS TaxPart
FROM ((tblItems INNER JOIN tblShowSales ON tblItems.ModelID = tblShowSales.Item) INNER JOIN tblShows ON tblShowSales.EventID = tblShows.ShowName) INNER JOIN (tblEvents INNER JOIN tblShowCosts ON tblEvents.EventID = tblShowCosts.SCEventID) ON tblShows.ShowID = tblEvents.ShowID
WHERE (((tblEvents.StartDate)<[Date]) AND ((tblEvents.EndDate)>[Date]));

(Edit: sorry, that should be "TaxPart: [Price]-[TaxRate]/100", so that the taxrate can change with the recods)

DISCLAIMER: i tested this on ONE record only. i don't know how it will behave on multiple records. also, you will probably want to make a form to display which event you want to limit the dates by.

 

[wiklendt]

Or, instead of limiting by date you could try limiting by eventid.

 

[GaryPanic]

Show = main Event
Event - is within a Show

So
Glastonbury is the Show
and the circus is an event within the show

a show being the main entity and there is only one- events are within that Show

Show:- Glastonbury Festival ( the whole )
events:- part of the whole

on a financial breakdown - the Show would have a budget of say 1,000,000
and the events would form part of this
event 1 -500,000
event 2- 250,000
event 3 - 150,000
event 4 -100,000
total 1,000,0000 (being the Show budget)

Still doesn't help on the tax -
If the tax is per show ( which would be weird) - as it usually per time frame (tax in 2015 is 8%) then set the tax rate to the Show and have your child form look at this.

but it would be better to have your tax at event tbl stage then you can change the tax rate when it goes up or down from date x onwards

so if the tax increases 01.09.15 from 6 to 10% then everything invoice after that date should have that tax - you would only need in this instance to record a tax rate and this would be 1 field and a default value of xx

 

[RainLover]

Or, instead of limiting by date you could try limiting by eventid.

Based upon what Gary said it would appear wiklendt has proposed a good solution to at least part of the problem.

 

[GaryPanic]

Given that you are USA based -
is there multi tax or different tax per state

So will your "boots " have a federal tax and also a state tax ? -
My knowledge on USA tax is zero
and does the tax apply to your you are based or where you sell the items

Tax is always a bitch to get right
are you selling on line then is the tax applicable to where the buyer lives ? (that's a new one for Europe - not sure when it comes in to effect )
you may need to have multi tax drop downs - or product code and tax applicable

example
Boots = code 001 if tax state = x then rate = y
boots 001= New York = Rate rate to apply = 7.5%
Boots 001 = Pennsylvania = Rate to apply = 4%

that will involve something slight different (Dlookup ) and drop downs from statelist box etc..

 

[wiklendt]

but it would be better to have your tax at event tbl stage then you can change the tax rate when it goes up or down from date x onwards

Yes, this is what i have been saying numerous times. However, something i noticed when i was doing the mock-up (based on OPs relationship view screenshot), and also from the OPs very first post, is that the tax rate he has listed in tblShowCosts - which appears to be a 1-1 relationship with tblEvents (which i can only assume is true because both tables' primary keys are the ones that are on either side of that relationship).

That said, i still believe that the tblShowSales should be directly linked to tblEvents rather than tblShows - however, when i was doing the mock-up, the PK and FK were all over the place (EventID != EventID between tables) and, i suspect, probably not autonumber.

I read somewhere (not this thread) an idea to name ID fields as, say EventPK and EventFK, depending on whether the EventID field is the primary or foreign key. Just like to mention that i like that idea.

 

[RainLover]

I read somewhere (not this thread) an idea to name ID fields as, say EventPK and EventFK, depending on whether the EventID field is the primary or foreign key. Just like to mention that i like that idea.

You can find a copy of that in our archives.

It is under "Naming Conventions."

I am glad you like it because I am the Author.

 

[GaryPanic]

Yeap - good practice to do this - (I wish I followed this myself - )

If the poster is hell bent on sticking to having the tax on the show - then Dlookup is the route
tax= dlookup xx on show table ...
but this just doesn't sound the right way to do stuff

Tax is a bitch - easy when you dealing with one tax rate - but having multiple taxes (federal/local) plus stamps duties /levies

- we deal in multiple countries so have all of the european taxes +Oz/NZ etc
so taxes are a nightmare