Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2007
    Posts
    8

    Question Unanswered: Using form value as column name

    I've been pulling my hair out over this, so if the following os possible please tell me how. Numerous searches have yielded no results.

    I have a table (Enrollment) that has several columns that are yes/no types (along with an ID column). The columns are named Fall2006, Spring2007, etc.

    What I would like to do is create a query that reads a value from a combo box in a form. For example, if I want all IDs where Fall2006 is true, the person would select Fall2006 in the dropdown, press a button and see ONLY those IDs where Fall2006 is true.

    I cannot figure out how to use the dropdown value as the column name in the where clause of the query. Is there a way?

    I started off with

    Select * FROM Enrollment
    Where Forms![Form Name]!FieldName = true;

    I've tried various combinations of quotes and ampersands (much like is necessary in ASP to break out variable names in text strings)

    Please help me end this frustration.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The simple answer is that you'd have to build the query in VBA to get the dynamic field name. The greater issue is the design of your table. Having a field for each period violates normalization rules, if you're familiar with them (if not, you should read up on them). Generally speaking, you want a design where each period of time is a record, rather than a field. If you post the specifics of you db, one of us can probably point you in the right direction.
    Paul

  3. #3
    Join Date
    Jul 2007
    Posts
    8
    The database is a classic student database for a college. The enrollment table tracks when they were actually enrolled in school. The yes/no fields were the only way I could think of to keep track of the students enrollment. Any other way I thought of created problems by having multiple values in a field (normalization problem).

    When you speak of VBA, I can only guess you are referring to Visual Basic. The only tools I have at my disposal is Access. I'm using Access forms and reports to make this happen. Not the most practical but the best I can do based on their budget and timeframe. Once my primary project slows down, I hope to be able to make it web-based.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No, VBA is Visual Basic for Applications, which is included with Access (and other Office programs). Hit Alt-F11 and it should pop up.

    A properly designed database should not require the addition of a new table field every semester (and the associated form/query/report design changes that would go along with that). You are correct to avoid multiple values in one field. I would envision a table like:

    Code:
    StudentID   SemesterID
         123           1 (representing Spring 07)
         456           1 
         123           2 (Fall 08)
    Obviously along with that would be student and semester tables.
    Paul

  5. #5
    Join Date
    Jul 2007
    Posts
    8
    How would you deal with the massive table size that occurs doing it that way?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Since it would be normalized...I suspect I'd have it a lot easier than the difficulties you will face having each semester as a field in one table. You will run into similar issues as your original question here with everything you try to do. And basically, you're talking about a table that would be "longer" (more records) but not as "wide" (more fields), so I don't think there would be a significant size difference.

    At the end of the day, you have to do what you're comfortable with.
    Paul

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jwsmith4
    How would you deal with the massive table size that occurs doing it that way?

    ...as a one off manual procedure
    id make changes to a copy of the app to reflect the new design
    id take copious notes of what tables/columns/queries i've changed
    i'f run a series of update queries (or if I had to create multiple records out of a single one then I'd use a seeries of insert/append queries) or VBA procedures
    if the app is local then I'd do it on a COPY of the db making the apropriate changes to the tab


    the chanegover / downtime period should be around 2..4 hours, possibly 1 day, but there is a great deal of work invovled before you can do the changeover.. every form/query/report will need reviewing to see if its impacted by the chanegs (and for somehtgn so key I'm guessing virtually every on ewill.

    it will be a pain, so its over to you.
    is this a short term I just need a fix problem, or do you want to do the 'right' thing so the db has an extended life.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2007
    Posts
    8
    The whole problem occurred because of a redesign. It was just realized that the graduate and undergraduate students need to be separated as data would be overwritten if a student was an undergrad and then came back for graduate studies. So far the redesign is at day 4 and not even 50% complete yet. It seems like every task in the redesign takes a monumental amount of effort in Access. Were this MySQL with a custom designed interface with PHP, I don't think the struggle would be nearly as hard. Access just seems so limited. VBA is really not an option as I don't know how to use it and can't take the time right now to figure it out.

    It sounds like pbaldy's idea for a table would work much better than my current solution, especially with regards to the current form I'm working on. However, I fear that table will eventually become unwieldy with the addition of 800-1000 records each semester plus summer school students.

    I'd say this is not a short term fix situation. I want the lifetime expectancy of this database to be in the 3-5 year range so that I can complete the main projects I was hired to do and do minor maintenance as necessary. However, I need to get this out of my hair as quickly as possible in order to concentrate on other priorities.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jwsmith4
    So far the redesign is at day 4 and not even 50% complete yet.
    You really shouldn't be mucking about with forms on day four of an enrolment database. It might be tempting to get things out of your hair but you will find that this db will be like some chewing gum stuck there if you don't design the tables correctly.

    The fact is that design will be a nightmare whether you are using Access or MySQL with a custom php interface.

    The below link is a popular one round here and is the absolute minimum you should be familiar with before embarking on a database project. I would also stringly recommend you finalise your normalised design before playing around with forms. You can only concentrate on so many things at a time and the presentation should be decoupled from the database as far as you are able
    http://r937.com/relational.html

    Also - I personally would not have two tables for undergrad and post grad students

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jwsmith4
    How would you deal with the massive table size that occurs doing it that way?
    Sorry - can't resist this one either.
    Proper db design is not about making tables as small as possible. As it happens, a side effect of normalisation is often to reduce the amount of data you store but this is not the point of it.

    Now - pbaldy's design means you will have a narrow table with more rows. Your design is a wide table with less rows. However, yours will actually end up "larger". Why? Because a row will only be added to Paul's table when there is a data to be stored. A column added to the table will appear in every record no matter the state of the student. So, in 5 years time when your current intake has left their records will still be getting larger because you will still be adding fields to your table. Whereas Paul's growth would be more or less linear (depending on changes to your recruitment numbers), each column you add will add additional data proportional to the amount of data you already have in the table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    .....Also - I personally would not have two tables for undergrad and post grad students ....
    Amen to that, a course is a course, a student enrollment is a student enrollment irrespective of what degree they are studying.


    academic registration & enrollment db's... still brings me out in a cold sweat after all these years.......
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jul 2007
    Posts
    8
    The tables are set with the exception of the enrollment table.

    I am now in the process of redesigning the forms and reports to work with the new tables. I can see that the way I currently have the Enrollment table is not the best.

    Originally all the students were lumped into one table, but this caused issues when undergraduates became graduates. If a student became a graduate student, the data was over written when imported into the database. The department has a need to see ALL student records for a student at the same time which is impossible for me to present based on strictly using Access forms and reports if there are three entries for a student in the student table (undergrad, master, phd as an example).

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    academic registration & enrollment db's... still brings me out in a cold sweat after all these years.......
    Having cut my teeth in a college it brings me out in a soothing wave of nostalgia
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jul 2007
    Posts
    8
    Quote Originally Posted by healdem
    Amen to that, a course is a course, a student enrollment is a student enrollment irrespective of what degree they are studying.


    academic registration & enrollment db's... still brings me out in a cold sweat after all these years.......
    This is not your typical academic enrollment database. This database tracks teaching certification.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jwsmith4
    This is not your typical academic enrollment database. This database tracks teaching certification.
    The nature of the certification is likely to be irrelevent to the design (apart from perhaps a few fields). The fact it is about enrolments and certifications is what will probably drive your design.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •