Database help!

[Jennerizer7]

Outside of a class 14 yrs ago, I haven't touched DB setup/design. We never started from scratch either. I was asked to create one for work and I have an insane deadline. Was hoping for a refresher course but not enough time for that. I am posting an attachment. The attachment is a form the end user wants to see to enter in information to the DB. It also has what he pretty much wants. Trying to decide how many tables I will need and how to piece them together. I'm guessing I will need a Business tbl, Person tbl, Financial tbl, Lead Bank tbl(?)... Any help would be great! The following corresponds w/ the attachment. Also, how do I get 15 relationship boxes.. confused!

The checkbox by Business name at the top #1, actually is for relationship. If it is checked, it is suppose to look to the relationships down in #9 and pull information for a letter that will be created to the Business. If relationship is checked, Business Name will at least match the relationship in #9.
#2. The people. These are the people that are going to get letters. They are the owners/co-owners of the business. Of course this is pulled if the Relationship box is checked.
#3. The address is the address of the business if Relationship box is checked, otherwise it will be the address of the individual person.
#4. Self explanatory. It’s phones of business or owner.
#5. Ok.. If participation is Y, then 6 is needed. If 6 is Y, then 8 is not needed. If participation is Y and 6 is N, then it needs to pull 8 in and create a letter to that bank (can prob import letter I'm assuming).
#7. He wants this to automatically update..so if the last contact letter was sent today, it will have that in there.
#9. These will always be businesses. They are related to either the "business name" or the entities below it in #2. Some have 10-15 relations so he needs 15 relationship "boxes".
#10. Tax Returns and Financials: Wants to know what we have (date), what we need (year/text-short text field) and the due date (date). Also wants to know when it was last reviewed (date) and by whom (initials). It sounds like he is going to want a qry run that ties all this together. If we need the tax return and financials for the relationed business and entities it will create that in the letter being sent.
The picture is what he wants the data entry form to look like for him/the users. He said that if relationship isn't checked the entities (people in #2) will have their own info in their own "card" but he wants it to look just like the picture in either case.

 

[Estuardo]

G'd evening,
That's a good question. How to find out how many tables do i need? Normalization. I know you said you have no time for this but that the answer. An example using the info you provided.
Business Name will be your main entity, so lets call it tblCompanies
Then we have an entity that just exist if the tblCompanies exist: Relations. Let's name it: tblRelations
A typical Info for a company will be:
CompanyID
CompanyName
CompanyAddress
CompanyCity
CompanyZip
CompanyPhone
CompanyEmail

Child table for Contacts/Owners/Co-Owners
ContactID
FKCompanyID
FKSalutation
ContactName
ContactPhoneH
ContactPhoneW
ContactPhoneC
ContactEmail

A Table Salutations (ztblSalutation) Prefixed with z because it will change few times or none is just a helper table.
PKSalutation
SalutationDescription

Finally Relations tblRelations
RelationID
FKCompanyId

With this you don't have to worry about how many relations a company may have. In a form the main form will be for companies a subform for contacts and another subform for relations.
In both cases you don't know how many contacts a company may have as well as relations.
Avoid data duplication, use a naming convention for your objects, this will make your life easier and also for all of us if you need assistance.
Use controltabs to organize the information.

One last thing, i don't think that to work in a single form is a good idea.


G'd luck

 

[Jennerizer7]

Thanks Estuardo for your suggestions! Do you think I should make a table, say tblFinancials containing the Tax Return/Financials and the dates in it?

Do you think it would be safe to put the Participation/Lead info in the company table or would you suggest creating a separate table for that info?

Thanks again for taking the time to offer help! it's appreciated!

 

[jdraw]

Best advice I can offer is to read/work through this tutorial. It will lead you to the tables you need, normalization and relationships.

 

[Estuardo]

G'd Evening,
Without knowing the data and how is related i can't say much.
1st Normal form says:
"Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key)."
So, the answer is yes, create a table for every entity.
About Participation/Lead. What is it?
A Company just can have one Participation/Lead? If yes add those columns to tblCompanies. If no, create another table for Participation/Lead.
Keep us updated, if you have any problem just say it.
G'd luck

P.S.
Just to clarify. I'm helping this way because the poster needs this for his/hers job. I said in my first post, that almost all questions are answered by understanding normalization

 

[gemma-the-husky]

OP

what's your deadline on this?

in general my recommendation for anyone in a corporate position is to get some professional help, even if it is just for initial guidance.

access is just not possible to "hack" if you will, in the way that excel is. There is a steep learning curve.

for one thing you simply must use some code in access - which you don't have to do in excel. access is just so powerful that a lot of time we actually prevent users using the full power of access.

