Am I out of my depth - Data Base Creation

[rovert]

Hi All

I'm trying to create a Client Data Base which interacts and helps me with my day to day mortgage brokerage work, however, I thought I was doing quite well till I started growing the data base and wanted it to do more... i'm semi hooked on this Access System, but I think i have not even scratched the surface yet...

The status so far...

I've created multiple tables, Such as Main Client Table and other ancillary Tables that include clients mortgage data, protection requirements and so forth, that in theory are to interlink with each other... however i'm now stuck.

I wish to be able to do the following.

I wish to be able to select multiple products for the client each of which will have a varying table of fixed fields, but I wish it to be only relevant to that client. But I am unsure how to create this in the Main Table or in separate ones, and as for relationships... how do you link these together with this scenario.

There are a load of other things for me to learn and set up, but until I crack this one there is no point.

I am a WYSIWYG kinda guy, not dealt with code, so shoot me down now and tell me to quit while i'm ahead, or if any one can point me in a favorable direction, i'm all ears.

 

[ajetrumpet]

so shoot me down now and tell me to quit while i'm ahead

"BANG..." QUIT WHILE YOU'RE AHEAD.


in all honestly, there isn't really enuf info in your post to help you out here.

 

[rovert]

LOL - That cheered me up....

Where do I start.

First of all, how do I create a Multiple Field in the Main Table that will offer me to complete another table or form on Selecting 1 or all of the items with in the multiple field...

'Does that make sense'

R

 

[Kryst51]

LOL - That cheered me up....

Where do I start.

First of all, how do I create a Multiple Field in the Main Table that will offer me to complete another table or form on Selecting 1 or all of the items with in the multiple field...

'Does that make sense'

R

Not to me , do you mean a look up field?

 

[rovert]

Hi Kryst51

Yes - I've created a lookup field with 5 options (multiple selection) and i would like to be able to expand on those options in different tables relevant to that client.

 

[David Eagar]

It might help to attach database so we can see it

 

[boblarson]

Hi Kryst51

Yes - I've created a lookup field with 5 options (multiple selection) and i would like to be able to expand on those options in different tables relevant to that client.

Lookups should not be defined at the table level and you can read why here.

 

[KathyL]

‘I wish to be able to select multiple products for the client each of which will have a varying table of fixed fields, but I wish it to be only relevant to that client. But I am unsure how to create this in the Main Table or in separate ones, and as for relationships... how do you link these together with this scenario.”

If each table for each client has the exact same fields, they shouldn’t be separate tables. It should be one table, and in that one table should be a Customer code. You then use the customer code to connect things, like connecting a subform to a primary form to show all stuff for one Client.

That’s the essence of a relational database.
Take a class at a community college, or get a tutorial book.

Kathy
Systems Analyst and Consultant

 

[ajetrumpet]

That’s the essence of a relational database.
Take a class at a community college, or get a tutorial book.

some people dont' have time, nor do they have the money. that's one of the reasons i keep posting on this site. to help people with these sort of issues.

i'm not going against what you said, but honestly, i've learned more practicle application of programming from people on this forum than i could ever learn in a class....IMO

 

[Galaxiom]

Those of us who don't turn back are always out of our depth.
As soon as we touch the bottom we are compelled to swim into deeper water.
But the Access Ocean is wide indeed and the VBA current strong.
I don't know anyone who ever reached the other shore.

 

[ajetrumpet]

Those of us who don't turn back are always out of our depth.
As soon as we touch the bottom we are compelled to swim into deeper water.
But the Access Ocean is wide indeed and the VBA current strong.
I don't know anyone who ever reached the other shore.

galax...the VBA WORLD IS TOUGH!? i am using php and js for our website. compared to vba, they are god. IMO =)


idk how many times i've found my page not working because i forgot ONE semicolon at line end somewhere in 2000 lines.

 

[Galaxiom]

When it comes to programming languages it is a case of Scylla and Charybdis.

 

[rovert]

Thanks all

I'd like to post it to be able to be viewed, can any one suggest how i do this.

 

[Galaxiom]

Remove all but a handful of dummy records.
Zip the database. (Right click > Send To > Compressed Folder)
Attach to a post.

 

[rovert]

Hi Galaxiom - That was quick... thankyou!

Ok - Pleasea dont laugh.... the Database is attached as requested... Basically its a download from the office site, but i've added tables etc...

The Contact Details form has been screwed up by me, which is a shame as I had created pages for the different tables that could have been applicable... so i've started the one titled 'Contacts' not a patch on the original one

In essence the flow should be as follows.

Advisor has all tables related to him/her - i should set up an admin one too
Contacts have multple tables depending on requirements : Products Provided which links to the Products of Mortgages, Life Insurance etc etc
Complaince will be related to the client database

Thats the main idea....

I'd also love to explore the following.

Can I create a scan to button to import docs directly to a form?
Can I create an e-mail to client button which in turn keeps a copy of the e-mail sent? and can this include attachements, or is this getting too techy?

I'm keen to learn more.

Thanks for your input

 

[David Eagar]

You need to go right back to basics and firstly understand 1 to many relationships and redesign your tables much more efficiently (search on normalisation).

The key will be deciding what is the primary table: Contacts? Products? Other?

 

[rovert]

Thanks David

I think i know what you mean... I've just googled Normalization and its starting to make sence...

So... If i was to make the PRIMARY table my Contacts, all others ar then sub tables - Yes?

 

[David Eagar]

I don't think sub tables is the right expression, but along those lines: If product is the primary table, then 1 product record can have many contacts, 1 contact record could have many adresses etc etc