Guidance on how to do this please

[Hell-fire]

Hello,

Thank you to anyone that will look at this post. This is the second time to
post this, my other post just got one response, sorry for reposting. I am
trying to make a
report that gets the information from one field. The field has a drop down
list with selection for the following: Assmt 1,2,3, Resolute 1,2,3, Pending
1,2,3,
closed 1,2,3. There is total of 12 in the list.

For the report I need to sort and tally the above information like this:

Cat 1 Cat 2 Cat 3 Total

Assessment 0 2 0 2
(IDxxxx, IDxxxx)

Resolution 0 2 2 4

(IDxxxx, IDxxxx) (IDxxxx,IDxxxx)

Etc.

When one of the Categories has any totals, the ID's are also listed.

Is this possible to do with the Query and Report function of Access?
For the other report I can have it sort and show each cat and ID in the
order I want, but would like to know if I can do the tally I showed above.
Any and all suggestions are welcomed. I am trying my best to learn how to
do this, but unfortunately, I need everyones expert advice. Thank you.

 

[Duane Hookom]

It isn't clear what the actual stored values look like. Please provide some
sample records and how you would expect them to appear in a report. Give us
the significant field names as well as the table name.

 

[krissco]

Hello,

Thank you to anyone that will look at this post. This is the second time to
post this, my other post just got one response, sorry for reposting. I am
trying to make a
report that gets the information from one field. The field has a drop down
list with selection for the following: Assmt 1,2,3, Resolute 1,2,3, Pending
1,2,3,
closed 1,2,3. There is total of 12 in the list.

For the report I need to sort and tally the above information like this:

Cat 1 Cat 2 Cat 3 Total

Assessment 0 2 0 2
(IDxxxx, IDxxxx)

Resolution 0 2 2 4

(IDxxxx, IDxxxx) (IDxxxx,IDxxxx)

Etc.

When one of the Categories has any totals, the ID's are also listed.

Is this possible to do with the Query and Report function of Access?
For the other report I can have it sort and show each cat and ID in the
order I want, but would like to know if I can do the tally I showed above.
Any and all suggestions are welcomed. I am trying my best to learn how to
do this, but unfortunately, I need everyones expert advice. Thank you.

If I understand correctly, you have a field which can contain one of
twelve values. Each individual choice (one of the twelve) is actually
two choices - a prefix (Resolute, Pending, etc.) and a number (1, 2,
or 3).

It is unclear how the ID numbers are stored and what relationship they
have to the data in your field.

First off, you data is not normalized - don't store more than one
value per field. Your data would be more manageable if you separate
the field.

That said, here is a stab at what your query would need to look like:

You will need to separate the two fields in your query.

