Need help With Validation of a report

[charlotte]

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.

[Alongi]

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

[Alongi]

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