Table Design w/ forms

[DrossZro]

Hey everyone, first time posting but i plan to be around a lot. I am learning this to improve processes related with my job.



Right not i have a table (table 1) that gets updated with information about out clients via a form. No big deal. Clients are in a list on the left side and their contact info and such is in the fields to the right.

These clients are billed and return payment once a month. I have a separate table that shows the amount they are billed for and for how many (table 2) and then another table (table 3) of returned payment and how many that covers, as well as the date and who processed their payment...this section is shown in the attachment. (there are two seperate SUBFORMS on this picture. The billed section is table 2 and the received section which is 4 fields across is table 3)

Now i have the client names and the figures sent and recieved (dollar amounts and dates) listed in the rows on the table 3 and the Months, Insured's, dates and OPIDS as the columns names...which has resulted in 48 columns....I want to be able to run reports to pull in any combination of data from table 2 and 3 as i can.

If this makes any sense at all to anyone...is there a more efficient way that i should go about this? Im pretty new so this is all a learning experience.

Thanks so much

[DrossZro]

k maybe this will help...

CUINFO
Credit Union
CU Name
Street
City
State
Zip Code
Audit Type
Phone Number
Road
Contact Name
Contact 2
Phone 2
Date Billed
Emails
Bill Type
Last Pay Proc
Comments
Billing Instructions

BILLED
CU Name
Jan 2011
Jan - Ins
JanDate
Feb 2011
Feb - Ins
FebDate
..etc for each month to total 36 columns

PAID
CU Name
Jan 2011
Jan - Ins
JanDate
JanDateP
JanOPID
Feb 2011
Feb - Ins
FebDate
FebDateP
FebOPID
...etc for each month to total 60 columns...

now the CU Name column tracks a relationship. The billed table tracks how much we billed a client for and how many items that contained. Then the paid is how much they paid, how many items, when it was received, when it was processed and who processed it. I want to be able to get a sum for each month. (total for all clients paid in January, who didnt pay..etc)...

I also realize this isnt probably the most efficient way to go about this. Any help on these two issues would be appreciated.

[ssanfu]

You are committing "Spreadsheet"... What are you going to do for 2011?? Recreate the tables, queries, forms and reports? You need a different design for a database.

This is the structure I would start out with. (It will probobly change a little). Without examples or a desc of what the fields contain, I did a "best guess fit". I might be missing a field in table "Journal".

Here is the structure I came up with:

table name: CUInfo
-------------------------------
CUINFO_ID Autonumber PK
Credit Union Text
CU Name Text
Street Text
City Text
State Text
Zip Code Text
Phone Number Text
Road Text
Contact Name Text
Contact 2 Text
Phone 2 Text
Date Billed Date/Time
Emails (Text?)
Last Pay Proc Date/Time
Comments Text (or Memo)
Bill Type Text (?????????)
Audit Type (???????????)

table name: Journal
------------------------------
Journal_ID Autonumber PK
CUINFO_ID_FK Long (link to CUInfo PK)
JournalType_FK Long (link to JournalType PK) (ex: Billed)
Period_Mth Integer (ex: 2 ) (for Feb)
Period_Yr Integer (ex: 2010)
TransDate Date/Time (ex: 2/2/2010 9:30 AM)
Amount Currency (ex: 523.42)

table name: JournalType
--------------------------------------
JT_ID Autonumber PK
Desc Text (Billed,Paid)

table name: BillingInfo
-------------------------------
BillingInfo_ID Autonumber PK
CUINFO_ID_FK Long (link to CUInfo PK)
Instruction Text
InstruDate Date/Time

Relationships
-------------------------------------------------------------
(table/field) (table/field)
ONE MANY
CUInfo / CUINFO_ID -------> Journal / CUINFO_ID_FK
CUInfo / CUINFO_ID -------> BillingInfo / CUINFO_ID_FK
JournalType / JT_ID -------> Journal / JournalType_FK



HTH
------
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

[DrossZro]

Thanks so much for this man, I have only been working with access the past week or so and figured I was going about it wrong.

So are we to say that the info for table Journal is to be entered by the user? Rather than layed out before hand like i had it?

I will examine what you put more tomorrow ubut thanks so much for the help

[DrossZro]

So for this table:

table name: Journal
------------------------------
Journal_ID Autonumber PK
CUINFO_ID_FK Long (link to CUInfo PK)
JournalType_FK Long (link to JournalType PK) (ex: Billed)
Period_Mth Integer (ex: 2 ) (for Feb)
Period_Yr Integer (ex: 2010)
TransDate Date/Time (ex: 2/2/2010 9:30 AM)
Amount Currency (ex: 523.42)