select iif(left(fieldName, 5) = "Assmt", "Assessment",
iif(left(fieldName, 8) = "Resolute", "Resolution", "Pending"))) as
myGroup, sum(iif(right(fieldName, 1) = "1", 1, 0) as cat1,
sum(iif(right(fieldName, 1) = "2", 1, 0) as cat2,
sum(iif(right(fieldName, 1) = "3", 1, 0) as cat3
from tableName
group by iif(left(fieldName, 5) = "Assmt", "Assessment",
iif(left(fieldName, 8) = "Resolute", "Resolution", "Pending")))

Again, no idea where the ID numbers come from. You could include them
in your report using a function.

public function getIDs(cat as string) as string

'Open your data where the ids are stored using a query like
'select id from tablename where fieldname = cat
'iterate the recordset to build the (id1, id2, . . .) list you desire.
'return that string
end function

call the function from a textbox in the report.

-Kris

 

[Hell-fire]

Hi Krissco,

Thank you for looking at my post. Sorry that it isn't as clear as it should
be.

For the one Field, it is a combo box with the 12 items in the list and user
then chooses the appropriate answer. The ID is a separate field linked to
each record.

So what I need is to get a tally of each item and then show the ID with it.

 

[Hell-fire]

Hi Duane,

For the Data used its like this:
Identifier Problem Status
I0001 Assmt-1
I0002 Pending-2
I0003 Assmt-1
I0004 Closed-3

I will use the data from the Identifier and Problem Status field. So for
Assmt-1, I have two listed, then need to show the tally of 2 and then the
Identifier info with it.

The two fields comes from the same table.

Thank you

 

[Hell-fire]

Hi Krissco,

As to my previous reply to your help, I included a field that might make
things easier. I really can't change many things on how this DB is
structured since I'm modifying someone else's design.

Instead of using Assmt1-3, etc. another field just has numbers 1 to 12 that
also corresponds with the "Problem Status" field listing Assmt1-3,
Resolute1-3, pending1-3, and closed-1-3.

Would it be easier to use both the "Report Order" and "Identifier" fields
instead? The "Identifier" field is also the primary key to 3 tables, with it
starting from I0001 on up. Thank you.

 

[krissco]

As to my previous reply to your help, I included a field that might make

things easier. I really can't change many things on how this DB is
structured since I'm modifying someone else's design.

Aah. I understand your frustration.

Instead of using Assmt1-3, etc. another field just has numbers 1 to 12 that
also corresponds with the "Problem Status" field listing Assmt1-3,
Resolute1-3, pending1-3, and closed-1-3.

In that case, forget all the iif() statements to get your grouping.
You can instead use:

select cint((StatusNumber -1) / 3) + 1 as myGrouping, ((StatusNumber -
1) mod 3) + 1 as myCategory

This will break your twelve integers into four groups with three
categories (I assume that each "Problem Status" is enumerated
logically).


The tricky part is how you want to display the data - multiple records
on a single row. You can use the sum(iif()) technique in my earlier
post to separate the count of categories into different fields a
single record. I don't know of a non-VBA way to get all your ID
numbers to display like you want them to (as a list).

I suggest dropping the ID numbers from your query, and including them
on your report using a function like the one I outlined in my original
post.

Would it be easier to use both the "Report Order" and "Identifier" fields
instead? The "Identifier" field is also the primary key to 3 tables, with it
starting from I0001 on up. Thank you.

I'm not at all sure what you mean.


-Kris

 

[Hell-fire]

Hi Krissco,

Thanks to your help, I was able to make my tally table, but I can't quite
figure out your meaning on how I should display the ID's.

For each of my 12 categories, I used this formula to add them up:
=Sum(IIf([Report Order]=1,1,0))

Now I'm trying to figure out how to match my ID's to each [Report Order]
number. For example, if Report Order = 1, then ID's I0070 and I0069 should
show up. How would I do the formula to do this?

Thank you for any assistance you can provide

 

[Hell-fire]

Hi Krissco,

Thanks to your help, I was able to make my tally table, but I can't quite
figure out your meaning on how I should display the ID's.

For each of my 12 categories, I used this formula to add them up:
=Sum(IIf([Report Order]=1,1,0))

Now I'm trying to figure out how to match my ID's to each [Report Order]
number. For example, if Report Order = 1, then ID's I0070 and I0069 should
show up. How

 

[krissco]

Hi Krissco,

Thanks to your help, I was able to make my tally table, but I can't quite
figure out your meaning on how I should display the ID's.

For each of my 12 categories, I used this formula to add them up:
=Sum(IIf([Report Order]=1,1,0))

Now I'm trying to figure out how to match my ID's to each [Report Order]
number. For example, if Report Order = 1, then ID's I0070 and I0069 should
show up. How would I do the formula to do this?

Thank you for any assistance you can provide

The simplest way of doing this is to use a subreport. The subreport
will contain the IDs for the current [Report Order].

The drawback to this method is that your results will display in a
column, not across the page.

In order to get your results to display across the page, you should
write a function and bind that function to the textbox in your report
that will display the IDs.

Place the following code in a standard module:


Public Function getIds(ReportOrder as Integer) as String

'Declarations
dim rst as New ADODB.Recordset
dim strSql as String, strRet as String

'Set up the query - you will need to modify this to fit your data
strSql = "Select identifier from tblName where ReportOrder = " &
ReportOrder

'Open the query
rst.open strSql, currentproject.connection, adOpenForwardOnly,
adLockReadOnly

'Iterrate through the recordset - building a string of all the IDs
while not rst.eof
strRet = strRet & ", " & rst(0)
rst.movenext
wend

'Remove the preceding ", " from the string
if strRet <> "" then strRet = right(strRet, len(strRet) - 2)

'Cleanup
if not rst is nothing then
if rst.state = adstateopen then rst.close
set rst = nothing
end if

'Return the string of IDs
getIds = strRet
end function


Now create a text box on your report where you want the IDs to print.
Set the control source to:
=getIds([ReportOrder])
.. . .where ReportOrder is the name of the control on your report.


The end result is to combine IDs on (conceivably) multiple records
into a single string and display this string in a single text box. The
function will be called for every ReportOrder on your report. When
using this approach, don't try and include the IDs in the query for
your report - only include the categories and counts.

-Kris

 

[Hell-fire]

Hi Krissco,

Thank you very much for your help and support.

Hi Krissco,

Thanks to your help, I was able to make my tally table, but I can't quite
figure out your meaning on how I should display the ID's.

For each of my 12 categories, I used this formula to add them up:
=Sum(IIf([Report Order]=1,1,0))

Now I'm trying to figure out how to match my ID's to each [Report Order]
number. For example, if Report Order = 1, then ID's I0070 and I0069 should
show up. How would I do the formula to do this?

Thank you for any assistance you can provide

The simplest way of doing this is to use a subreport. The subreport
will contain the IDs for the current [Report Order].

The drawback to this method is that your results will display in a
column, not across the page.

In order to get your results to display across the page, you should
write a function and bind that function to the textbox in your report
that will display the IDs.

Place the following code in a standard module:


Public Function getIds(ReportOrder as Integer) as String

'Declarations
dim rst as New ADODB.Recordset
dim strSql as String, strRet as String

'Set up the query - you will need to modify this to fit your data
strSql = "Select identifier from tblName where ReportOrder = " &
ReportOrder

'Open the query
rst.open strSql, currentproject.connection, adOpenForwardOnly,
adLockReadOnly

'Iterrate through the recordset - building a string of all the IDs
while not rst.eof
strRet = strRet & ", " & rst(0)
rst.movenext
wend

'Remove the preceding ", " from the string
if strRet <> "" then strRet = right(strRet, len(strRet) - 2)

'Cleanup
if not rst is nothing then
if rst.state = adstateopen then rst.close
set rst = nothing
end if

'Return the string of IDs
getIds = strRet
end function


Now create a text box on your report where you want the IDs to print.
Set the control source to:
=getIds([ReportOrder])
.. . .where ReportOrder is the name of the control on your report.


The end result is to combine IDs on (conceivably) multiple records
into a single string and display this string in a single text box. The
function will be called for every ReportOrder on your report. When
using this approach, don't try and include the IDs in the query for
your report - only include the categories and counts.

-Kris