Report problems Access 2000

[Gale Coleman]

Hello all,

I use Windows 2000 pro with Access 2000.

I have an table with the following fields

Casenum, Partial, full, paperapplication, and Onlineapplication.

For each client we have I have in input sheet that records if we did a paper
application or an online application for the person and also if the
application was partially done or fully done.

In the input form it has these two lines that when filled out properly, goes
into a table called zMedicarePartD

full partial
paper online

Here is where the trouble begins. I need a report that will give me the
following info:

Number of paper apps Number of Online apps total of
1 and 2 Reimbur Rate Total Reimb Request
#of Partial # of full # of Partial # full

I can't figure out how to get the count of partial and full for the paper
application and the count of partial and full for the online apps.

I have tried it a number of way, but I am not getting close. Can anyone
guide me?

Thanks

Gale

 

[tina]

the problem starts (as so many do) with the non-normalized table design -
which is identified here by the data stored in field names (partial, full,
paper, online). your table should have three fields, not five:

CaseNum
Completion
ApplicationType

so each CaseNum record has a single Completion value ("partial" or "full)
and a single application type value ("paper" or "online"). you can use a
Totals query to count the number of cases broken out by completion value
within application type, or by application type within completion value.
then try a Crosstab query to get the reporting layout you want, of the data
returned by the Totals query.

hth

 

[Gale Coleman - LSND]

OOOOOOO yes, that is what I need!

OK, now I did a total query and cross query to get the total number of
partially served and fully served underneath each application type. The
number served works out ok, but the type of application is shown by a number
(1 or 2) instead of the words Online or Paper. Is there a way to turn the
numbers into words?

Gale

 

[tina]

the type of application is shown by a number

(1 or 2) instead of the words Online or Paper.

i'm guessing that's because you have a table of ApplicationTypes, as

tblAppTypes
TypeID (primary key)
TypeName (such as "paper" or "online")

and the TypeID is a foreign key field in your "main" table, which i'll call
tblCases. and the two tables are linked on the common TypeID field in a
one-to-many relationship, as
tblAppTypes 1:n tblCases
where one application type can be used in many cases, but each case has only
one application type.

all correct so far? if so, then go back to your first query - the Totals
query. add tblAppTypes to the design grid, and create the link *from*
tblCases *to* tblAppTypes. use a LEFT JOIN which will "return all records
from tblCases, but only records from tblAppTypes that have a match in the
linking field". add the TypeName, field from tblAppTypes, into the query
design grid and Group on it. if necessary, you can remove the "app type
foreign key field of tblCases" from the design grid.

your table and field names are no doubt different, but hopefully you can
apply the above scenario to your setup.

btw, if you didn't create a "supporting table" such as the tblAppTypes i
described, and instead created a Lookup field in your tblCases....then get
rid of that Lookup field! instead, set up the supporting table and link it
to tblCases as i described above. for more info on why Lookup fields in
tables are a *bad* idea, see
http://www.mvps.org/access/lookupfields.htm

hth

 

[Gale Coleman - LSND]

Yikes!

That was scary.

I just did it the easy way and used combo boxes and typed in the value from
the row source. Now when I input into the new form it stores the value
(paper or online) in a table called zMedicarePlanD. I run a cross query
based on a total query and get a very nice report. Easy to read, just about
what I wanted. The only thing I can't make it do is the following:

Have a row with two columns in it one named Paper and one named Online with
another row underneath that with two additional break downs of partial and
full .

I can get the two rows, one online and one paper with the columns as partial
and full. I guess I can work with that. Any suggestions for that?

Thanks millions,

Gale

 

[tina]

hmm, well, there may be a way to get what you're after. i've little
experience in setting up Crosstab queries, so i'm not sure. i've been doing
all this in my head so far; i'm going to make a rough model of your setup
(with supporting tables - *not* Lookup fields! <g>) and see what i can come
up with by fiddling with the Crosstab query a bit "for real". so give me an
hour (or two, or three), Gale, and check back for a further post from me.

or somebody else out there who's slicker with Crosstabs than i (that could
be a large group) may post first and teach us both something!

hth

 

[Gale Coleman - LSND]

Goodness, you don't have to work so hard for me! I will keep fiddling
also, but tomorrow, not anymore tonight. Thank you much for your extremely
hard work!

Gale

 

[tina]

no problem - when i'm working on something i don't know well, i'm teaching
myself (hopefully) while helping somebody else (again, hopefully). <g>

at any rate, it didn't take me 2-3 hours to figure out that i can't see a
way to do exactly what you described as far as the layout goes - at least,
not dynamically. i think i could do it in a "hard-coded" sort of way,
perhaps with a series of queries and/or subreports. but i didn't pursue that
avenue, because i hesitate to offer you an inflexible solution; you really
need something that will *automatically* accomodate additional completion
"categories" and/or additional application types - because in a business
environment, you never know when that's going to come up. (after all, less
than 10 years ago, who would have thought they'd need such a thing as an
"online" application type?) the crosstab result you've already achieved
"I can get the two rows, one online and one paper with the columns as
partial
and full."
will make that automatic accommodation. and don't forget that you can just
as easily set up the opposite result: two rows, one partial and one full,
with the columns as online and paper.

btw, in the setup you're using, where the completion categories and
application types are text data in your table zMedicarePlanD, i don't think
you actually need the Totals query. try basing your Crosstab query directly
on your table; it should give you the same result.

and further btw, the small testing model i set up is properly normalized and
structured: three tables, a form with correctly set up combo boxes for easy
data entry, and two queries (a join query for the tables, and a crosstab
query based on the join query). if you'd like to see the model, to learn
what you can from it, just let me know. i always watch "my" threads for
several days, at least.

hth

 

[Gale Coleman - LSND]

this is kind of what I was trying to do and couldn't think my way through.
I will try it again tomorrow.

"and don't forget that you can just as easily set up the opposite result:
two rows, one partial and one full, with the columns as online and paper."

Gale

 

[Gale Coleman - LSND]

This would be great!

and further btw, the small testing model i set up is properly normalized and
structured: three tables, a form with correctly set up combo boxes for easy
data entry, and two queries (a join query for the tables, and a crosstab
query based on the join query). if you'd like to see the model, to learn
what you can from it, just let me know. i always watch "my" threads for
several days, at least.

Thanks,

Gale

 

[tina]

This would be great!

(Gale, i thought you were done for the night! <g>)
post your email address, disguised to foil the spammers, and your version of
Access (i can send you A97 through A2003). i'll email the db to you.

an example of a disguised email address would be
gSPcoAleMmaBnaEtyaGhoOodoNtcomE
with decoding instructions to "remove all the capital letters and change the
at and dot to symbols".

 

[Gale Coleman - LSND]

jkklG88767CnoiuOm./[email protected]

OK, they are all in caps!!

Gale

 

[Gale Coleman - LSND]

I use Access 2000

Gale

 

[tina]

on it's way.