Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    14

    Unanswered: Complete newbie drowning... help?

    Hello to all on the forum,

    Ive been following the access forums for a couple weeks since I unwittingly took on an access project as a favour. I didn't want to post until I was really stuck, and here I am. I have a couple of simple questions, I think Im looking for advice on my methodology. Any suggestions would be great.

    Im designing a Contacts database for an organisation through Access 2003. Its fairly standard stuff. Name Position Address etc etc. A fewcomplications though, I am having trouble with.

    1. A member supplies 2 addresses (work and home) but I need to specify one as a mailing address. And provide mailing lists with that address. Im attempting to define this through using checkboxes in the table. Where do I specify? Would this be a query or in a report or form?

    2. Memberships can be honorary, prospective, corporate, personal. Depending on whats specified, (personal or corporate) a further condition is applied, ie. if the membership is valid or expired. this condition is dependant on when the membership was renewed. Do I set this up through a query?

    3. I need to supply a wide range of reports, and I need to be able to print as mailing reports etc, and these need to be user defined. eg. a report based on all members from a certain region. Do I have to define an individual report for each region? Or can I supply a facility to define reports through the use of dropdown boxes?

    I realise, these are very basic questions. I really hesitated about posting, cuz I wanted to sort them myself, but Im lost. I tend to be fairly lame with computers, this being the first time I have attempted something like this. Ive been beating my head against this db for 3 weeks and the Access Bible still reads like heiroglyphics to me. Anyway. they are three of the many questions I have. All input gratefully appreciated.

    Cheers.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Hello, and welcome. You're asking pretty broad design questions, and I'd rather not design it for you. I will try to give you some direction; these are my opinions at a quick glance - there are numerous ways to do each of these tasks.

    1. A member supplies 2 addresses (work and home) but I need to specify one as a mailing address. And provide mailing lists with that address. Im attempting to define this through using checkboxes in the table. Where do I specify? Would this be a query or in a report or form?
    A Yes/No field (checkbox) can be used in the table (Yes for Work and No for Home perhaps), or a number field that is a drop down (perhaps #1 for Work, #2 for Home). It stores the number, but displays the text on your forms. Use the form to allow the user to select the correct value.

    2. Memberships can be honorary, prospective, corporate, personal. Depending on whats specified, (personal or corporate) a further condition is applied, ie. if the membership is valid or expired. this condition is dependant on when the membership was renewed. Do I set this up through a query?
    I would set it up in the table, using a number field to store the type, with a list that attaches the text label to each number (like above). The valid or expired can be a Yes/No. On your form, make the "Valid" field Null if it doesn't apply, or have user select Yes or No when it does apply.

    3. I need to supply a wide range of reports, and I need to be able to print as mailing reports etc, and these need to be user defined. eg. a report based on all members from a certain region. Do I have to define an individual report for each region? Or can I supply a facility to define reports through the use of dropdown boxes?
    When the report is the same and only the selection of the data is different, use one report and filter the data as required. By "regions" I assume you mean groups of locations. You would have to define the groups seperately so that the query knows what is included in each region. If you're in the US, then you can do this by making a table that contains each State and assign each state to a region. A query adds the region to each address and then your report can group and filter by region.

    I realise these are very basic questions
    Thus the problem in answering them - the forum is better suited to specific questions.

    good luck,
    tc

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Nothing like soliciting opinions where everyone thinks their way is the best ...

    1: Generally, you want a table for the addresses and a junction table for each address associated to a contact/customer where that table will indicate home or work address.

    2. You can if you want ... You'll have to specify criteria to set the type of membership tho ...

    3: No. That's criteria for the report to operate on ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Quote Originally Posted by slider_ie
    Hello to all on the forum,

    1. A member supplies 2 addresses (work and home) but I need to specify one as a mailing address. And provide mailing lists with that address. Im attempting to define this through using checkboxes in the table. Where do I specify? Would this be a query or in a report or form?
    You'd use a query to define the Parameters. However, it depends on the backend of your database and how you are storing the information, as to how you structure your query.

    Personally, I'd have a lookup field in my table called "Select Address for Mailing List" and have the options "Home";"Work"

    Then i would set up 2 queries, one query would filter all the options where the criteria is "home", and add in all the field from that contact

    The second query filters all those people with "work" selected and pulls out the work addresses for those.

    you'll have to run 2 queries, but its the best way i can think to do it thats quite quick to set up. You can create a macro to handle the whole process, even exporting to word as a milmerge ;-)

    Quote Originally Posted by slider_ie

    2. Memberships can be honorary, prospective, corporate, personal. Depending on whats specified, (personal or corporate) a further condition is applied, ie. if the membership is valid or expired. this condition is dependant on when the membership was renewed. Do I set this up through a query?
    well in this case, start a new quer, drag in your membership details fields

    for type, in criteria type; "Personal" OR "Corporate"

    where the membership date is under the criteria type >date()

    That'll return all the valid memberships, for all the expired type <date()

    if your renewal date is say like last year and you want to base it on that date, you'll need >date()+365

    Quote Originally Posted by slider_ie
    3. I need to supply a wide range of reports, and I need to be able to print as mailing reports etc, and these need to be user defined. eg. a report based on all members from a certain region. Do I have to define an individual report for each region? Or can I supply a facility to define reports through the use of dropdown boxes?
    You can!!!

    Create a new table lookup_region, and create a new lookup field that has your regions in it (or gathers the regions from another table) close & save

    Create a new query qry_region_search, add in the new table, and drag in the single field you have. Then add in your memberships table and create a link between the regions from region_lookup and the region in memberships.

    Don't forget to drag all your data to the query that you wqish to display.

    You can then create a new form, bind it to the table lookup_region and add in the field.

    Disable the navigation buttons and scrollbars in properties etc.

    Open this form in form view, select a region and close.

    Open your query and et voila, it should all be there.

    Good luck any probs/questions just ask

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  5. #5
    Join Date
    May 2006
    Posts
    14
    Thats great advice guys. Dan in particular thanks for taking the time.

    I think I have the mailling list problem licked. But I think I have created longterm problems in my table setup. I wanted to reference both Home and Work address in the same table. Now when I tried to relate the Area to another table, it didn't like me duplicating the relationship (one for home, one for work) so I created 2 essentially idenctial Area tables (one for home, one for work). This can't be right, especially if trying to use this table as a lookup parameter later. Ideas?

    As for the membership status issue. Bit of a headache, My fields in the table are as follows:

    Join Date (static info.) Date Renewed (user input) Renewed From (continuous from expiry date) Expiry (expression) and Days to Expiry (expression).

    Am I right in trying to build the "Expiry" and "Days to Expiry" as fields in my table? Where do I incorporate the expressions? At the table level? If not, where, and is there a need to reference it back to the table level? Or would a report be sufficient? aaaaaaaahhh!!!!!

    Im picking it up as I go, and finding the tricks used at every stage are helpin my overall knowledge. Its what I would call an exponential learning curve. Thanks for lettin me bug ye!

  6. #6
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Quote Originally Posted by slider_ie
    Thats great advice guys. Dan in particular thanks for taking the time.
    Now when I tried to relate the Area to another table, it didn't like me duplicating the relationship (one for home, one for work) so I created 2 essentially idenctial Area tables (one for home, one for work). This can't be right, especially if trying to use this table as a lookup parameter later. Ideas?
    I tried in access to set up something similar to test what you mentioned. It seemed to work fine for me.

    I had one table "Counties"
    and another "Options" with 3 fields "Option 1" "Option 2" "Option 3"

    I was able to call the county in each opetion field for each record ok. I used the lookup wizard and all worked well. If your fields are named the same This may cause confilt. so have Work_area and home_area, and them both call form the same table of areas. Should work fine... in thoery! ;-)

    Quote Originally Posted by slider_ie
    Join Date (static info.) Date Renewed (user input) Renewed From (continuous from expiry date) Expiry (expression) and Days to Expiry (expression).

    Am I right in trying to build the "Expiry" and "Days to Expiry" as fields in my table? Where do I incorporate the expressions? At the table level? If not, where, and is there a need to reference it back to the table level?
    Expressions are best done in a query or in a report. You can base all this on the date you have already "subscription date" as this would remain the same throughout the term of the membership.

    In a new query, where it says field, you'd have your expression there, it would look something like:

    expiry: =[subscription date] + 365

    Then to calculate the days to expire:

    DaystoExpire: [Expiry]-Date()


    Quote Originally Posted by slider_ie
    Im picking it up as I go, and finding the tricks used at every stage are helpin my overall knowledge. Its what I would call an exponential learning curve. Thanks for lettin me bug ye!
    Thats how we all learn bud! ;0)
    sometimes simple is best.... and i'm just a simple fellow.

  7. #7
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Talking Just a little extra help! ;-)

    This has the examples (all working) of what i have explained to you.

    Dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  8. #8
    Join Date
    May 2006
    Posts
    14

    Thumbs up Cheers

    Thats brilliant Dan, thanks for the sound advice. Managed to get that flowing a little better now. Have made huge progress in the last couple days. Im starting a few reports now, in anticipation Ill have to go back and do it all again anyway. I may post a new thread in the not too distant future. Thanks for all the help dbforums.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Excellent! And thanks for the feedback.

    Sometimes we wonder if we may have offended somebody with our advise or ideas - they never post a reply!

    tc

Posting Permissions

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