Consumer list linked to other data tables

[derrick]

Hello, thanks for reading this. I'm trying to combine four large spreadsheets into a single database to make the data more manageable. All four spreadsheets use the same list of consumers. The data needs to be recorded for each fiscal month. So I'm trying to figure out how to link those different data sources to each consumer. I think I can figure out creating the forms, queries, and reports on my own.

[burrina]

Are the spreadsheets already in the db? Do you have the table structure as you need it already? Same list means to me that they could be in one table.

[derrick]

Are the spreadsheets already in the db? Do you have the table structure as you need it already? Same list means to me that they could be in one table.

1. No, I did try creating a database table by importing the spreadsheets but it didn't turn out right. I think because of the way she has the spreadsheet setup. Each month is a spreadsheet within the workbook. Within each spreadsheet she has it broken up into categories with sub totals and at the very bottom there is grand total.

2. So far what I have is a table created for the consumer list and billing setup as:
tblConsumerList
ConsumerID (Primary Key)
Last Name
First Name

tblBilling
RecordID (Primary Key)
Date
Waiver
$Request
Payment
Other Billing
Payments After Adjust
Program Recoup
Carry Over
Write Off
Remainder

[burrina]

Is tblConsumerList related to tblBilling? If so tblBilling needs a Fake Key to link them such as ConsumerID and data type as number. Also some of your fields in the table have spaces in them and this is ill advised. Also the $ sign as well should be eliminated. What other requirements do you envision for the db? Write them down on a piece of paper and draw them out.

[derrick]

Right now I'm just working on getting these two tables to work. I really don't understand the other spreadsheets she uses (the ones with sub categories and totals). So I'm working on these first two tables for now. I will also make a form to add new users and then a form to add new records. So for the form to add new records I would like a drop down to select a consumer and then the data related to that consumer would show within the form. Then later I will need to find out what reports she needs from that data.

[burrina]

Why would you need another form to add a new record? I understand a new user, but this could be opened via the form you are on. If say you have the consumers form open and need to add a new record, then just click the new record button. If you need to add a new user (What is a new user?) then as I said you could make the form open from there or a menu as well. These 2 tables are VERY important to your data structure since billing is involved. My other questions you did not answer! You need to get a good understanding of those subcategories and totals if it is working for that person since you will need to replicate the results somehow.

[derrick]

Sorry I didn't mean new user I meant new consumer. tblConsumerList would have the list of all the consumers. The values in tblBilling would need to be linked to a consumer under tblConsumerList. Eventually I will need the other spreadhsheets with their values linked to that same consumer list. I will work on getting understanding for the subcategories when I'm reading to add those spreadsheets. For now I would just like it so that when she selects a user in the combo box it will display that consumers values within the tblBilling.

[burrina]

Can you upload what you have so far?

[derrick]

Can you upload what you have so far?

Here is what I have and sorry for the confusion. I don't work in finance so I don't really understand these spreadsheets. The person I'm trying to help (also not a finance person) is filling in for someone who left the company. These spreadsheets are overwhelming to her. They are very large and I think it would make more sense to have them in a database. But it has been a long time since I've worked with access. I haven't opened access in over 10 years when I got certified for Access 2000. So I'm trying to dust off the cob webs and hopefully get a better solution for her. And thanks for the help!

[burrina]

I did not know what these fields mean and so the formula(s) may be off, so edit at your will.
Good Luck with your project. Also please take a look at your tables and see the revisions I made.

[derrick]

I did not know what these fields mean and so the formula(s) may be off, so edit at your will.
Good Luck with your project. Also please take a look at your tables and see the revisions I made.

Basically I'm trying to make a form that does this http://www.youtube.com/watch?v=x0lX3q5dhhU . A combo box that you select the consumer displayed as [LastName]&","&[FirstName] and then brings up the data for the consumer.

[burrina]

I uploaded you a revised db, did you look at it?
If this is not what you want then I am at a loss! Good Luck With Your Project.

[derrick]

Hey Dave, where can I view your revised db?