stumped beginner

[cooldjmac]

I created my first database for a festival in michigan to use to track money (how many 1's, 5's, etc..) and by area, booth and product which worked out great for the festival 2007. Now they want to use it for festival in florida.

I'm curious if anyone knows how to add some admin specific data. My idea is on a daily basis the admin will set some settings that will be applied to all transactions: (location, temperature, weather, head count) which these four items would be included in the report that is run at the end of the day and attached to each transaction.

Could I get some help/direction on how to make sense of the mess I created? I have a month to come up with a solution that works so they can track overall transactions and transactions by location, day, area, booth, product... YIKES!

Festival_v3.0.zip

[cooldjmac]

I should tell you what I have been trying to do.

In the morning prior to festival open the admin would set location, temperature, weather, headcount (I imagine these would be global settings but not sure).

All areas, booths, transactions would have the locationid preceding it so when the users click on "Enter Transactions" it would somehow filter based on the location id. So only the areas, booths and transactions with the admin set location will show up.

Then reporting would also be set to select the date, location, etc.. to run the report.

Eventually I would like to be able to run comparisons but not important right now.

Am I crazy to think this is possible?

[Cortical]

Create a new table to hold location, date, temp.... i.e. on the day data. No, the would not be global data, as having entered the data, you may as well keep it as a historical record, and more importantly, you will want to use it for reports etc. downstream.

All routine stuff. I would be approaching it with dedicated tables for, locations, (Areas), booths. transactions

[cooldjmac]

Thank You for your reply and suggestion.

I currently do have Areas, Booths and Products in all there own tables. With your suggestion I created an additional table called ontheday with fields such as (date,weather,temp,headcount and most importantly.. location).

Now I am curious on how I create the realtionship so when transactions are entered they automatically get the applied location, temp, weather (currently using the auto set for date).

I am hoping I could create a report looking for the location setting set in the report script.

Anyone can help with setting up the relationship? Do I need to create a relationship for area, booth and transaction or just transaction? Is the transaction table the primary key or would it have the foreign key and the ontheday table would have the primary key?

Sorry... relationship still confuse me

[Fenton]

Let's assume that the new records in the tables, mostly Transactions, will always be entering values from the last record in your administrative Festivals (date, location, etc.) table. In other words, you'd only have 1 Festival going on at any one time, and it would be entered last in that table, just before the Festival started.

You would also need a Constant (calculation, =1) relationship to the Festival table, from whatever table, 1=1. Sort the relationship by Date descending.

Then, your various fields could be auto-enter, by calculation, using:

Festival_desc::Date

Festival_desc::Location

etc.

Edited January 21, 2008 by Guest

[Cortical]

you don't actually need to create fields for date, weather etc. in the Tn tbl, just the Location_ID.

Create a rel from TnTBL to Location/Events TBL using Location_ID, then you can add the related Location fields to the Transactions layout, and once the Location_ID is hard coded (entered) into the TN tbl location_ID field, the related date etc. values will display.

There are options as to how you enter the Location_ID value into the Transaction record. One way is as an auto enter last as Fenton suggested. Another way, and as you are a beginner, may be worthwhile implementing to introduce you to a very basic, and very versatile technique, is to create a value list of the Locations (L_ID) and attach the VL to the location_ID field in TN TBL. That way you can manually select the Location each TN record.

The VL would require a relationship using constant fields, create a field _constant a calc = 1, number result (or an auto enter calc ) indexed in both tables.

Then define the VL to use Location_ID as the first field, and Location as the second field. This will display both values in the popup list attached to the Location_ID field, but only enter the actual ID.

Attach the VL to the Location_ID field. You will need to add the related Loaction field to the layout to dispaly the Location if required.

The auto enter Fenton suggested, is more efficient; make sure the relationship is defined sort date (Location date) descending.

But the VL will give you some versatility, and teach you a fundamental technique which you can then apply to some of the other fields. Both are simple and quick.