Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: Need Help With Report

    Good day. I have a db with two tables that I started building today. I am fairly new to Access and SQL so I need a lot of help. Here are the details:

    1) Table 1: Fields - LastName, FirstName, userID, emailAddress, Status (Active, Deactivated, Disabled), LastLoginDate (Ex: Thu Aug 11 21:00:00 GMT-0700 2011).

    *All fields are text fields and the primary key is userID.

    2) Table 2: Fields - EmailDomain (ex: yahoo.com), Region (a number between 0-24)

    *EmailDomain is text and Region is double

    I am trying to create a report that would populate the following:

    1) Have the following fields: LastName, FirstName, emailAddress, Region, LastLoginDate, and UserStatus (an unbound field)

    2) The UserStatus field must consist of the following entrys: Current (between 1-30 days), Password Expired (31-60 days), Account Disabled (61-90 days), Account Terminated (90+ days). The days are calculated from the LastLoginDate but that date is not a date field but a text field.

    3) The Region needs to be added to the report somehow in coordination to the email domain of the email address. Each email domain has an associated region number in the second table depending on where they are located in the state.

    4) Only want the records that have a Status of Active.

    Thank you in advance for the help in this.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off
    All fields are text fields and the primary key is userID.
    bad idea
    if its a numeric column then use a numeric datatype
    if its a datetime column use a datetime datatype

    give yourself a fighting chance n this and give your tables a meaningful name Table1 Table 2 don't cut it.

    you could assign the user status using a compound IIF statement in the query. or use a function to return the value or perhaps better yet join to a table that defines your user status. personally Id prefer the latter as it means nay changes in account status are handled by a data change not a code change. a data change is implemented by the user, a code change by the developer and requires much more effort (and such changes are usually demanded at the last minute by users when the developer is usually bombed out with other work). if you don't want to get to grips with the join then next up woudl be a user function (even though its probably slower than a compound IIF) as its going to be easier to refer to the function rather than havign to copy the same IIF to each relevant query.
    in any event to make the userstatus work you will need to get to grips with the Access datetime functions (one of the main reasons why you should store date time values in the datetime datatype

    to link to Table2
    you need to find a way to associate asnd email address with a domain
    several ways to do that
    either store the email in the user profile as two components with a fk to table 2. so that would mean say mig1980@yahoo.com being stored as mig1980 and the ID of yahoo.com in table 2
    alternativley write a function which returns the domain and use that in the query
    you could with some judicious use of string functions likt instr and rigth locate the domain in the query but that would require copying everytime you need to use that function. persoanlly Id leverage the power of SQL and for table 1 use a foreign key to the domain in table 2
    the region needs to be added to the report somehow.... well thats an odd request, either you know how the region is associated to the domain, or you don't. certainly I we don't know.
    according to your data model iuts associated to the domain (which frankly as we all know is pants). I could believe region being associated with a user, but not a domain. ferinstance yahoo, gmail, hotmail and others all have generic domains you hvae no ability to localise a user based on those domains. within other domains you have no notice of where a user might come from. granted there domain could be structured according to the relevent advice eg a company may use <site>.<country>.<company>.<tld> but I doubt it, its far more likely to be <company>.<tld>.

    so first off I'd suggest you redesign your tables, introducing new tables as required. you may need to refresh yourself on normalisation before doing so

    you also need to sort out your 'region', where do you get it fdrom, how do you display it and so on.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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