Results 1 to 3 of 3
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need help With Validation of a report

    First of all, I'd suggest you lose some of the white space in your message, since it does nothing to make it readable and just stretched the message waaaaaay out. It would also be more useful if you posted the SQL for the crosstab query rather than just describing it.

    Second, SSN isn't a number, it's text, and I do NOT recommend using it as a primary key, because it must be entered and can therefore be entered incorrectly. Primary keys are difficult to change, so the idea is to never have to change them.

    Finally, there is no way to insert a zero in a cross tab query when there are no records for that grouping. All you can do is define the column headings in your Column Heading field in the query so that the group will always appear, even if it has no values. Then in your report, you can use the Nz() function to substitute a zero for the null value.
    Charlotte

  2. #2
    Alongi
    Guest

    Need help With Validation of a report

    Almost forgot I am using Access 2000 for this.
    Ok I am a real beginner and in the US Army and told to make the following work so if you have input it be very much valued.
    Ok here is the problem with sample data
    Ok 1st I made a table with the following values in it an requirements for them
    <TBLPERSONEL>
    |Field| |Field Name| |What the Field data is|
    Field 1: |SSN| |Number Value| used as Primary Key
    Feild2: |MPC| |Text|will be only the following[C,E,O,W]
    Feild3: |Branch| |Text| Values of [AR,CR,NR,MR,FR] only

    Ok now for the Sample Data for the Above table
    SSN MPC BRANCH
    000-00-0000 W AR
    111-11-1111 C CR
    222-22-2222 O NR
    333-33-3333 E MR
    444-44-4444 E FR
    555-55-5555 W NR
    666-66-6666 C CR
    777-77-7777 O AR

    ok now for my other Table I have I will call TBLBranchConvert
    Will be
    Feild1:|branchSym||Text|This will have my list of [CR,AR,MR,NR,FR] and they will be my PK because in the next Field I will convert the symbol to a real Branch Such as AR = Army
    Feild2: |Real Branch| |Text| will hold what the other fields Symbol = to [Cilivian,Army,Navy,Marine,AirForce]
    So the Tbl will be this

    BRANCHSYM RealBranch
    CR Civilian
    AR Army
    MR Marine
    FR AirForce
    NR Navy
    OK NOW AFTER THAT IS DONE THE GOAL IS TO GET A REPORT THAT WILL ANSWER MY FOLLOWING QUESTIONS
    Need to get one that will give me How many personnel are in that Branch then break down to the MPC as in the following
    Break out of Total
    Branch TotalBranch C O W E
    AR 2 0 1 0 1
    NR 1 0 0 1 0
    Thur all 5 branchs i have defind get the hint of how it want do totals ?
    Ok so i did a CrossTab Query with the following Defined
    Ok i did the Following relationshop 1st
    Between my <TBLPERSONEL> and my <TBLCONVERT> I related BRANCH in TBLPERSONEL with BRANCHSYM in TBLCONVERT so i grab the REALBRANCH to show up instead of my CR but as Civilan , AR as Army (Just looks nicer then CR AR MR NR FR)
    ok here we go
    Field :| Branch:REALBRANCH |
    Table: | TBLCONVERT |
    ToTal: | Group By |
    CrossTab:| Row Heading |
    Sort:| (Blank)|
    Criteria:|(Blank) |
    NEXT SELECTION
    Feild: | MPC |
    Table: | TBLPERSONEL |
    ToTal: | Group By |
    CrossTab:| Column Heading |
    Sort:|(Blank) |
    Criteria:|(Blank)|
    NEXT SELECTION
    Feild: | SSN |
    Table: |TBLPERSONEL|
    ToTal: |Count|
    Crosstab:| Value |
    Sort:|(Blank) |
    Criteria: |(Blank)|

    NEXT SELECTION AND LAST ONE SO FAR

    Feild: |TOTAL SSN: SSN|
    Table: |TBLPERSONEL|
    ToTal: |Value|
    CrossTab: |Row Heading|
    Sort: |(blank)|
    Criteria: |(Blank)|


    OK there is the items in my Crosstab Query

    I made sure I had a person for Every branch and MPC comb so i can see what info i would get with a large # of personel and if it would do teh calations correctly. " WHICH IT DOES WITH ALL THE POSSIABLE COMBS THERE ARE FOR IT" it looks like the following

    Army TotalSSN# C# O# W# E#

    With every Branch I have in the table.

    So i do a report based off of this Crosstab Query and it works Fine with it long as i have the following items in it

    in MPC i need
    at least 1 [W] record
    at least 1 [E] record
    at least 1 [O] record
    at least 1 [C] record

    However now that i made my Report outline i need it to do the Following in the following importance of how to fix.

    If there is NO [E,W,O or C] listed in MPC to use it still as a heading but to place a Value of < 0 > there instead of saying that Report

    is now no valid [E] is no longer a valid field.

    *** When i do not have a [E] value under [MPC] in a record in the <TBLPERSONEL> Table ****

    So if you can Help Pls do

    Dom

    mailtohunt@jam.rr.com

    **** Ok an Update I found out about teh Nz Function but that only solves Par tof my Problem on the Report.

    It changes my Nulls to a Value of < 0 > like i wanbted but if i Do Not contain a [E W C or O] Record my Report still becomes invalid.

    To see what i mean Change the C's in the TBLPERSONEL to E's and you see that it can not do the report taht is my Roadblock.

    Dom

  3. #3
    Alongi
    Guest

    Re: Need help With Validation of a report

    Ok sorry about the White Space i am new to posting stuff here thought i made it more readable.

    ok to anwser your second point about the SSN

    I messed up on the post and said number it is a text (like I said I am new).

    I use the SSN as a Primary Key because it is a Unque item that every person I count has to begin with. (A SSN Feild will never be changed in the database)

    Ok for your Third point I am now editing the SQL statement to match my example data.
    Here is the SQL statment for my Crosstab Query
    TRANSFORM Count([TBLPERSONEL].SSN) AS CountOfSSN
    SELECT [TBLCONVERT].[REALBRANCH] AS BRANCH, Count([TBLPERSONEL].SSN) AS [TOTAL SSN]
    FROM [TBLPERSONEL] INNER JOIN [TBLCONVERT] ON [TBLPERSONEL].BRANCH = [TBLCONVERT].BRANCHSYM
    GROUP BY [TBLCONVERT].[REALBRANCH]
    PIVOT [TBLPERSONEL].MPC;


    And now do you have any suggestions on how I would extract the data form my TBLPERSONEL to come up with the count I am looking for or am i tring to come up with something that is not possable.

    I need a report that is in the following format
    Number of
    Branch BranchToTal C O W E

    ARMY 16 0 5 5 6
    AirForce 9 1 2 2 2
    Navy 4 0 3 0 1
    Marines 5 1 1 1 2
    Civilians 8 8 0 0 0

    The above should be doing the Following I hope.
    1. Give me a List of the Branch i have in the TBLPERSONEL
    2. Count the SSN Field for each Branch
    3. Then Give me a Count of each Branch's MPC as in Count C,E,W,O

    Ok pls let me know if this clears it up a bit more then before sorry about the confusing post to start with

    Dom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •