Results 1 to 10 of 10
  1. #1
    rxmaman is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    5

    How to design database for user selects a date to run an event and the database resets for next time

    I'm designing a database for a racetrack. Once a week, the user will enter drivers, race lineups and race results. This data needs to be saved for yearly reports. But, I want the database to "blank out" all fields in all forms for the following week (new date, new results etc....). Currently, I have a "Race management" navigation form with subforms to enter all data, including the date. I feel like I'm missing a step such as: first select date, then race management form opens and all forms will default to the original date selected. Am I close?

    I am very new at this. Learning everything from online searches. Thanks for all your help.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,127
    I want the database to "blank out" all fields in all forms for the following week
    This sounds like you've based your form(s) on a table, which is something I rarely do. Basing them on a query allows you to restrict displayed records according to your needs, otherwise you load an entire table each time the form opens, unless you apply a filter on opening. If you're going to do that, then why not just use a query? I don't use navigation forms, but whatever form you're opening from there, they can make things more complicated when you attempt to pass values to an opening form. Usually, the reference to the nav form that contains the filter value isn't correct, which is one reason I don't use them (the other being that you can only have one open at a time), but you didn't post anything that really explains the process you're using. If that isn't the case here, then I don't understand what the issue really is.

    If you want all subsequent forms to be be able to know a chosen date, then you need a way to a) pass this value from one place to another or b) write it to a global variable or a table and reference it from there when those forms open.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    rxmaman is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    5
    Quote Originally Posted by Micron View Post
    This sounds like you've based your form(s) on a table, which is something I rarely do. Basing them on a query allows you to restrict displayed records according to your needs, otherwise you load an entire table each time the form opens, unless you apply a filter on opening. If you're going to do that, then why not just use a query? I don't use navigation forms, but whatever form you're opening from there, they can make things more complicated when you attempt to pass values to an opening form. Usually, the reference to the nav form that contains the filter value isn't correct, which is one reason I don't use them (the other being that you can only have one open at a time), but you didn't post anything that really explains the process you're using. If that isn't the case here, then I don't understand what the issue really is.

    If you want all subsequent forms to be be able to know a chosen date, then you need a way to a) pass this value from one place to another or b) write it to a global variable or a table and reference it from there when those forms open.
    I understood most of what you said lol. Basically, my design is like this (because that's all I know.....)
    Click image for larger version. 

Name:	Race mgt nav page.jpg 
Views:	35 
Size:	55.1 KB 
ID:	34462

    Each car class tab is based on a query to show only cars in that class. In driver registration, the box is checked when the driver registers. My next step was to do a query to select only drivers that registered and another query to do the heat lineups based on the track rules.

    I basically want the user to do this: select the date from the track season schedule. Forms shown in pic above would open. Drivers get registered in each class. Once that is done, the user clicks the Heat Lineup tab. Upon selection of the car class, a query runs to lineup the cars for racing according to track rules. Then the user selects the heat results and enters the results. Then, the user selects the Feature Lineup tab and a query is built to lineup the cars in two rows based on the track rules and so on. The next week, we start all over again.

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    You should have a main form with general information about the race, like Track/Race name, Race Date, maybe some other basic information like Location, Track Length, etc. If this is all for a single race track then I suppose you can just use Race Date. All of your other forms should be sub forms of this main form. As micron suggested, this should be based on a query, one that uses TrackName (or RaceName) and RaceDate as criteria. When the user enters a new RaceDate, then all forms would load (or requery) empty, ready for the user to enter new data.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,127
    I probably wouldn't have started out on this nav forms path, but since you have gone this far, I don't necessarily agree with starting over. After re-reading your 1st post, it seems that what you're missing is a date field in your query/queries. My current thinking is that when these forms are populated, they should contain a date field that gets its value from wherever this is entered at the start of the process. This query date field would cause the forms to open blank (I think that's what you're asking for) whenever the date provided doesn't match any search date provided, or if no date is provided. Without being able to see behind the picture, I can't say where I'd put this. Probably, the issue of how to pass a date from one form to another can be easily solved if the beginning of your process can store the date in a table, and the query underlying each subsequent form can make use of it. If you want to compact/repair your db and zip a copy of it into a reply, I and maybe others will take a look at it. Provide instructions on how to operate the db.

  6. #6
    rxmaman is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    5
    Thanks everyone. Here's my dilemma, as an example: My Comp 4 registration form is based on a query that selects Comp 4 drivers from the Drivers table. Therefore, I don't know if it's possible to base it on 2 queries; one being the event date?
    I've spent countless hours working on this because I love a challenge. I'm just teaching myself from online forums, youtube etc.... If Navigation forms are not the way to go, please suggest another way - I'm open to starting over. I'm attaching my database for your kind review and comments. I basically would like it to look like this (and, it would be extra sweet to figure out how to double-click on a driver and move it to the entry table. I tried using a Make Table query by adding a command button to create an "entrants" table with the intention of using that to create lineups. It could work....I think....but I'm still stuck with tying registration, heat lineups, etc...to an event date as previously posted.Click image for larger version. 

Name:	Driver registration screen shot.jpg 
Views:	26 
Size:	36.1 KB 
ID:	34506 I would like to attaching my zipped database for your kind review and suggestions but don't know how

  7. #7
    rxmaman is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    5

    Here's my database

    I hope this works. I followed instructions from Micron on a separate thread. It looks funny. Database event setup test.zip

    Click image for larger version. 

Name:	attaching files on forum.jpg 
Views:	25 
Size:	79.8 KB 
ID:	34508

  8. #8
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    I have looked at your db. You have some table design/structure problems that really need to be resolved before you spend more time on the design of any other objects (queries, forms, reports). You have to get the foundation right before you build the house. I don't have time to get too deep into it right now (at work), but i will post back. You may get response from others here as well in the meantime.

  9. #9
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Some thoughts about your table structure. As I see it, you have the following entities (once you clearly define your entities, those become your tables);

    Events - This would be data about the event itself, like EventName, Date/Time, maybe location if you track data for multiple tracks

    Races - This would be the Heats and Feature races that you are currently storing in separate tables. I don't see a need for that, you just need a field to indicate what type of race it is.

    Drivers - This would be both Drivers and Owners. You would use a couple of Boolean (Yes/No) fields to indicate if a person is a Driver, an Owner or both. The structure here might vary somewhat depending on if you have race teams, or just individual Owners/Drivers.

    Classes - The different types of race classes

    Cars - General info about the car, including what Class it belongs to.

    Sponsors - Info about sponsors.

    Points Schemes - I don't see a need for two different point scheme tables like you have now (one for Feature races and one for Heats). This info can all be stored in the same table. It's even possible this table isn't necessary and can be incorporated somewhere else but I'm not sure right now because I haven't analyzed it that much yet.

    Other Entities I'm not sure about;

    Payouts - I know what a Payout is, just not sure if it needs it's own table or can be incorporated into something else.

    Run Types - Not clear on what this represents, so I can't say if it needs it's own table.


    Then you have the relationships. Here are my initial thoughts on that.

    Drivers <> Cars The only direct relationship I see for Owners/Drivers is with the Cars. The Car is what gets entered into a Race. Who the Owner/Driver is is just ancillary data that can be obtained through the overall hierarchy of relationships. This relationship is Many-to-Many. An Owner/Driver can have many cars, and a Car can be driven (or owned) by many people. So, you need a junction table here.

    Classes >> Cars This is One-to-Many. A Class can have many Cars but a Car can only belong to one Class (I think, could be wrong about that).

    Races <> Cars Again, M-to-M so you need a junction table. By the way, the junction table here is where you would store info about the finishing results for each Car in each Race. I'm thinking you would also relate your Point Scheme table to this junction table, but I would have to think that one through a little more.

    Events >> Races An Event can have many Races (Heats or Features), but a given Race can only be related to one Event.

    Sponsors <> ??? Question mark here because I don't know if they are sponsoring the Event itself, or individual Owners/Drivers within the Event. Maybe both. Either way I see it as M-to-M.

    I can put together a working example so you can see some of these concepts in action, but don't want to spend time on that unless you indicate that you want to continue down this path (i.e. the path of you taking a few steps back and correcting the structure so your application will ultimately give you the results you want).

  10. #10
    rxmaman is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    5
    Thanks Sean. I'm all for learning the basics - self-learning is great but coaching is essential. Please let me know how I can improve

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Accessing Database by multiple user at a time
    By prasadmng555 in forum Programming
    Replies: 3
    Last Post: 11-23-2016, 08:29 AM
  2. Employee Time Tracking Database Design
    By shell159 in forum Database Design
    Replies: 1
    Last Post: 01-12-2016, 02:58 PM
  3. Replies: 2
    Last Post: 03-18-2015, 07:34 PM
  4. Database Design: Normalize daily scheduled time
    By Lorlai in forum Database Design
    Replies: 1
    Last Post: 03-12-2013, 12:26 PM
  5. Design multi user database - avoid simultaneity transactions
    By snoopy2003 in forum Database Design
    Replies: 2
    Last Post: 02-27-2011, 12:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums