Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    The_Learning_Curve is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    12

    Quote Originally Posted by Ajax View Post
    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
    Ahh, So in this case, technically, addresses table has no need of having referential integrity with clients. Since multiple clients can have the same address. And applying referential integrity would require every new client record to create a new address record. Is that about right?

    I'm definitely still trying to grasp some basics here.

    I decided to combine some tables that I thought would be more efficient. I combined clients and employees into an individuals table and combined client training and employee training together since the data columns were the same, I figured that I could distinguish the differences between client and employee data using queries rather than separating them into their own tables.

    I have been applying referential integrity to everything since I started building this, so now I need to analyze what actually needs it. I did remove the integrity on company to individuals (originally clients) before the snapshotClick image for larger version. 

Name:	DBRelationship.jpg 
Views:	17 
Size:	158.8 KB 
ID:	26758

  2. #17
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ahh, So in this case, technically, addresses table has no need of having referential integrity with clients. Since multiple clients can have the same address. And applying referential integrity would require every new client record to create a new address record. Is that about right?
    Not so much. RI ensures that an address in the many side must first be in the one side. You just couldn't enter an address of "1313 Mocking Bird Lane" for a client unless it was a record in the Address Table (the 1 side).



    What does the table prefix "Ikt" mean?


    First the easy things:
    1) lktServices.Description: "Description" is a reserved word. Maybe "ServiceDesc" or "CatDescription" or "CategoryDesc"

    2) lktAddress.ZipCode: Why "ZipCode" a number? Normally it is Text because no math calculations are done on it and if you try to enter "00684" in a number field, the leading zeros will be removed. Sorting a text field will have a different order than a number field.

    3)lktCompanyCamera: can't figure out the purpose of this table.....

    4) tContactLog.ContactDate: Why a text field? I would use a Date/Time type field.
    4a) tContactLog.ContactTime: Why a text field? I would use a Date/Time type field.

    -----------------------------------------------------
    Harder things:

    It looks to me that these 3 tables, "lktCompanyAircraft", "tMaintenanceDetails" & "lktAcDetails" are linked backwards.

    - One aircraft can have many Maintenance Details and one Maintenance Detail applies to one aircraft.
    lktCompanyAircraft.AircraftID_PK ------> tMaintenanceDetails.AircraftID_FK

    - One aircraft can have many AcDetails and one AcDetail applies to one aircraft.
    lktCompanyAircraft.AircraftID_PK ------> lktAcDetails.AircraftID_FK

    - Some of the fields in "lktAcDetails" could be/might be moved to table "lktCompanyAircraft".

    - Might need to move fields "MaintIssue" and "CorrectiveAction" in table tMaintenanceDetails to a new table.


    I think there might be a couple of other relationships that I would consider backwards............

    ------------------------------
    Note: To keep straight which fields were which, I added a suffix of "_PK" for the PK fields and "_FK" for the FK fields.


    Would you post the new(est) dB?

  3. #18
    The_Learning_Curve is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    12
    Attachment 26761

    So "lkt" is lookuptable. "t" is for table. lookuptable in my mind is a table strictly for referencing the details about something or someone. Tables in my mind interact with other tables more. If that makes sense. But I doubt i'm using that term properly or i def haven't labeled consistently.

    As far as data entry in "individuals" with "address" as a sub form (Will have the same issue when it comes to "Company"). Should the PK of Individuals table be in the Address table instead of the Address PK in the Individuals? If I did that, I would have to add the Company PK to the Address table too. Are there downsides to the to adding the FKs in address table?

    Ok, for the company aircraft, company camera, maintenance and lktaircraft and lktcamera... A hired pilot can have their own aircraft or they can use our company aircraft. If they own an aircraft, I need to beable to search for pilots by the aircraft they own among other pilot qualifications. Now for the aircraft we own, each one is registered with the FAA, they all have to have maintenance logs and flight logs. So i made a "lookuptable" for aircraft, that table will hold all performance info about specific aircraft. The Company Aircraft table (and cameratable) will hold information regarding serial numbers, registration etc, then the maintenance table is all about the maintenace logs for company aircraft and cameras. I have switched them around a couple of times while building the relationships, i'm not sure exactly the best way to do this.

    Sorry for all the inconsistencies with labeling.

    DB should be attached
    Attached Files Attached Files

  4. #19
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Another thing to consider with your existing relationships is that you cannot create a record in tJobLog unless parent records already exist in ikdIndividuals, iktAddress, ilkAcDetails., tWorksOrder and iktCamera.

    Do not confuse relationships with joins in queries. They are presented in much the same way (like a table datasheet and a query datasheet) but are different things underneath.

  5. #20
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Oops, I found more..
    "TrainingLog.TIME". "Time" is a reserved word and a built in function. Maybe "TrainingLog.LogTime"?
    "lktAddress.Number". "Number" is a reserved word. Maybe "lktAddress.Address"? "lktAddress.Address1"

    "lktCompany.PhoneNum" is another field that I would make Text, not Long Integer.

    You could delete 3 fields from "lktPilotQual" table since you have table "tOwnedAircraft".
    Delete fields: "lktPilotQual.Owned Aircraft1", "lktPilotQual.OwnedAircraft2" & "lktPilotQual.OwnedAircraft3". ("lktPilotQual.Owned Aircraft1" has a space in the name)



    I was thinking you could combine tables "lktCompanyAircraft" and the privately owned aircraft into one table ("lktAircraft") by having a new field "Ownership" with values of "Company" or "Private".

    I still think the relationship between aircraft and maintenance are backward in the relationship window.

  6. #21
    The_Learning_Curve is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    12
    Man, I caught a nasty bug.. been absolutely useless today.

    The order of relationships with aircraft and company aircraft.. I originally figured I would need the flexibility of connecting non company aircraft to a job so I put the generic aircraft table first, but that really doesn't make sense now. The only reason why I HAVE to have an aircraft connected to job information is for log purposes, I don't have to log non company owned aircraft, so it would be useless to have that option.But now if a job takes place, I'll HAVE to choose a company aircraft rather than the generic make and model that might be used by independently owned aircraft. Unless I don't allow referential integrity. Of course I'm just speculating since I can't even make a basic multi table form work yet.

    So other than some of the little issues you guys have mentioned with some of the tables and column formats and labels, my main focus should be figuring out how to properly create the relationships

    As Ajax mentioned, I seem to be confused with relationships and joins in queries. I'm also confused as to when a query is better to use for making a data entry form vs making a multi tabled form. If I used an Append query, if thats the right term for this, the query would automatically create a new record in all tables once I start typing in information for the master table right? Example, a query with "individuals", "address" and "company"... I start entering data into "individuals" fields and the query would automatically create new rows in the "address" and "company" tables. Of course I need to the option of choosing existing addresses or companies in some situations, but as far as how append queries work, is that right?

  7. #22
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    If I used an Append query, if thats the right term for this, the query would automatically create a new record in all tables once I start typing in information for the master table right?
    go back to post #12, do not pass go, do not pick up 200$

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Desperate help: Query in table
    By desmonda in forum Access
    Replies: 2
    Last Post: 08-27-2016, 04:53 PM
  2. Replies: 2
    Last Post: 05-15-2014, 07:37 AM
  3. In desperate need of help with my project
    By Jasmine0305 in forum Access
    Replies: 5
    Last Post: 02-11-2012, 05:06 PM
  4. Desperate Dan
    By Andyjones in forum Access
    Replies: 0
    Last Post: 01-27-2012, 07:09 PM
  5. Desperate Need of Help - Queries
    By Niki in forum Access
    Replies: 2
    Last Post: 07-25-2011, 10:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums