So close, yet so far. My desperate journey to a functional DB - HELP

[The_Learning_Curve]

So i've been doing everything I know how to do to learn the basics and I seem to be getting no where. Am I wrong to assume Access can be self taught through free online resources? I've been at it off and on for about a month now in between operating my business and i'm really getting my butt kicked. Which is a tough pill to swallow considering i'm self taught with piano, guitar, music theory, auto mechanics, general aviation, unmanned systems etc... The internet is an unbelievable resource that actually takes away the title "self taught" as i'm actually taught by the individuals and resources online that contribute. With that being said, I don't think i've ever run into such an abundance of vague and limited amounts of information on access. Or i'm too ignorant to comprehend and apply the info I come across. I have priced out a couple of database builders, but its just not an option at this point unless things "take off" beyond my expectations. Plus, I know how things go, I need to understand whats going on so I can maintain and add things as the business grows.

I'm operating a service business, with equipment. The equipment needs operating logs and maintenance logs. The operators need training logs and operating logs. I also provide training for customers, so customers have training logs, some of my equipment may be used for customer training so that info needs to be related. Then of course your basic customer data and contact logs, work orders and general job information data. On top of it all, all employees for the time being are independent, so being able to identify skill levels and experience as the roster grows is necessary.

Feel free to criticize me and bash me for trying to tackle this with no experience, just throw in some useful advice while you're at it!

My problem right now is very basic, I haven't even tried some of the more complex queries and forms yet. I can't get a client data entry form to interact with the contact id as you go through the process of adding a new client. I think it comes down to how i'm placing the keys in the tables. I have done subdata sheets on the client form and it throws up an error about (no record exist), so it's not auto creating a new record in contact logs as I tab over.

I'm attaching a jpg of the relationships. Anyone willing to verify the relationships or critique my methods? If you can even follow the web of craziness. I know its alot to ask, I just wish I could pay someone a reasonable amount of money to sit down with me for a couple of hours and point me in the right direction.

[The_Learning_Curve]

Also, since this is such a complex DB, is it reasonable to assume this can be made functional without knowledge of coding? I'm not expecting anything fancy or super efficient with the GUI. I just want it to be functional.

[ssanfu]

Welcome to the forum.....


Field names (actually ANY object) - shouldn't use SPACES in names.

Names should be only letters and numbers (exception is the underscore). NO spaces, punctuation or special characters.
You have some spaces, a dash "E-mail" and parentheses - "Skill Level (1-3)" in field names.

Better names:
"Email" or "E_mail"
"SkillLevel" "Skill_Level" (what happens if you add skill levels 4 & 5 but the field name says 1-3???? Nothing really, but is confusing. Also "1-3" is data....


Any chance you would post the dB?
Do a "Compact & Repair", then Zip it....

[The_Learning_Curve]

Thanks for the tip, I tried to stay consistent with no spaces and labeling, but after frustration set in I got a bit lazy. I'll definintly make it a hard rule for now on. Heres the zip, I didn't realize I could do that with compact and repair and then zip!OS Database.zip

Thank you for your time!

[The_Learning_Curve]

BTW, I have noticed that some data could end up being repeated in some cases such as "company" in the clients table, thats something else i need to get done along with normalizing the labeling

[The_Learning_Curve]

As I was going through correcting the labeling as recommended and contemplating on creating a table for "company", do you think it is wise to make a look up table for all individuals, instead of having seperate tables for employees and clients?

[ssanfu]

, you added the lock file (.laccdb) not the accdb to the zip file...

[The_Learning_Curve]

OS Database (2).zip

oops!

[CJ_London]

going through correcting the labeling as recommended

One tip, if you are not aware, is to use the field caption property. For example

FieldName - SkillLevel
Caption - Skill Level

When you start to create your queries, forms and reports, Access will apply the caption to the associated label. This will save you having to edit the labels to add a space to make it 'user friendly'.

[The_Learning_Curve]

Good point, thanks! I remember that in some of the tutorials, I just have been so caught up on getting it functional I haven't properly paid attention to doing things the right way like a dummy lol.

Assuming my keys are correctly placed, what would be the right way of making forms for data entry into multiple tables. Say i build the contact log into the customer data entry form. How can I make it start a new record in the contact table after tabbing off of the newly created customer record. Would a query be the ideal source of the table? Originally I did the contact log as a sub form, but once i tabbed over it wouldn't create a new record and just throw up an error.

Most of my confusion is in data entry forms and how to fluently add data to multiple tables while keeping everything in sync. Of course I don't want to actually see the autonumber data for all the tables either in the data entry form.

[AccessPower]

The issue that you're going to have with queries as a data source is that they disable data entry unless they're updatable. You can use subforms to avoid this and link fields as another approach, though this can get messy pretty quickly.

If you're looking for more information on using a query as a data source, check this out:

http://allenbrowne.com/ser-61.html

Also: HOLY BANANAS that's a lot of relationships.

[CJ_London]

right way of making forms for data entry into multiple tables

rule is one table, one form. If you have multiple tables, use subforms. So your contact log would be a subform on your customer form.

How can I make it start a new record in the contact table after tabbing off of the newly created customer record

If linkchild and linkmaster properties are set for the subform control (which they will be if you drag the contact form onto the customer form) then this will happen automatically.

Of course I don't want to actually see the autonumber data for all the tables

sometimes access does this for you, otherwise, just delete or hide the control

Personally I find the auto layout of controls often gets in the way of my form designing so if I've used the wizard to create the form then in design view I select all controls and click on the Remove Layout option under the arrange tab so I can move them around at will.

[The_Learning_Curve]

Ok, great info so far guys, much appreciated. I did a quick retest of adding the contactlog form to the client form. Checked that the ClientID indeed was linked as the master to the clientID in contacts. As I tested the form, I added a new client record, as I tabbed to the contact subform I get this error

"You can not add or change record because a related record is required in lktaddress"

So AHA I thought, progress, I forgot to add the address lookup table to the clients. So I did and the same message pops up when tabbing into the address subform.

Should each master form essentially have no lookups for data entry?! IF that is the case, the lookuptable for address would have to contain the various ID's of individuals, companies and job locations. Which doesn't seem right to me. Any suggestions?

[The_Learning_Curve]

Its like the referential integrity is preventing me from tabbing to the next subform to enter new data related to the master form.

[CJ_London]

when you apply referential integrity you cannot create a record on the many side of the relationship (the infinity sign in your screenshot) unless the 1 side of the relationship already exists.

So sounds like you main form is based on the many table and the subform on the one table - it needs to be the one side is the main form and the many, the subform