Main table about 3000 customers with 18 fields
Will input payments for each customer monthly and / or quarterly total about 12,000 per year.
Plan to purge payments over 18 months old frequently.
1) Put it all in one table or break into two ?
2) As far as db size wise and efficiency, would it really matter ?

Thanks in advance.

Separate table for payments, unless you only plan on storing the last payment in the customer table. Adding a field for each payment would be a nightmare to maintain and violate all the rules of normalization.

Thanks Paul. Sort of figured that.
I have tried making two tables and letting access do all the relationships etc.
Just can't get the payment input to work right.
I'll keep trying.

Post back if you get stuck. The 2 tables would be related by customer ID, and a common way of viewing the data would be a form/subform, where the form was the customer data and the subform was the payments. Master/Child links would keep the subform in sync with the customer on the main form.

Ok here goes.
I imported my XL file with the import wizard, and manually set my tables.
Set CID which is the unique cust ID as the PK in TblCustInfo and Access made a lookup field in the TblPayments called ID.
Then made a single form from the tables, not a query, having Name, Addr, and CID from TblCustInfo and Chknum, Chkamt, and Recdate form TblPayments.
It will let me input the check info but doesn't relate any record to any of the TblCustInfo records.(customers)
Tried a combo box in place of the Name, Addr, etc., still same.
Sheeeeeez.
What am I doing wrong ? Holy moly I've deleted and redone so many times.

Tried a form with subForm. Nopie
It appears to me the relationship is many (customer Lookup to Payments field) to one(payments:ID) which, is reverse isn't it ?
I tried to change it but it won't let me. Tried to change it to CID to ID but it says the fields must be the same size.
I know I'm rambling but I'm also losin' it.

Can you post a sample?

Sample of ?
If you mean screen shot(s) - I'm sure the capability is here but haven't done it.

Hope this works

20470

You want the customer ID in the payments table (to identify who made the payment), and the relationship would be between the customer ID in the customer table (one) and the customer ID in the payments table (many).

I made a field in Payments CID. Linked the two and it will only give me a one 2 one.
Changed it to lookup field, still one 2 one.

There is something really simple I am missing here. duh

Without seeing a sample db, it's hard to know what might be the problem.

I started another db, made both tables from scratch. no data
Set CID (cust) as pk and autonumber. set PID (for clarity) in payments

cid as pk and autonumber, pid as pk autonumber
cid as auto and name as pk, pid as pk auto
cid as auto no pk, pid as auto and chknum as pk

various other ways
every link I set comes up as one 2 one

How can I attach the db or send it to you ?

btw I am using access 2003

Compact it, zip it and attach it here. It has to be under a certain size, but most are fine if they don't have significant data in them.

This is the one I did by design with no data

20476

This is the one with partial data

20478

feel free to fix forms, reports, queries, etc. etc. etc. lol

In Bates, you have CID as the key and an autonumber. As an autonumber, there can't be duplicates, so there can't be a one-to-many relationship. You want it as the key in the customer table but not a key in the payments table. Also, they need to be the same data type. Typically it might be an autonumber in customers and a long integer in payments.

In NewTrial you don't have CID in the payments table, and CID in the customer table isn't the key. If I fix both of those, I am able to create the expected one-to-many relationship.

But CID in the customer table can't have dups since that is the actual customer account number in essence and must be an autonumber.
That will be the number I will have to use when, once finished, I will append the up2date data and let it use the existing numbers then auto increment from there.

I had used PID in the payments for clarity. That is a db I was just trying so I wouldn't get Bates so messed up.

I meant you have it as an autonumber in the payments table. Autonumber in the customer table is fine. It's actually a double in the customer table if I recall correctly.

Ok
CID is ok as auto in cust table, pk or no pk ?
In pay tbl, it doesn't have to be an autonumber ? meaning it really doesn't have a physical number in that field in the pay tbl other than a reference to cust tbl CID ?
I'm trying to get this.

It can be an autonumber in the customer table, and it should be the primary key. In the payments table, it can not be an autonumber, and it generally won't be a key (it would be considered a foreign key, but it wouldn't actually be a key field in payments). It must have the same data type as the related field in the customer table, so if that's an autonumber then the field in the payments table would be a long integer. You're identifying who made each payment, so it would simply be the customer number.

ok got em set in trial
one to many

made a single page form
Name and CID from cust tbl
chknum, chkamt, recdate from payments tbl

input a couple of dummy records (me of course)
won't list the records in the name textbox

I appended some records to the trial db, made a form with sub form, IT WORKS!
Paul, you are a gentleman and scholar and a very nice person for all your efforts and patience.

Now all I get is some GotFocus error after inputting records then when I go to do something else. I'll have to figure that one out.

Well, I have a number of former teachers that would argue the scholar part, but I'm glad you got it sorted out. Post again if you can't figure out the focus thing.

Seems to have quit for the time being. I assume GotFocus has to do with form control.

Thanks Paul
I gave you 'mana'

I'll be pickin' your brain more before I'm done with this.


Bob

ps, you in Vegas ? My fav gambling place.

I live about 400 miles north of Vegas. My employer has an office there though, so I go there fairly often. Let me know the next time you visit, and I'll see if I'm going to be down there.