Help with design

[jzwp11]

First some general recommendations. It is best not to use spaces or special characters in your table and field names. Second, even though Access has the capability, it is best not to use lookup field (combo or list boxes) in your tables. This site details the problems doing so can cause. The values in those combo boxes should be split off into their own tables and a foreign key used to reference the applicable value. Combo/list boxes are best left for forms.

Out of curiosity, the 3 fields: cash, check, credit card, I assume you are using these 3 fields to capture payment that could come in via multiple payment methods. For example, someone pays 100, but gives it to you as 50 cash and 50 in a check. Am I correct? If that is correct, then the table is not structure properly. If a payment can have multiple payment methods each should be captured as a record in a related table



tblPaymentDetails
-pkPayDetailID primary key, autonumber
-fkPaymentID foreign key to your table 2 (ID) field
-fkPaymentMethodID foreign key to tblPaymentMethods
-currAmount

tblPaymentMethods (3 records in this table: cash, check, credit card)
-pkPaymentMethodID primary key, autonumber
-txtPaymentMethodName

I'm not sure I understand the purpose of the posting date from/to fields in table 1. Doesn't each transaction/payment have its own posting date in addition to the receipt date?

[Ray67]

The three fields cash,check and credit card you are right im capturing payment type. The reason i structure it like that is because it made it easier for user to enter that data. Posting date from/to is the date that we posted in our other system.

[jzwp11]

The three fields cash,check and credit card you are right im capturing payment type. The reason i structure it like that is because it made it easier for user to enter that data

I understand, but it is not a normalized structure which could cause you problems later on.

Posting date from/to is the date that we posted in our other system.

But why have 3 dates posting date to/from and today's date? Say the user posts 5 receipts today for the provider and 10 tomorrow, why would you just not have 2 records in table 1 with the applicable posting date (i.e. today's date) and the applicable payment records in table 2 for each of those dates?

[Ray67]

Didn't think of it that way.

[Ray67]

Sorry i ask so many questions. I'm new to access. What is pk and fk stand for???

[jzwp11]

I use several prefixes in my field names to help identify the field/datatype. pk denotes a primary key field while fk denotes a foreign key field (i.e. the field that corresponds to a primary key in another table; it is the pk-->fk that forms a relationship between two tables). When I design my tables, they all have an autonumber primary key field. The foreign keys in my tables need to be long integer number datatypes in order to join with the corresponding pk. The autonumber datatype is just a special case of a long integer number datatype.

I use other prefixes as follows:

txt=text field
dte=date/time field
log=yes/no or logical field
long=long integer number that is not a fk
sp=single precision number
dp=double precision number

[Ray67]

Thank you for your help. I will start to build it. Lets see how it goes.