Blanks in File.

[lars7]

Hi,
After I have importing an excel file my report is reading a code field like there is a value in every record when there are thousands of blanks how can I sort this problem.

Thanks in advance.

 

[easyit]

Hi,

You could use a query to filter out the blanks; then use the query as source for the report.

EasyIT

 

[lars7]

Hi easyit,
Sorry I didn't explain properly, The file has 23 thousand staff records not all of them has a code number beside there name which is the way it should be. But the table is saying that although the code field for some are blank that there is a value in there. There is a date field in the table too and if I copy all the blank records from that and copy them to the blanks in the code field then I can have a proper count of the staff who have been coded. I think it is a "Is dirty" issue. I hope I have explained it better now.

 

[lars7]

Hi,
I got around the problem with a couple of extra queries,all the fields in the query are imported as "text fields".

Query 1
Counted all the paynumbers to give me count of staff and used "" to filter out leavers.

Query 2
Used <>"" to filter out all the blanks and Leavers with code numbers to give me a count of staff coded.

Query 3
Joined the first 2 queries together.


Not sure why this happens so not happy.:-(

 

[lespaul]

why don't you post the SQL of queries 1 & 2, maybe someone can help you figure out how to do it in a single query. A "visual" of the table and your expected results can also be extremely useful in assisting you.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lars7]

Hi Les,
There are 8641 records in "tblacuteservices" that do not have a code number if I click in any of those blank fields and filter by selection I will only get 1 record. For some reason the table thinks that every record has a value in the code field. Here are the SQL'S:


Query 1
SELECT tblacuteservices.Directorate, tblacuteservices.StaffGroup, tblacuteservices.PayNumber, tblacuteservices.Code, tblacuteservices.Left
FROM tblacuteservices
WHERE (((tblacuteservices.Code)<>"") AND ((tblacuteservices.Left)=""));

Directorate StaffGroup PayNumber Code Left

Medical Nursing (Tr.) G0807397 N1000
Medical Nursing (Untr.) G5931169 N1001
Medical Nursing (Untr.) G5808448 N1001


Query 2
SELECT tblacuteservices.Directorate AS Division, tblacuteservices.StaffGroup AS [Staff Category], Count(tblacuteservices.PayNumber) AS TotalStaff, tblacuteservices.Left
FROM tblacuteservices
GROUP BY tblacuteservices.Directorate, tblacuteservices.StaffGroup, tblacuteservices.Left
HAVING (((tblacuteservices.Left)=""));

Division Staff Category TotalStaff Left
ACCIDENT & EMERGENCY Nurse - Qualified 84
ACCIDENT & EMERGENCY Nurse - Unqualified 15
ANAESTHETICS Administrative & Clerical 17
ANAESTHETICS Ancillary Staff Council 7

 

[lespaul]

and what are your final results in the third query?

Leslie

 

[lars7]

Here is a sample of Query 3


SELECT qryacuteservicesreport2.Division, qryacuteservicesreport2.[Staff Category], qryacuteservicesreport2.TotalStaff, Count(qryacuteservicesreport1.Code) AS SCode
FROM qryacuteservicesreport2 LEFT JOIN qryacuteservicesreport1 ON (qryacuteservicesreport2.Division = qryacuteservicesreport1.Directorate) AND (qryacuteservicesreport2.[Staff Category] = qryacuteservicesreport1.StaffGroup)
GROUP BY qryacuteservicesreport2.Division, qryacuteservicesreport2.[Staff Category], qryacuteservicesreport2.TotalStaff;


Division Staff Category TotalStaff SCode
Child Health PTA (UNTR.) 5 1
Child Health PTB 10 0
CLINICAL PHYSICS Administrative 6 5
CLINICAL PHYSICS Professional 39 22
CLINICAL PHYSICS Scientific 15 0
Clinical Services Admin 291 210
Clinical Services Ancillary 15 8
Clinical Services Nursing (Tr.) 740 512
Clinical Services Nursing (Untr.) 216 168