By "Integer" do you mean "number"?

What is the "Long" reffering to?

and then what do the PK's and FK's stand for?

Will each clients payment be assigned a unique Journal number?


table name: JournalType
--------------------------------------
JT_ID Autonumber PK
Desc Text (Billed,Paid)

Does this table just describe if they are paying or being billed?



table name: BillingInfo
-------------------------------
BillingInfo_ID Autonumber PK
CUINFO_ID_FK Long (link to CUInfo PK)
Instruction Text
InstruDate Date/Time

confused about the instruction part?





just thought I had when looking at your response. thanks again for your help. you are really helping me and its greatly appreciated.

Ryan

[ssanfu]

Hi Ryan,

By "Integer" do you mean "number"?

Yes. The field data type would be "Number". Then below on the GENERAL Tab, there is a combo box for the Field Size. This is where you select Integer, Long, Byte, Single, Double, etc.

What is the "Long" reffering to?

"Long" refers to a long integer number type.

and then what do the PK's and FK's stand for?

PK = Primary key for the table
FK = Foreign key -- link to another table's primary key

Will each clients payment be assigned a unique Journal number?

Yes . Do you assign a unique Journal number or is an autogenerated number ok? The autonumber should not "mean" anything other than it is a unique number to identify a record.


Does this table just describe if they are paying or being billed?

Yes. Both types of records in one table. You can set up queries with date parameters to select different years without having to create new queries/tables /reports for each year.

confused about the instruction part?

Yes... and also a couple of other fields.
( BillType, AuditType DateBilled, LastPayProc)


Attached is a mdb w/relationships.. not complete but might help

HTH
-----
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

[DrossZro]

Thanks so much Steve. You rock

Im gonna try and work up a form that would work with those tables and see if I can make a lay out that makes sense. You are seriously awesome. I may have more questions because I really want to understand all of these steps for what I am doing rather than just "Cheat"

On table:

BillingInfo

The billing instructions are mainly just to describe how we bill that client so it would me a text/memo field i think. What did you have in mind for the IntrucDate field? Im a little lost with that.

Also, the JournalType...I understand the "1" and "2" is to represent if its billed or paid but what is the best way to get the user to enter this information? Should I have a drop down column with Billed and Paid in it that would enter a 1 or 2 into a table for that entry? Being new I am a little confused on how to go about this as well as the best way to have the user enter this information (along with the JV Periods...would this be two seperate fields so they put a 2 in one and then 2010 in the other? Pretty much through out any kind of display like i had already made?)

The users of the form will need to easily be able to view billing and payment history as well as submit a new entry, all on one page. So if I had entry fields on the top of the page like:

Amount:_____ Items:____ Month:____ Year:_______ Billed/Paid:______

that would be entered by the user and then update a couple of forms below it with the billed/paid history?

[ssanfu]

Ryan,

Remember that I didn't know what the fields were for (no description) and no examples of the data. When you normalize the structure, you look at each field and decide if the field (data) are related to the other fields. So in the table CUInfo, how do the fields "Date Billed", "Bill Type" & "Audit Type" relate to defining a CU (Credit Union??)?

Same question for the field "Instruction". I didn't think it had anything to do with defining a specific CU (as opposed to the CU address). So I moved it to its own table. Eacu CU can have 1 or more instructions. Now you need a date field to know *when* the instruction was issued. Now you can have multiple instructions (a history) for each CU ordered by date (and/or time).

Not knowing what the "Date Billed", "Bill Type" & "Audit Type" fields were for, I left them alone..... but you should think about them. This is called normalization.

As for the JournalType, 1 or 2 will be stored in the "Journal" table, but a combo box, you will see either "Billed" or "Paid". that way you will always have the same spelling, not "Bill", "Pd", "pd", "billing", etc.
You would use a combo box with the row source set to

SELECT JT_ID, Desc FROM JournalType ORDER BY JT_ID

(I just realized "Desc" is a reserved word.... )

[DrossZro]

thanks again dude. i will look at this more tomorrow night, need to get some sleep for now. once again, all your help is so very much appreciated.

do you know of any resources/tutorials that would be beneficial to me that i could read in between mine and yours posts? : )

have a good night

[ssanfu]

Have a look at

http://accessmvp.com/Strive4Peace/

Chapter 3, Normalizing Data

Crystal has a lot of good, basic (no pun intended) info.


Also:

http://tinyurl.com/hczcd