Hi to all,

I'm new to both this forum and MS Access Db writing so would appreciate any help that can be offered.

I am trying to write a Db that relates / evolves around Military Rank structures. As this Db will be used to store Unit data (Number, Rank, Name etc) it should be capable of creating what we call Nominal Rolls (Department lists that are in a specific Rank order). Unfortunately, if I were to use just the power of the PC to create these lists it would not work. Military ranks must be placed in strict order of seniority; for example, A Maj (Major) must appear before a Cpl (Corporal) on the nominal roll; however with normal sorting this would not be the case as it would be done alphabetically without any filtering method. Therefore I have designated a Rank ID column in the data capture for each individual. An example of this is: Maj = 04, Cpl = 12, etc... This method allows the strict ordering method as required.

I have 2 tables, with associated colums of:

Table 1:
Tbl_Course Details

Columns:
Rank
R ID

Table 2:
Tbl_Ranks

Columns:
Rank
R ID

Relationships have also been created between the two Tables.

I enter data for each individual on a single form and have created a combo box which updates [Course Details.Rank] through selection of the row source from the table [Ranks.Rank]. This works fine. Where my data entry falls down is that I cannot get the Rank ID [R ID] column of Tbl_Course Details to automatically update after I have selected the required individuals Rank from the combo box on the form. I have sucessfully used (in an unbound Txt Box) "=[mil rank].[Column](1)" on the form to show the correct Rank ID when selected, however I cannot get the form control to store this data in [Course Details.R ID]

I understand that this data should not be stored in this way as it is considered bad practice, however I feel in this instance it must be as there is no other workaround that I feel I can use at this stage.

I would really appreciate any help that anyone could offer me on this as its starting to drive me insane with frustration! If you can help, then all I would ask is that you explain what is required from a basic users perspective and (hopefully) include any code that I require.

Many thanks in advance,

Dave

Hi Dave,

(IMG:style_emoticons/default/welcome2UA.gif)

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

You are correct that the rank ID should not be stored in conjunction with the actual rank in multiple tables. Your issue is very common and the "best" workaround/solution I have used in such cases is similar to what you have implemented. It goes like this:

tblPersonnel
PersonnelID, PK
FirstName
LastName
RankID, FK
etc...

tblRanks
RankID, PK
RankName
SortOrder

The SortOrder field in my example is, I think, the equivalent of your RankID field (or R ID). Here's how I use it:

SELECT RankName, LastName, FirstName
FROM tblPersonnel
INNER JOIN tblRanks
ON tblPersonnel.RankID=tblRanks.RankID
ORDER BY tblRanks.SortOrder DESC

That way, I don't have to store the ranking order in more than one table.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

theDBguy,

Many thanks for a quick answer - appreciated!

I am using MS Access 2003 (apologies. lesson quickly learned!)

I think I understand your answer, however a few pointers would be further appreciated:

1. Does your solution store the R ID in tblPersonnel (my tbl_Course Details), or is it just identified against the tblRanks?

2. How and where do I use the source code you posted?

3. Does this code update the tblPersonnel entry for R ID on my form?

Sorry if these are very basic questions, but I am very confused over this.

Many thanks again,

Dave

Hi Dave,


I would store the R ID in only one table - tblRanks.



In a query for your forms or reports where you need the data to show up. For reports, you will also have to adjust the Grouping and Sorting to sort by R ID because reports ignore the Order By clause in the query.



No it does not because, as I had said before, there is no need to store the R ID in tblPersonnel. It's actually against normalization rules.


Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

theDBguy,

Many thanks for your help, I'll try your suggestion and see where this leads me.

Many thanks again,

Dave

Hi Dave,

(IMG:style_emoticons/default/yw.gif)

We are happy to help. Good luck with your project. Let us know how it goes...

theDBguy,

Thanks, I now understand the importance of the code and what it stands for.

However, having tried to understand where the Select Qry code is typed, I just can't grasp it; is the code placed in a query (and where/how). I would appreciate if you could explain in very basic terms where to place the code and how to use it. This would be great if you can. Sorry for being a pain!

Dave

Hi Dave,

The code I posted is very specific to the sample table structure I explained earlier. If you have the same exact table and field names, you can easily do the following to use it:

1. Create a new query and cancel out of adding any tables.
2. Go to SQL View and then copy and paste the code over what shows up in the window.
3. Run the query to test it and then save it.

If you have a different table structure than my example, you can try the following steps:

1. Create a new query and add the two tables to display
2. If a relationship line does not show up automatically, drag the Primary Key field from the Ranks table into the Foreign Key field in the other table
3. Drag the fields from each table that you want to show up in the query onto the query grid columns
4. Finally, drag the sorting field from the Ranks table onto the query grid and set the sorting sequence to the order you want
5. You can also uncheck the Show box for the sorting field if you don't want to see this column in the query
6. Run the query to test it and then save it

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

theDBguy,

Perfect, many thanks!

I have now realised that this all evolves around Relationships, and that by creating the query correctly I can manipulate data in the same way as the code does but without storing it.

Many thanks again,

Regards,

Dave

Hi Dave,

(IMG:style_emoticons/default/yw.gif)

Glad to hear you got it sorted out now. Good luck with your project.