Need Help with Report

[mig1980]

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 in the report that have a Status of Active.

Thank you in advance for the help in this.

 

[GinaWhipp]

Which is the field that connects those two tables?

 

[vbaInet]

Which is the field that connects those two tables?

Hi Gina,

I was going to ask the same question and then I noticed it was mentioned in number 3.

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.

 

[vbaInet]

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.

Is userID also Text? What kind of values does it hold, numeric or letters (i.e. alphanumeric)?

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

*EmailDomain is text and Region is double

You don't need to make Region a Double. Just make it an Integer or a Byte.

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.

You will need to change the format of your LastLoginDate to a proper date for this to work. For example you can split the data into two fields, one to hold the real date part and the other to hold the GMT part. Then combine both (i.e. concatenate them) in a query to use in a report. The DateDiff() function is what you will use to calculate the difference in days and then you can use the Choose() function to return the text to be displayed based on the number of days. This you do in a query too.

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.

Also in the same query, use the InStr() and Mid() function to return the domain part of the e-mail address then you can join this calculated field to table 2.

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

In the same query, just enter "Active" in the Criteria row under the Status field.

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

Once you've done all of the above, you will now be able to drop the Region field from table 2 into the query and use this query as the Record Source of your report.

One last note: the Status field can be further normalized. You can create a table that has two fields, ID (Number) and Status (text) where -1 will represent Active, 0 - Deactivated, 1 - Disabled then link this up to table 1 via the numeric field. Or use the Format property to format the Status field accordingly. The Format property for numbers has a format for Negative values, Zero and Positive values hence my reasoning above.

 

[GinaWhipp]

@vbaInet... missed that!