Comparing 2 rows in access

[analyst1]

Hi,

I need some help with a report i'm working on in access (but this will essentially be based off the control source a query). What I have is a table with data that lists delinquency in terms of staff attendance. For example, if person A has more than 3 absences in Quarter 1, then they are in stage 1. If Person A has more than 3 absences in Quarter 2, they are in Stage 2 and on and on. My table looks like this:

NAME QUARTER # OF ABSENCES
Person A Quarter 1 4
Person B Quarter 1 5
Person A Quarter 2 3

I want my report to look like the following:
NAME QUARTER STAGE
Person A Quarter 1 1
Person A Quarter 2 2
Person B Quarter 1 1

Can anyone help me out here?

Please and Thanks

 

[MSAccessRookie]

Hi,

I need some help with a report i'm working on in access (but this will essentially be based off the control source a query). What I have is a table with data that lists delinquency in terms of staff attendance. For example, if person A has more than 3 absences in Quarter 1, then they are in stage 1. If Person A has more than 3 absences in Quarter 2, they are in Stage 2 and on and on. My table looks like this:

NAME QUARTER # OF ABSENCES
Person A Quarter 1 4
Person B Quarter 1 5
Person A Quarter 2 3

I want my report to look like the following:
NAME QUARTER STAGE
Person A Quarter 1 1
Person A Quarter 2 2
Person B Quarter 1 1

Can anyone help me out here?

Please and Thanks

I would think that a standard Query that selects all three items and then Orders the rows by Person First, followed by the Quarter should give you what you are looking for.

 

[analyst1]

But the Stage field is a custom field, i'm not sure what to enter in as an IIF condition.

 

[ajetrumpet]

Hi,

I need some help with a report i'm working on in access (but this will essentially be based off the control source a query). What I have is a table with data that lists delinquency in terms of staff attendance. For example, if person A has more than 3 absences in Quarter 1, then they are in stage 1. If Person A has more than 3 absences in Quarter 2, they are in Stage 2 and on and on. My table looks like this:

NAME QUARTER # OF ABSENCES
Person A Quarter 1 4
Person B Quarter 1 5
Person A Quarter 2 3

I want my report to look like the following:
NAME QUARTER STAGE
Person A Quarter 1 1
Person A Quarter 2 2
Person B Quarter 1 1

Can anyone help me out here?

Please and Thanks

there is really no relation here between what your table says and what you want in the report. it's difficult to understand why you are doing this, but regardless, the way to do it is probably like this:

SELECT DISTINCT

table.name, table.quarter,

dlookup(

"# of abs", "table",

"[name] = '" & [name] & "' AND

[quarter] = '" & [quarter] & "'")

AS Stage

ORDER BY [name], [quarter];

that's the query. then simply make an autoreport and ther eyou have it.

 

[analyst1]

there is really no relation here between what your table says and what you want in the report. it's difficult to understand why you are doing this, but regardless, the way to do it is probably like this:

SELECT DISTINCT

table.name, table.quarter,

dlookup(

"# of abs", "table",

"[name] = '" & [name] & "' AND

[quarter] = '" & [quarter] & "'")

AS Stage

ORDER BY [name], [quarter];

that's the query. then simply make an autoreport and ther eyou have it.

Sorry, if staff has greater than 2 absences then they are in a stage (stage varies based on if this is their first offense).

I copied the following with ASP being the table name, and received an error stating: "unknown"

SELECT DISTINCT ASP.emp_name, ASP.quarter, DLookUp("[# of abs]","ASP","[emp_name] = '" & [emp_name] & "' AND [quarter] = '" & [quarter] & "'") AS Stage, ASP.[# of abs]
FROM ASP
ORDER BY ASP.emp_name, ASP.quarter;

 

[ajetrumpet]

rename the table and see if the error persists.

 

[analyst1]

Its not actually a table, but rather a query. I've renamed it to ASP2, but am still seeing the same problem.

Would this not be easier to accomplish in excel?

It can't be that hard to compare 2 rows (in same table within access).

 

[ajetrumpet]

Would this not be easier to accomplish in excel?

YES!

It can't be that hard to compare 2 rows (in same table within access).

actually, it is extremely difficult because access was not built for that purpose. this question comes up so many times you wouldn't be it

 

[analyst1]

What if I was to create a reports form in access, where I can click on a preview report button (report would show the query above), but only the report would be outputted in excel?

how would I go about creating a report in excel based on access information?

 

[boblarson]

What if I was to create a reports form in access, where I can click on a preview report button (report would show the query above), but only the report would be outputted in excel?

how would I go about creating a report in excel based on access information?

Actually, you can send a query, table or form recordset to Excel just by using my reusable code here:

Table Or Query

Form

 

[analyst1]

I've copied it into 2 separate modules, but am not sure as to how to trigger them?

 

[boblarson]

I've copied it into 2 separate modules, but am not sure as to how to trigger them?

You just need to copy the code into one module (standard module, not form or report module) making sure to NOT name the MODULE the same name as any of the functions. And then you call the code like this:

from a button's click event on the form you are exporting:

Private Sub YourButtonName_Click()
  Send2Excel Me
End Sub

(so you would go to the button's click event in the VBA window and type the Send2Excel Me part exactly as shown including the ME part)

To do a query or table:

Private Sub YourButtonName_Click()
  SendTQ2Excel "YourTableOrQueryNameHere"
End Sub