Form for large quantity of information, several tables, one form

[RankSinatra]

I'm having an interesting issue. What I need is to track inventory of tickets by starting/ending numbers. I would have one field for starting out, ending out, the calculation of issued (ending out - starting out + 1), the starting returned, ending returned, calculation for that total (also ending - starting + 1), and the total usage (issued - returned). That's already 7 fields, and I want to have 10 of these I can keep track of per cashier. Couple that with the different types of tickets that can be sold, and I'd have a table with over 500 fields. That can't be the best way, and our work computers are slow enough, so I want to split up the tables. How do I maintain the relationship between the different types of tickets but still all related to the one cashier as if one record? I'm asking this on the form forum because I ultimately want to type in usage for all tickets per cashier and move between each cash register's sales with the record selectors.

[June7]

So have an 8th field for CashierID and maybe 9th field for TicketType.

[John_G]

You need to rethink the structure of your data. Your statement "...I want to have 10 of these I can keep track of per cashier." indicates that you have a repeating set of data for each cashier. When you have this situation, you don't want to have all the groups in the same record; you data is then not properly normalized. If you are unfamiliar with that term, there are many good references that can explain it for you (I'll look a few up for you). Then, you have more complexity with "Couple that with the different types of tickets that can be sold...", but the issue is the same.

You need to have another table, where each record contains information for only one set of tickets, perhaps something like this:

CashierID
Sale_Date
Ticket_Type
Group_ID - this identifies one of the "10 of these for each cashier", but you didn't say what it actually is
Starting_Out
Ending_Out
Starting_In
Ending_In

Notice I didn't include any of the three calculated fields. You don't need them in the table; you can always calculate them in a query when you need to.

How do I maintain the relationship between the different types of tickets but still all related to the one cashier as if one record?

You do that by including cashier ID and ticket type in the detail table as I indicated above.

Because you have a one-to-many relationship (one cashier can have many ticket sales records), you will prbably want to look at using a form - subform arrangement.

Your description in your original post is a little ambiguous, so if you can provide a bit more detail on how your process works, we may want to revise your data structure a bit.

[isladogs]

You need to rethink the structure of your data.

@RankSinatra
Unless of course, you want to "do it my way"

[Missinglinq]

Your thread title, itself, bolsters John's suggestion that you need to be using a Form/Subform construct, here. One of the major rules of thumb, in relational databases, is that the presence of any Table with more than 20-25 Fields is generally a sign that the database is non-normalized.

@RankSinatra
Unless of course, you want to "do it my way"

You're telling your age now, ridders!

Linq ;0)>

[RankSinatra]

Yeah I've been looking into normalization. Not really understanding how data can be more or less normalized comparatively. What I'm trying to do is replace our excel spreadsheet with a database so I can run reports for monthly usage instead of having to pull up each day's spreadsheet, copy the usage into another spreadsheet, 30 times. The database also keeps track of the financial side too. I got that working with a form/subform auto updating the department with a combobox. I'm just trying to figure out how access can keep track of data of this magnitude. I'm going to have a different table for each classification of wristband: Admissions Over 48, Admissions Under 48, All Rides Over 48, All rides Under 48, Weekend Passes, Spring Break Passes, VIPs, and Tickets. I was going to do the calculations on usage for each one and then just pull those calcs into a query that relates them all by Attendant Name, Operating Date, and Chosen Colors for the operating day (Admissions Over and Under are one color per day, All Rides Over and Under are one color, the rest have no color specification.) Again, I want to be able to balance the entire wristbands for each attendant on one form, the same as it is on the attached screenshot of our spreadsheet (the actual .xls I can't upload here) and just move between them with record selectors without straining the CPU too much from the hundreds of fields. Is it possible with separated tables?

[isladogs]

You're telling your age now, ridders!

Linq ;0)>

Maybe I'm the real missing link from millions of years ago ...

[John_G]

Here are some links to information on normalization.

A big thank you to forum user ssanfu who posted them in another thread:

Some light reading......

=======================
Normalization Terms and Concepts http://www.utteraccess.com/wiki/Norm...s_and_Concepts
Normalization http://www.utteraccess.com/wiki//Normalization



What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html

[RankSinatra]

Thanks for all of your input, I settled on a series of tables, calculation querys, and subform. The problem is making the subform requery.
Main form is AdmOEntryCalcs. Trying to make subform AdmOTicketVar requery whenever any text box is changed on the main form. Seen lots of answers for this, none of which worked. I'm assuming I'm typing the vba in wrong or in the wrong place. Tried requery and recordsource.

[RankSinatra]

This subform will not change no matter what I do except closing and opening the form. AfterUpdate of text box "AdmOver" set recordsource of subform "AdmOTicketVar" to it's underlying query "Adm+TicketVar" (Yes I know the plus sign is a huge mistake in naming but I'm kind of locked into it now and it does work as long as I'm careful and use brackets or quotes.)
Then requery the subform object. The object has the same name as the actual subform, AdmOTicketVar.
Some things I've done have made the subform flicker a little, so I guess it's requerying, but the actual calculations in it won't update for anything.

Private Sub AdmOver_AfterUpdate()
Forms![AdmOSalesEntry]![AdmOTicketVar].Form.RecordSource = "Adm+TicketVar"
Forms![AdmOSalesEntry]![AdmOTicketVar].Requery
End Sub

All these codes I lift from other forums/questions and none of them work. What am I doing wrong?

[ssanfu]

Have you set the linking fields for the main form/sub form?

Post your dB for analysis........

[John_G]

Try:

Forms![AdmOSalesEntry]![AdmOTicketVar].form.Requery

If that doesn't work, there might be something wrong with your query [Adm+TicketVar].

Does it reference the form field [AdmOver] in its criteria?

[RankSinatra]

I'm afraid the actual file is irreducibly complex, I can't strip it down enough to make it small enough that I could upload it. I need a database where a subform refreshes every time the record updates/a text box is filled in, etc. Anything at all I can reference. I would make a database I could upload but I'm at work and this is currently my only access to the internet.
The parent/child both reference the same data from the main form (Employee first/last name, Operational Date, Bag Number, etc. That's it - they both match on the fields that link them. Should I put reference to the text box in the subform's child reference?
I put that code in the afterupdate of the text box under code builder and it did nothing, unfortunately. No error, no flicker, etc. The query underneath utilizes a totals sum for one of the fields, is that the issue? I know I couldn't write new records before, but it does still update when I close/open the form again.

[June7]

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

Why would the subform need to change when data entered into ANY textbox on main form? That is unusual.

Saving calculated data is usually not advisable.

If you want to provide db for analysis, follow instructions at bottom of my post.

[RankSinatra]

CashroomDatabase.zip

Compact and repair is pretty cool, didn't know that was a thing.
This is a very extensive database, I deleted a lot of the fluff and now you have the incredible mess that is our wristband balance. The form is very large, each text box needs to update that final TotalVar subform under each classification of wristband. The various text boxes all factor into the final variance equation. I'm not storing calculations in a table, but they run under the query. I need to be able to view each employees ticket usage and variance at the end of every month via report, that's why.

Sorry about the disorganized naming conventions, I didn't plan this thing out right. First database.

Look for the form WristbandBalance. Ideally every text box under issued, returned, and the sales will update the sub-subform ticketvar. Database has a backend as well.