IIf statement with dates

[Mile-O]

quick question, when it says for instance, ShowID (Foreign Key, Number) and ShowID (Primary Key, Autonumber) do I just have it get the ShowID, or the show name? And if so, how would some one know what the ID is to the ShowName?

You only need the ID. The relationships in the database allow you to join between the tables, using these keys, to get the data from other tables.

That is like our table size for each show, 10X10 booth, its really booth size, not table.

Do you take your own tables of that size to fit a booth, or is that the size of the booth you request at the events? If the latter, then it's more than likely a Cost, that goes into the Costs table mentioned, perhaps with a separate Cost Types table, so that things like Booths, Vehicle Rent, meals, whatever can be logged.

Also, what is the difference between "Event" and "Show??"

If you look down your existing database, you had Cabool Farm Fest three times, once per year 2012 to 2014. You are treating each one as a Show.

However, the way I see it, is that Cabool Farm Fest (in general) is a Show that you do, and there were three instances of this show...and I'm calling these instances Events.

Part of the idea of data in a database - or the first rule, anyway - is that data in each cell be atomic....at the lowest it can possible go. No storing things like Rod, Jane, and Fred. in one field. Or apples, bananas, and carrots. You would have a separate record for 'Rod', 'Jane', and 'Fred'. Ditto those 'apples', 'bananas', and 'carrots'.

Applying that to your data, you have:

Cabool Farm Fest 05-2012
Cabool Farm Fest 05-2013
Cabool Farm Fest 05-2014​

What we have here is two elements of data in a single field. The Show itself and the date that it is taking place. However, there's no need to store that date in the name of the show as you already have fields called StartDate and EndDate, which tell us the times of the show. If need be, this date can be concatenated in a query...

...check out the attachement for a more visual example with your own data.

 

[Infinite]

Well, I was looking at your table, and the thing is, we would need to have the show dates in the names, because we have a giant ledger that we input our items sold, with the date, gross sales, tax, etc, etc, and it has the show name, so if we were to have a drop down menu with the show names, we couldn't tell if Cabool farm fest sold 100 items on the show of 2013, or if it sold 200 items on 2014.

 

[Mile-O]

Well, I was looking at your table, and the thing is, we would need to have the show dates in the names, because we have a giant ledger that we input our items sold, with the date, gross sales, tax, etc, etc, and it has the show name, so if we were to have a drop down menu with the show names, we couldn't tell if Cabool farm fest sold 100 items on the show of 2013, or if it sold 200 items on 2014.

Look at qryEvents in the sample. It has a field that I mocked up called ShowName2. It does what you want. Because, in the events table, you have the show name and you have the event dates, you have greater flexibility.

Hopefully you are using a form to enter this stuff into your ledger.and not directly into the table.

 

[Infinite]

Yes and no, I use a table to input the date, because the program we use for our sales at shows (cash and credit) I can just import it onto, without doing each sale, now for shows, doing it with a form would work just fine.

 

[Infinite]

What IS the difference between a table and a form? I know I should use a form to enter the info, but why?

 

[Infinite]

And how would I get the shows to sort oldest to newest, with the EventID to be in order of 1-137?

 

[Infinite]

Sent you a PM message.

 

[Mile-O]

What IS the difference between a table and a form? I know I should use a form to enter the info, but why?

Right, back to basics....

A table is where the data is stored. It contains only the data that it needs to help. It usually represents an entity of your business. ALL information in the fields of that table should be dependent on your primary field (your unique identifier). In these cases, I just use the Autonumber, as a unique ID for each entity.

