How to Show different columns based on data in a field?

Dirtsnap

New member
Local time
Tomorrow, 07:24
Joined
Jun 10, 2009
Messages
7
Hey guys,

I am basically trying to show different columns based on information in 1 of my tables.

So for example if the data = 1, then show these columns, if it equals 2, show these different columns, if it equals 3 show different ones again.

I have been trying to use the IIF() function to do it but haven't had any luck so far. This is the code i have been trying to use:

Code:
SELECT IIF(membership.Goals="Lose Weight", 
(table1.Cardio), IIF(membership.Goals="Get Fit", (table1.Cardio, table2.Muscle_building_exercises)), 
(table2.Muscle_building_exercises)

FROM membership, table1, table2
WHERE membershp.membership_no=[membership number?]
Note i have been making a gym database and i want it to be able to show different exercises based on the person's goals.
 
The most proper way to do as you are wishing is with proper JOINS between the tables ....

Would you care to share your table stuctures as well as as small snapshot of data for those tables?
 
gym.jpg


So that's my table set up and the 2 actual tables i have data in so far. This is for a school project so i have made up all the data, but didn't think it would be neccessary to put data in the workout and program tables to get this query to work.

I basically want this query to be able to generate a list of exercises for a person based on their goal. I am going to make it pretty basic, only 1 cardio exercise if their goal is lose weight, cardio and 1 of each type of exercise (arms, legs, chest, etc). then if their goal was build muscle i want it to ignore cardio and just do 1 of each exercise.

I have gotten the query to randomly pick a exercise from each category, but am having trouble showing the columns which relate to the person's goal. My full Query code so far is

Code:
SELECT IIF((membership.Goals="Lose Weight"), (Cardio_Generator.Cardio), 
(IIF(membership.Goals="Get Fit", (Cardio_Generator.Cardio, Abs_Generator.Abs, Arms_Generator.Arms, Back_Generator.Back, Chest_Generator.Chest, Shoulders_Generator.Shoulders, Legs_Generator.Legs), 
(Abs_Generator.Abs, Arms_Generator.Arms, Back_Generator.Back, Chest_Generator.Chest, Shoulders_Generator.Shoulders, Legs_Generator.Legs))

FROM Cardio_Generator, Abs_Generator, Arms_Generator, Back_Generator, Chest_Generator, Shoulders_Generator, Legs_Generator, membership 

WHERE membership.membership_no=[What is the membership number of the person you wish to make a program for?]
 
You posted while I was writing the following. I see you are mostly on the right track but some of what I wrote still applies.
Especially normalise your goals.


Dynamic column selection is not the done thing and indicates you have an inappropriate data structure.

You also should normalise the data so that you are not testing against the name of the goal but a key. Imagine if you slightly misspell a goal on a member's record. It won't appear in your report because it won't match. You would also end up with many variations of the goal.

Put the choices along with a key in a table which will become the Row Source for a combobox on your member form allowing you to choose the predefined goals.

Same with both the exercises and their types.

This what I would suggest from what I imagine you are doing.

Member table should hold the personal details against a member key. FirstName, LastName, Address1, Address2, City, DOB phone etc. Nothing about their goals or exercises.

Goal table would have GoalID, Type and GoalName. This would be the goals that could be selected. Type would be a key from the GoalType table.

GoalType would have TypeID, TypeName(Cardio; Muscle Building etc)

Another table records the goals assigned to the member. It would have the key from the member record and the goal key. Each goal and member combination has a different record. You might also include information such as date started and the progress towards this goal in this table.

Another table would maintain detailed information about the exercise sessions. Each record would hold the member key, exercise key, date, repetitions etc. Maybe even heart rate information etc

Remember the idea of tables is to store data. It isn't about presenting it in readable ways such as you have described with a field for each type of exercise and trying to turn on columns as required. Presentation is the job of forms and reports.

Don't be afraid to make many tables with many records in them. Hundreds of thousands of records can be searched in seconds. It is much easier to relate and retreive the information when the data is properly structured.
 
Last edited:
I am not a gym user so i am not quite clear on how things should be related.
However it appears to me that Exercise and Program tables should be related by a Type table that shows which exercises have particular workout properties.
 
so is everyone saying its not a problem with my code? its a problem with my tables?
 
My goals can't really be typed in wrong, they are put in from a combo box which has 3 options, "Lose Weight", "Build Muscle" and "Get Fit", so assigning numbers for these wouldn't really help a whole lot i don't think. all i really want to know is if there is a problem with my code? cause i don't really have time to rebuild my database...

Thanks for the help so far guys.
 
Oh yeah PS, when i run it i get the error:

Syntax Error (missing operator) in Query Expression "the whole query" then when i click OK it highlights "FROM".
 
Your tables are not designed well ... specifically, your Program table is not good. You need to separate that into two tables ...

Program
----------
ProgramId (Autonumber, PK)
Program_Code (Set as a key field for a Unique Index)
ProgramDesc

ExcercisedArea
-------------
ExcercisedAreaID (Autonumber, PK)
ProgramID (Number/Long, FK to Program.ProgramID)
ExercisedArea (Text, 50) --- has values like "Cardio", "Chest", "Arms", etc ...

---------------------------------------

Ultimately, In designing table structures, you will not have a Primary table (Excercise) related to a Foreign table (Program) with more than one Relationship.

You have 7 distinct relationship keys between the two tables in question ... that is bad ... a properly designed schema will relate tables using a single key.

Note: a "Key" is a field or group of field, that when used, are treated as one value ... so you can have a relationship based on multiple fields, but the fields used for the Primary Relationship Key must match in quantity and type to those of a Foreign Relationship Key ...

I know that sounds verbose ... and probably confusing ... did it make sense to you?

Can you post your database ... with no data ... and I (or others) will probably be glad to set you on the right path toward what is called "Normalization"
 
Oh my! .... I must apologize ... I have had this post on my screen since the wee hours of the AM ... the last post I saw was the Relationship Diagram .... So ... much conversation has occurred since then and I am sorry to post duplicate information ... I left my second post on the thread in the hopes it may add to conversation ....


.....

>> so is everyone saying its not a problem with my code? its a problem with my tables? <<

You would not need code if your tables were correct ... :) ... but, for what its worth, I do not like nested IIf's, IMO, you are better off with the Switch() function.... but that is not a recommendation to use it in this case ... you really need to work on your tables ...
 
thanks heaps for helping datAdrenaline,

yeah my table are a bit weird, started out alright, but then i foresaw problems so i tried to change the tables around, but i think i just made it worse. lol

I will try and make the tables the way you said:

Program
----------
ProgramId (Autonumber, PK)
Program_Code (Set as a key field for a Unique Index)
ProgramDesc

ExcercisedArea
-------------
ExcercisedAreaID (Autonumber, PK)
ProgramID (Number/Long, FK to Program.ProgramID)
ExercisedArea (Text, 50) --- has values like "Cardio", "Chest", "Arms", etc ...

what do you mean by ProgramDesc though? program descending?
 
And i managed to upload my database to rapidshare, too large to make it an attachment (even after removing all pictures, forms, reports, queries, macros and data), but i put it in a .rar and its pretty small now :)

Code:
http://rapidshare.com/files/243029303/Gym.rar.html
 
My goals can't really be typed in wrong, they are put in from a combo box which has 3 options, "Lose Weight", "Build Muscle" and "Get Fit", so assigning numbers for these wouldn't really help a whole lot i don't think.

Although it isn't the source of the problem, it would still be good design practice to normalise the goals. It saves data storage space and processing time. It is much faster for Access to compare numbers than strings.

Presumably you have the combobox rows populated from a simple list in its properties. When you want to add more goals it is easier to edit if the names are drawn from a table. The table can also hold display ordering information.
 

Users who are viewing this thread

Back
Top Bottom