Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Report problems Access 2000

Discussion in 'Information Technology' started by Gale Coleman, Nov 10, 2005.

  1. Gale Coleman

    Gale Coleman
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    UK British Sikh Report-2016 Breaking News Mar 24, 2016
    1984 The Story I Did Not Report In ’84 History of Sikhism Jun 28, 2015
    Opinion Top Green Beret Officer Forced to Resign Over Affair With WaPo Reporter Breaking News Jun 26, 2014
    United Sikhs New Report Shows Increasing Hate Attacks on Sikhs Sikh Organisations May 21, 2014
    Legal ACLU reports: Judge orders Sikh man, "Remove that rag, or Go to Jail! Breaking News Sep 27, 2013

  3. tina

    tina
    Expand Collapse
    Guest

    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" <gcoleman@legalassist.org> wrote in message
    news:%23JWLM9X5FHA.1032@TK2MSFTNGP11.phx.gbl...
    > 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
    >
    >
     
  4. Gale Coleman - LSND

    Gale Coleman - LSND
    Expand Collapse
    Guest

    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
     
  5. tina

    tina
    Expand Collapse
    Guest

    > 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" <gcoleman@lnospam.org> wrote in message
    news:Ocf%23ZqZ5FHA.3296@TK2MSFTNGP09.phx.gbl...
    > 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
    >
    >
     
  6. Gale Coleman - LSND

    Gale Coleman - LSND
    Expand Collapse
    Guest

    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
     
  7. tina

    tina
    Expand Collapse
    Guest

    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" <gcoleman@lnospam.org> wrote in message
    news:O7vPi$Z5FHA.3136@TK2MSFTNGP09.phx.gbl...
    > 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
    >
    >
     
  8. Gale Coleman - LSND

    Gale Coleman - LSND
    Expand Collapse
    Guest

    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
     
  9. tina

    tina
    Expand Collapse
    Guest

    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" <gcoleman@lnospam.org> wrote in message
    news:OgJMgJa5FHA.3880@TK2MSFTNGP12.phx.gbl...
    > 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
    >
    >
     
  10. Gale Coleman - LSND

    Gale Coleman - LSND
    Expand Collapse
    Guest

    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
     
  11. Gale Coleman - LSND

    Gale Coleman - LSND
    Expand Collapse
    Guest

    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
     
  12. tina

    tina
    Expand Collapse
    Guest

    > 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" <gcoleman@lnospam.org> wrote in message
    news:uTArvxa5FHA.252@TK2MSFTNGP15.phx.gbl...
    > 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
    >
    >
    >
     
  13. Gale Coleman - LSND

    Gale Coleman - LSND
    Expand Collapse
    Guest

    jkklG88767CnoiuOm./kLEMAhhnN@LEjnnbbGALASSIST.nkjhhjORG

    OK, they are all in caps!!

    Gale
     
  14. Gale Coleman - LSND

    Gale Coleman - LSND
    Expand Collapse
    Guest

    I use Access 2000 :)

    Gale
     
  15. tina

    tina
    Expand Collapse
    Guest

    on it's way.


    "Gale Coleman - LSND" <gcoleman@lnospam.org> wrote in message
    news:%23aDutGf5FHA.1148@tk2msftngp13.phx.gbl...
    > jkklG88767CnoiuOm./kLEMAhhnN@LEjnnbbGALASSIST.nkjhhjORG
    >
    > OK, they are all in caps!!
    >
    > Gale
    >
    >
     

Share This Page