A table only stores data. It does not do calculations. It does not store the results of calculations. Storing the results of calculations is a general no no. (If you stored fields called price, quantity, and total, where price was $5, quantity was 10, and stored total was $50, then changing the price in the database to $6, would result in a price of $6, a quantity of 10, and a total of $50, which would be incorrect, as we know the total should be $60.

Also, a table has rows and rows of data, although they are not in any type of order. Although it may look like they are, because our keys are somewhat consecutive, they aren't really. Access doesn't care two hoots about which order the data is in the tables. Row #1 may be in row #1 one time, another time that same row could be row #19,214. It just doesn't matter.

So say hello to queries. Queries are where we can sort the table data and do any calculations we need. Also, importantly, it lets us join our tables together in order better present our data. That's why we use these foreign keys to link our tables...

So, referring back to the confusion between events and shows. We have a table for events - these are one-off fixed points in time. Many events can only relate to a single show. However, many shows can relate to an event. What we have here is a one-to-many relationship.

We could painfully put the name of each show in our events table, but what happens if the 'Cabool Farm Fest' becomes the 'Cabool County Farm Festival'? We can change each record in the events table where the text is stored (bad practice) or we put a marker in our events table to another table called Shows where we only store the individual name of each show. Then, when we want to change the name of the festival, we need only do it once, and every pointer to that record is immediately updated. (There's also the storage benefit of using numbers over text as keys.)

In our query we could also solve the issue of Price and Quantity not equalling Total. As Total is a calculation, we don't need a field for it. It never needs stored. Instead, we just say something like Total: [Price] * [Quantity] and it is calculated live (at run-time) each time. No errors.

But where can we represent these queries? That's where Forms come in. These are the user interface for your database. Here your user will enter data into your database or view that data. They shouldn't be able to get into the tables. That way madness lies, and users can, for example, bypass any data validation your forms may have, delete items from fields that may upset the relational integrity of your tables (deleting the value from a foreign key field, say, so queries can't properly connect), and so on. Hell, given access to your tables and five minutes to spare they could probably end up deleting them from the database completely...!!!

There are also reports, which can present your information in a summarised way for printing, too.

So, with all this in mind, time to check out my next little example, of a user interface over your data, and a quick ability to add or edit events.

 

[jdraw]

Did you work through the tutorial I suggested?

 

[Infinite]

I have been trying to, but we have a show today and a 2nd one tomorrow, so some time in the next week I should be able to.

 

[Infinite]

Hey Miles, Thank you a TON! I am actually going to (If you dont mind) use your table you posted for the ledger. If not, that is understandable. I have made some changes, and the "Find" Button doesnt work any more, any idea why?


(I sound like a 5 year old. Mom! Its broken! Fix it!

 

[Mile-O]

Hey Miles, Thank you a TON! I am actually going to (If you dont mind) use your table you posted for the ledger. If not, that is understandable. I have made some changes, and the "Find" Button doesnt work any more, any idea why?

I don't mind.

Still, we need to get those tables defined properly. So, I suppose we need to understand what all the monetary fields are about, etc.

 

[Infinite]

ok, I have added, removed, modified, etc, etc this report. Here ya go.

Tell me what you think, what should change, stuff like that. Thanks!

 

[Mile-O]

I was just writing reply here on tables for Costs, CostCategories, and CostsToEvents, however I accidentally pressed something and the web page changed, losing what I'd written. Damn.

I'll need to write it again.

The gist was that the costs seems that they can be split into income and expenditure. Also, are the tax fields calculated in some way based off these other amounts?



On the Find button not working, you had renamed it from cmdFind to cmdFin.

You've also changed the combo in the events for to use qryEvents rather than qryComboShows, which is what it should be. The ControlSource of the other textboxes for H/A and I/O should be columns 3 and 4 respectively. The cboShows ColumnCount property would need to be extended to 5 rather than 3, and the extra widths added, so that the property reads 0;2cm;0;0;0

Finally, in your original data, there was the 'Apple Butter Makin Mt Vernon' show, with events in 2012, 2013, and 2014. 2013 was marked as Home, with the other years as Away. If this is NOT a data input error, and this CAN happen, then the H/A and I/O fields likely need moved from tblShows to tblEvents.

 

[Infinite]

Finally, in your original data, there was the 'Apple Butter Makin Mt Vernon' show, with events in 2012, 2013, and 2014. 2013 was marked as Home, with the other years as Away. If this is NOT a data input error, and this CAN happen, then the H/A and I/O fields likely need moved from tblShows to tblEvents.

No, the thing is, for that show, we did it 2 years, and on the 3rd year, we had a friend do it. For us, it was away, but they were closer, so it was home, but I have changed that on all records so its all away, because the show is not "Home"

 

[Infinite]

One more thing, I have that next button, and when I open the events form by itself, I can keep going next, but when it opens through the shows tab, it doesnt work. And idea why?

 

[Mile-O]

One more thing, I have that next button, and when I open the events form by itself, I can keep going next, but when it opens through the shows tab, it doesnt work. And idea why?

'Next' button? I don't recall putting one in my example. Is this a button that you've added, or have you switched on Navigation Buttons to your form?

 

[Infinite]

No, I added it. If I open frmMenu and then double click on a show, then I cant go next.
But, if I open frmEvents by itself I can then go next all I want.

I also have more text boxes with booth cost, etc, etc, but, there not showing me the $ sign. They only show like 250, not $250. And yes, I have it set to currency on the text box, and the query im pulling the info from, and the table the query is pulling the info from.

One more thing, how can I make it so I can edit the info from the form? Or is that a no-no?