Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Design concept help (A2k)

    Pat, (or any other kind soul with helpful advice)

    I'll lay it out for you as best I can:
    Keep in mind -this is my first one.

    I have a large conference coming up in Novemeber. It's attended by several hundred people, mainly physicians for continuing education purposes. I have a table for students, guests, classes, billing, price structure. This is all many to many, so I have junction tables for StudentClasses, StudentBilling. I have a students form, with three subforms, guests, classes and billing.

    So, the student personal info is entered -then the guest info( if they have any) , then the class info, and billing information. One problem I'm encountering is that there are 27 different price possibilities depending on the types of classes they are taking, how many days they are going to attend the conference, if they are retired, if they are part of a group, bla bla bla. I've set up a pricing table, but don't have it related to anything yet, as I'm not sure if this should be a standalone object. I'm thinking that the pricing info should somehow be tied to a student "type", but I have NO CLUE how to make that happen, due to the number of possibilities.

    After the registration is complete a confirmation email/receipt will be sent to the student, listing the dates and times of the classes, the price paid, the guests if any.

    Each day I want to be able to print a report that lists all students by name and the amount and method of payment.

    I will need to print name badges for all participants, regardless of "type".

    After completion of conference a certificate will be emailed to the Doctors only, (other student types have no need for it).

    What I'm rethinking is my tables. (and forgive me, as I'm still thinking linearly). It seems to me that each participant should be listed in one table, and there would be a combo or listbox with the different student or participant types. Then I would have another table of those types, and the primary key (autonumber) would tie everything together.

    I'm probably way off track here, but it just seems easier this way. I'm having a hard time getting over Excel.....So inefficient as a database, but so much easier for me <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

    Please, some guidance on how this would be better set up!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database Design concept help (A2k)

    What you have described here should be doable.

    Can you have a check that pays for more than 1 student?
    Can you have a check and Credit Card that can pay for 1 or more students?

    Are participants in fact students?

    These questions will effect the structure of your tables.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Design concept help (A2k)

    Hi Gabi,

    Pat has already asked some pertinent questions. You might also look back at the beginning of the thread starting at <post#=260773>post 260773</post#>. It should be possible to compute the conference fee from the various characteristics of the participants. Whether this can be done in a query or in VBA depends on the exact way the fee is determined. If you want help with it, you'll have to provide precise and specific details.

  4. #4
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design concept help (A2k)

    What you have described here should be doable.

    <<Can you have a check that pays for more than 1 student?>> Yes, many times an orgazination will pay for a group of students
    <<Can you have a check and Credit Card that can pay for 1 or more students?>> Although not likely, it is possible, so the capability needs to be there.

    <<Are participants in fact students?>> Not necessarily-There are faculty, sponsors, and guests. Faculty and sponsors pay nothing, guests are paid for by the student bringing them.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database Design concept help (A2k)

    I guess my question about students was not clear enough. Does the table Students hold students as well as faculty, sponsors and guests?
    As regards the other questions, the DB I posted in your other thread defines those tables for multiple payment types for multiple students, so you will need to take that design (or something similar) into consideration.

    >>Each day I want to be able to print a report that lists all students by name and the amount and method of payment.<<
    This was from your first post. How do you propose to do this where a corporation pays for multiple students?

    Keep us up to date with your design before you start, because if you start too soon before cementing the design you could be in all sorts of trouble, believe me I know, I have done it and it hurts.

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design concept help (A2k)

    I have separate tables for students, guests, sponsors, faculty, staff. As for the report, I have no idea how I propose to do that, I just want to!! <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>

    I'll take a closer look at what you posted and see how it works--Fortunately, it's early enough to scrap this and start over. Or I could always turn it over to our programmers to do, but I WANNA DO IT MYSELF!!

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database Design concept help (A2k)

    You mentioned in your first post that:
    >>I have a table for students, guests, classes, billing, price structure. <<

    How do the other tables (guests, sponsors, faculty, staff) relate in this structure? Do sponsors have billing, price structure etc? If they do, then it may be wise to include these in the one Students table and separate them via a code (S=Students, G=Guests, etc). If I'm way off here, just belt me between the eyes.

    As you see there can be a lot of questions that need to be entered before starting.

    >>Fortunately, it's early enough to scrap this and start over. Or I could always turn it over to our programmers to do, but I WANNA DO IT MYSELF!!<<
    You will end up doing us out of a job. <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

  8. #8
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design concept help (A2k)

    Sorry I wasn't more clear-The sponsors do NOT have billing or price structure. Nor do the guests, faculty or staff. I do need to have them included as participants however.
    <How do the other tables (guests, sponsors, faculty, staff) relate in this structure? >> The guests are related to the student, the faculty are related to the classes, which are related to the student. Staff and sponsors are not related to anything-they would be standalone tables. However staff and sponsors do need name badges, just as everyone else does.

    <<You will end up doing us out of a job. >> Well, this is my one and only <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> downfall-I'm a bit of a control freak and always want do everything myself-much to the consternation of anybody who works with me!!

    Thanks for the advice

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database Design concept help (A2k)

    >>Well, this is my one and only <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> downfall<<

    Are you sure? (Just kidding)

  10. #10
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design concept help (A2k)

    Yep, other than that, I'm perfect!!

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database Design concept help (A2k)

    I knew that. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    How's your database design going?

  12. #12
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design concept help (A2k)

    Well, right now it's not really going anywhere-I've been sending out sponsor solicitation letters for 2 days (utilizing my Excel list from last year to accomplish my mail merge) I've imported those tables to my data base, but have not had the courage to try and accomplish this timely task with Access. I've actually just finished that for the day, so I'm gonna go back in the previous thread and open the data base you sent and see what I can do with it.

  13. #13
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design concept help (A2k)

    Well, I checked out the tables and all looks good-I had deleted some of the tables for that copy that I sent over as they weren't necessary for my query design. Now I've got to go back to the drawing board. If you were working with what I've described, how would you set it up? (if you can easily explain it, and have the time). I had a great instructor when I took the classes, and he told us that our first databases would not work, it's a lot of trial and error-I know what I want it to do, I just don't know how to make it do it-I've got NO experience with VBA (other than the little I've been helped with here), so it's the automation stuff that's hanging me up. Our programmers told me to write "functional and technical requirements" and hand it over to them to do, but I don't even know how to do that! I've told Hans before that I feel like I'm in a foreign country and don't speak the language! If only it were as easy as

  14. #14
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design concept help (A2k)

    OOPS I mean to add <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23> !

  15. #15
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design concept help (A2k)

    Pat, if you get a chance, take a look at how the entire database is set up and let me know if you think it will run efficiently, or if I should change things. Thanks
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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