Creating my First Query (not very well)

[sorensjp]

All,



I’m an absolute Access novice in the US Navy currently stationed in Japan. I have some experience with Excel and now trying to move up to Databases. My goal (albeit very ambitious) is to create a better “Weapons Qualification” Tracker. The attached file C_VILLE MASTER GUN QUAL TRACKER (V0.91)PIVOT_TBL.xlsm. I created from an article from Excel MVP Trevor Easton web http://www.onlinepclearning.com/staf...xcel-database/. The password for the file is "Online".

Query – my first hurdle Ranks and Names. The attached Access DB file is what I’ve started with are just tables. And attempted to make Relationships. Here my goal is to understand how to make a cascading Form (don’t know the correct term). Ultimately, I would like to create a user Form to add, edit or delete the Members (Sailors). I just don’t understand how to setup the rank structures to build the base for the form. I could be going about this all wrong of course .

The Form needs to update a Members Rank without effecting the name:
tbl2Enlisted_Officer_Pick determines if the member is Officer or Enlisted.
tblRanks_Enlisted & tblRanks_Officer are the Rank and Pay Grades associated with the enlisted and officers respectively
tbl1Member_Names is self-explanatory.

Eventually on the Enlisted Ranks I would like to add the feature of “Ratings” (i.e. BMC, BM1, BM2, HTC, MMC, STGCM, STGSC, STGC, STG1, STG2, STG3, STGSN, STGSA…).

I appreciate any advice. Access is interesting and has a great many more features then Excel (or at least I think so).

C_VILLE MASTER GUN QUAL TRACKER (V0.91)PIVOT_TBL.xlsm.zip
ADMIN TRACKER.zip

V/r,
Jim

[June7]

Welcome to the forum and thank you for your service.

Don't look at Access as having more features than Excel. The apps are completely different for different purposes and really cannot be compared to each other.

On first look at your db, I would recommend only 1 table for all rank and pay codes regardless if officer or enlisted. The rank and/or pay grade code identifies whether officer or enlisted. You could have another field with "Officer" or "Enlisted" values if you want. Also, typos in some of the rank codes using zero (0) instead of O (look at 0-7 and 0-5, shouldn't those be O). tbl2Enlisted_Officer_Pick is unnecessary unless you create a category field in the rank/pay table.

I really don't understand how you managed to link an autonumber primary key field to a text foreign key field. Even if the Relationships builder allowed this, cannot be done in a query.

Doesn't make sense to link Rank_Type and Pay_Grade fields. These fields don't share common values. Same goes for Pay_Grade and Rank_ID

I think you need a better understanding of what primary and foreign keys are and how tables should be linked on them.

On the positive side, I am glad to see no spaces or punctuation/special characters (with exception of underscore which is acceptable) in naming convention.

The term you may have been looking for is form/subform arrangement (or nested forms) but so far your db doesn't require this. You might find cascading (or dependent) comboboxes useful down the road.