Need help with my database for the Marine Corps

[Xavier!3]

I am currently trying to use height and weight ratios to determine if Marines are under or over weight according to our tables. With that I have a few different columns: Height/Weight/Minimum Weight/Maximum Weight/Over or Under are the column headers. I currently have IIF expressions in the min weight and max weight criteria to determine the specific weight ranges per individuals height which is a mandatory part of this situation. I am at a loss to figure out how to show the amount of difference if someone is under or over their weight range. If they are under the result should appear as a negative number, the number being how much they are over weight. If they are over weight the number should appear with a + symbol and how much they are over weight. This is incredibly time crucial if anyone has any solution or advice to find a solution it is HIGHLY appreciated.
Semper

 

[Montez659]

Xavier, Welcome to the board!

Are you trying to view all of this information via a table or have you looked at the reports function? You could easily set up all the formulas that you need within a generated report and keep the table storing the raw info, such as name, rank, serial number, height and weight, and then have the report calculate the minWeight and maxWeight and Over/Under. Much easier to do within a report.

 

[Xavier!3]

Thanks for your reply. That sounds like a great idea but two things. Would the table still show the data calculated in the form (we would need to be able to print the table for further references and paper copies, you know how it is copy of a copy of a copy thing). Two how would I go about adding that auto calculated data into a form?

 

[Montez659]

The information would not be stored in the table, and I am not sure where the form comes into play, but you could easily print off a report with all relevant info that you are looking for including calculations.

What you are wanting is possible, just a bit over-engineered. This is the question that I think - why store all that extra info? The purpose of the db is to store the raw info, and use things as queries and reports to display what you need.

That said, I don't claim to know the situation in which you are operating. If you REALLY need to store all that info, you could check into running an update query. I am only a beginner in update queries so I probably couldn't help much in that aspect but basically the update query would take your existing table and you could run some calcs to update the other columns of the table.

If you don't really need to store that info in the table, I can help you with getting it into a report. Other than that, hang around MrExcel for a while and someone that knows more about Update queries could get you started up.

And thank you for serving our country!

 

[Xavier!3]

Alright I had totally forgotten about the reports. If you could walk me through how to set it up in a form so that the information would be viewable/printable in report format that would be amazing.

Its a duty I have honor serving

 

[Montez659]

The first thing that I would do is run through the report wizard. This will give you a basic template to display all of your info. Select the table that you want to use, and then select all of the fields. You probably don't need to group by any info at this point and how you want the data sorted is up to you. Look at the Design when you are finished. There might be items on there that you don't need, but they can be deleted later.

When you get to the report design, you will see many different levels "tiers" to it. The "Detail" area is where you can add some unbound textboxes to insert your formulas into. You will want to put the formula into the "Control Source" section under properties. Then, when you run the report, it will take the info that you have a compute the formulas and spit them out into the text box. Such as the formula you have for min weight, just plug that =iif( into that box. This should get you started in the right direction.

What version of Access do you have?

 

[Xavier!3]

Alright sounds good. Its access 2007. If I have any issues along the way Ill repost here however we are closing for the day I really do appreciate your help Montez you truely are a life saver.

 

[Montez659]

Not a problem, let me know how it goes.

And you are the real-life life saver!