most importantly, NEVER expect to open and use tables directly. even developers/designers obey this - except when investigating data for some speacial reason. use queries and forms always.

in general terms, try to avoid to think about access in the same terms as a spreadsheet, and try to avoid any concept that needs a row of data to reference another row of data (which is common in excel)

good luck

 

[Jennerizer7]

Estuardo,
Not every business will have Participation (it relates to loans). If there is no participation loan, then there will be no lead bank info necessary. If participation is marked Yes, then we would ask if we are the Lead bank. If we are the lead bank, then nothing else needs to be done. If we aren't the lead bank, then we would need to pull in the Lead bank info and create a letter to send them. Participation is where 2 banks act together holding one loan.

Jdraw,
Thanks for the tutorial

Gemma,
Lets just say due "like yesterday". I know that forms and queries will need to be used--trust me, the users that will be entering info don't need to mess around with the original tables or my life might turn awful--haha. I was hoping all of you would be professionals as I can't seem to find anyone near here to ask for initial help. I have the basics down on paper..I drew out what tables I think I need..its the tying them together that is giving me a little grief.

thanks for all your help and suggestions! Greatly appreciated.

 

[Estuardo]

Ok.. i see. One more question & suggestion
A loan is a complete entity or belong to another entity? I'm asking you this to know who's depending on who.
At the beginning "Participation" seemed to be something related to nothing.
Build a db with the main entities (upload the mdb) and lets start to work on it.

 

[Jennerizer7]

Participation loans are only dealing w/ businesses.

 

[Estuardo]

Hello,
Let me ask you a couple of questions:
1. what are these tables for: tblBusBusRole and tblBusPersRole?

tblBusBusRole
Linked to: tblBusiness tblRole tblBusPersRole


tblBusPersRole
Linked to tblBusiness tblRole tblPerson

They look to me more like a query result than tables

What kind of information is stored there?

 

[Jennerizer7]

Not sure if anyone is familiar w/ Jack Henry/as400/iSeries, but that is what we use as our "core" system here. It has all the info that is needed to correspond w/ the database. There is a screen for instance that has all the people related to the business and it also has the tax info..for instance, what we have the date doc received, the doc expiration date (when the next one is due), the document date and the detail description which says for instance Fed Tax Return for 2012 (this is what they currently need). That takes care of some of the fields on the form. It has a CIF number which is used as a unique identifier (instead of using social security #). Is there a way to just pull this info in from the 400 instead of manually having to enter everything?

Estuardo, I was thinking that could be used to hold Lead Bank/Owner/Co-owner/Guarantor, etc

 

[gemma-the-husky]

extrsacting data from a AS400

possibly, but more likely get an export from the AS400 in a csv format (or fixed width format) and import that into your database.

you could even use a print file if its the only thing you have, although its harder. I can suggest a tool (not free) for that.

 

[Jennerizer7]

Jack Henry is our outside vendor that pretty much takes care of most things w/ the iSeries. We don't do much in house w/ this except data entry, running a few queries etc, ..everything else is pretty much outsourced. In any event the person that wants the DB showed me a screen from the iSeries that contains the financial info he is looking for. I wasn't sure how to link this in the database. Screen is attached The other screen he showed me are the relationships of the people that are attached to the business/business loan.

(Well I didn't realize the s/s was so large, so I took info and paste it into word.)

 

[Jennerizer7]

Not sure if this looks a little better than the last DB I uploaded. Still not sure exactly how to create that form that the user wants to enter the data in. For now I am skipping the ODBC as our vendor hasn't given us the table that I would link to.
Disregard the table called tbleCustomerContainAll.
Thanks!

 

[Estuardo]

I just made a few changes on your tables and added a couple of forms. I guess that if you build an easy GUI your user will appreciate it. Like i said before one form for all is not a good idea.
Back to your tables. I've found a couple of columns in several tables related to "last contact". That column is on Bus, Contacts and Documents. What is that for? Also i think that you're missing tables or the tables you have do not reflect your drawing or is just that i still don't get it .

 

[Jennerizer7]

I suppose I should rename lastContact as LastLetterSent perhaps. I know that the user is going to want to have a query to pull when the last letter was sent to the customer/business. I have attached the original form he wanted to use as data entry and have given a little more explanation. I created relations table to relate business to business or business to contacts.. as I said, each can have up to 15 different relations to other businesses or other contacts. I'm not sure if it is set up properly.

 

[Jennerizer7]

Estuardo,
What tables do you feel are missing or what is it that you possibly don't understand? Also, was just playing around with DB7.zip that you attached. How is it that you can add a new business record w/o entering in the table. If I try in the frmBuss, it gives me a VB error...invalid use of Null. Clicking debug highlights this line: Idex.Customer = Me.txtPKBus
Thanks.