noob.. DB for storing multiple lines in to one record

[wmarke]

I seem to over-think and then I get stuck.. I'm working on a rather 'simple' access DB.

What I am trying to accomplish- every day, and sometimes multiple times a day we make bank deposits. There may be one check, or 20. Also, there are multiple departments that they get assigned to.

The foundation is going well, but where I am getting stuck is;
1- How would I be able to store, multiple lines of data in to one record for that particular deposit.
i.e; the fields for each check would be : Department, amount, check number and reference.

But all kept in one record...

[nick404]

You do not want to have multiple things in one field; this is not what they call "normalized."
Add more fields to make each record more specific.
What pieces of information are needing multiple lines?

[June7]

Why is an incoming check split up by department? That would not happen in a conventional accounting system.

[nick404]

I do not understand what your problem is? If those are the fields you need for each check, then in the table's design put those fields in and use an autonumber as the primary key (based on the fields you gave I do not think any of them will be unique to other entries). Then you can enter in all those things as one line ('record') of data for the check.

[orange]

wmarke,

You should really do some tutorials on database concepts to understand what you are trying to do.
Access is not intuitive. Access is a tool, but you have to know something about database to use the tool.
see the links in this post for more info

Good luck.

[cbende2]

"Multiple lines in one record"

What you're looking for is a 1 to many relationship table.

One Deposit could then have many Checks.

Do a little research on 1 to Many relationships, and you should be able to solve your problem.

[wmarke]

Sorry I was triple tasking while writing the post and wasn't very clear. Not a good multitasker! So, no, we are not splitting a payment in to two different accounts..

Essentially, here is what the "report" that I am trying to get at the end of the day..

DEPT A
CHECK #12345 INV#56789 JOE BLOW COMPANY $500.00
TOTAL $500.00
DEPT B
CHECK #5252 INV#10101 LARRY FINE COMPANY $1500.00
TOTAL $1500.00

TOTAL DEPOSIT $2000.00


And that "deposit" could be recorded as a record if you will, so we can go back and review, for any reason.

I also would like the report to generate a PDF version, which is not a problem for me to do.


thanks!!

[June7]

I still don't understand a system that assigns incoming funds to a specific department. But if you must, then have a field in table to select department ID.

[wmarke]

That I understand and that is working just fine. But what if I have 10 checks and 3 departments and they each get assigned to any one of 3 departments? What if the next time I get 30 checks and they each get assigned to any one of 5 departments?

Yes, it is an off way of "Accounting" but the different departments having separate accounting software; it's just how we do it and no I don't agree, but... it is what it is. Our office is the clearing house for payments.

[June7]

Don't really understand the issue. That is data entry of 10 records for 10 checks. Select department for each record. Can have code carry forward value into next record. This is 'user friendly' automation. The more user-friendly, the more code. Essentially, in the AfterUpdate event of control, set the DefaultValue property so that when user enters data into a new record, that field will automatically populate. However, this automation has its risks. Since the field already has a value, the user might overlook changing when appropriate.