Rudementary form & table questions. NOOB! Be gentle... (1 Viewer)

I'll start with the obligatory I'm sorry that I'm new here, I'm really sorry if I posted in the wrong place and I'm really really sorry that I even have to ask for help, so let the berating begin.


So, I haven't used Access in some time, very rusty but I have a pretty good handle on the entry level usage of forms / tables. I am working on a project and I'm truly stuck. I tend to over think everything that I do so if you respond, please explain in simple terms.



I am attempting to build a project that does the following (it's a check deposit record, but not like a 'checkbook')

1- I may have one, or many bank deposits on any given day.

2- Each of those deposits may be a direct, scan, or manual deposit.

3- I may have 1, or 20 checks for each said deposit.

4- There are 5 different departments that each check may be distributed for.



I have the tables built for each department, the 3 types of deposits. I also have tables built for the deposit entry (like a 'main' form) and then a table that houses the check number, amount, reference (text.) The department and type are both lookup, which that works just fine.



The larger issues:

1- I am unsure how to link the 'main deposit" to the one, or multiple checks
2- When I enter the data in from the form, i.e. check #, amount, etc it does populate to the table.

3- When that deposit is "done", I am unsure how to start a fresh 'detail' entry for that one 'main' deposit.



In short, one record is the "main" deposit, and that main deposit may link to one, two or fifty 'deposit details". But then the next "main" deposit is blank in reference to the old checks that were entered.



I'm 75% there, and hopefully my questions do make sense. I really do appreciate any input and thank you in advance.


I'm sensitive. Don't yell, I'll run and hide then...

Bearing in mind my signature, I would probably do something like this.

Have a table/form that holds the details of the deposit with data like, date, branch etc.
Then I would have a table/datasheet subform that holds the deposit details, chq, cash, amount etc.

The main deposit table will be the parent, the table/datasheet for the deposits the child, linked by the Autonumber key in the main table.
I would only have one table for departments and a field to identify each one.

Knowing what I know now, I would not use Access built in lookup fields, but have my own manual Lookup tables if needed and the data is not available elsewhere.

I would not store the total of the deposits on the main form, but calculate it from the records in the Deposits table that are linked to the record on the Main form.

HTH