How do I get rid of blank records

C

C0rrinn3

I'm new to access and VB..

I have been asked to modify a report that is not populating with values all
the time, sometimes it populates blanks. In my query the fields are listed
correctly with relation to my fields, system part and workset, except
sometimes the workset for the system part is blank. I cannot use "is not
null" becasue some of the fields are supposed to be blank.

For example..

system part workset

Architect_Doc
Architecture
Architect_Doc
Architecture
Data_Doc
Data_Doc Dataware
Data_Doc Dataware

When populating my report the workset value for Data_Doc comes up blank. How
can I have it pick up the workset Dataware?

Thanks
Corrinne
 
C

C0rrinn3

Becasue I dont know how...


Here is the results of the query and the query..

SELECT DISTINCT tblRoleTypes.RoleType, DIMENSIONS_PCMS_USER_ROLES.ROLE,
DIMENSIONS_PCMS_USER_ROLES.ASSIGN_TYPE, DIMENSIONS_PCMS_USER_ROLES.PART_ID,
DIMENSIONS_PCMS_USER_ROLES.WORKSET_ID, "E" AS STATUS, tblRoleTypes.Area FROM
DIMENSIONS_PCMS_USER_ROLES INNER JOIN tblRoleTypes ON
DIMENSIONS_PCMS_USER_ROLES.ROLE = tblRoleTypes.Role WHERE
(((DIMENSIONS_PCMS_USER_ROLES.USER_NAME)=[Forms]![frm_DAR_Parms]![txtID]))
ORDER BY tblRoleTypes.RoleType, DIMENSIONS_PCMS_USER_ROLES.ROLE,
DIMENSIONS_PCMS_USER_ROLES.PART_ID;




RoleType ROLE ASSIGN_TYPE PART_ID WORKSET_ID STATUS Area
DOCUMENT EDITOR CREATOR R
APP_SERV_REGRESSION_APPS_PROJECTMANAGEMENT REGRESSION E CHNG
DOCUMENT
EDITOR CREATOR R APP_SERV_REGRESSION_APPS_RXCWIN_CODE REGRESSION E CHNG
DOCUMENT
EDITOR CREATOR R APP_SERV_REGRESSION_APPS_RXCWIN_DEPLOY REGRESSION E CHNG
DOCUMENT EDITOR CREATOR R APP_SERV_REGRESSION_DOCS REGRESSION E CHNG
DOCUMENT EDITOR CREATOR R AZ_MEDS_DOCS E CHNG
DOCUMENT EDITOR CREATOR R DATAWAREHOUSE_1_DOCS E CHNG
DOCUMENT EDITOR CREATOR R DATAWAREHOUSE_1_DOCS DATAWAREHOUSE E CHNG
DOCUMENT
EDITOR CREATOR C HR_RECRUITING_AND_STAFFING HR_RECRUITING-STAFFING E CHNG
DOCUMENT
EDITOR CREATOR R HR_RECRUITING_AND_STAFFING HR_RECRUITING-STAFFING E CHNG
DOCUMENT EDITOR CREATOR R IL_ADVANCED_CARE_DOCS IL_ADVANCED_CARE E CHNG
DOCUMENT EDITOR CREATOR R IL_ARCHITECTURE_DOCS E CHNG
DOCUMENT EDITOR CREATOR R IL_ARCHITECTURE_DOCS IL_ARCHITECTURE E CHNG
DOCUMENT EDITOR CREATOR R IL_ARCHITECTURE_DOCS IL_ARCHITECTURE_2 E CHNG
DOCUMENT EDITOR CREATOR R IL_ARCHITECTURE_DOCS IL_ARCHITECTURE_PROD E CHNG
DOCUMENT EDITOR CREATOR R IL_CLINICAL_SERVICES_SYSTEMS_DOCS E CHNG
DOCUMENT
EDITOR CREATOR R IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM E CHNG
DOCUMENT
EDITOR CREATOR R IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM_2 E CHNG
DOCUMENT
EDITOR CREATOR R IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM_PROD E CHNG
 
P

pietlinden

I'm new to access and VB..

I have been asked to modify a report that is not populating with values all
the time, sometimes it populates blanks. In my query the fields are listed
correctly with relation to my fields, system part and workset, except
sometimes the workset for the system part is blank. I cannot use "is not
null" becasue some of the fields are supposed to be blank.

For example..

system part                                                        workset

Architect_Doc                                                     
Architecture
Architect_Doc                                                     
Architecture
Data_Doc                                                    
Data_Doc                                                            Dataware
Data_Doc                                                            Dataware

When populating my report the workset value for Data_Doc comes up blank. How
can I have it pick up the workset Dataware?

Thanks
Corrinne
Click to expand...

What are the fields describing in the real world?
What are Architect_Doc, Architecture and Data_Doc? How are they
related? IF you normalize this, your solution is simple.
 
K

KARL DEWEY

I was going to say to create a translation table and run an update query but
that will not work as you have some as many-to-one ---
PART_ID WORKSET_ID
APP_SERV_REGRESSION_APPS_PROJECTMANAGEMENT REGRESSION
APP_SERV_REGRESSION_APPS_RXCWIN_CODE REGRESSION
APP_SERV_REGRESSION_APPS_RXCWIN_DEPLOY REGRESSION
APP_SERV_REGRESSION_DOCS REGRESSION

and others as one-to-many ---
IL_ARCHITECTURE_DOCS
IL_ARCHITECTURE_DOCS IL_ARCHITECTURE
IL_ARCHITECTURE_DOCS IL_ARCHITECTURE_2
IL_ARCHITECTURE_DOCS IL_ARCHITECTURE_PROD
IL_CLINICAL_SERVICES_SYSTEMS_DOCS
IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM
IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM_2
IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM_PROD

--
KARL DEWEY
Build a little - Test a little


C0rrinn3 said:
Becasue I dont know how...


Here is the results of the query and the query..

SELECT DISTINCT tblRoleTypes.RoleType, DIMENSIONS_PCMS_USER_ROLES.ROLE,
DIMENSIONS_PCMS_USER_ROLES.ASSIGN_TYPE, DIMENSIONS_PCMS_USER_ROLES.PART_ID,
DIMENSIONS_PCMS_USER_ROLES.WORKSET_ID, "E" AS STATUS, tblRoleTypes.Area FROM
DIMENSIONS_PCMS_USER_ROLES INNER JOIN tblRoleTypes ON
DIMENSIONS_PCMS_USER_ROLES.ROLE = tblRoleTypes.Role WHERE
(((DIMENSIONS_PCMS_USER_ROLES.USER_NAME)=[Forms]![frm_DAR_Parms]![txtID]))
ORDER BY tblRoleTypes.RoleType, DIMENSIONS_PCMS_USER_ROLES.ROLE,
DIMENSIONS_PCMS_USER_ROLES.PART_ID;




RoleType ROLE ASSIGN_TYPE PART_ID WORKSET_ID STATUS Area
DOCUMENT EDITOR CREATOR R
APP_SERV_REGRESSION_APPS_PROJECTMANAGEMENT REGRESSION E CHNG
DOCUMENT
EDITOR CREATOR R APP_SERV_REGRESSION_APPS_RXCWIN_CODE REGRESSION E CHNG
DOCUMENT
EDITOR CREATOR R APP_SERV_REGRESSION_APPS_RXCWIN_DEPLOY REGRESSION E CHNG
DOCUMENT EDITOR CREATOR R APP_SERV_REGRESSION_DOCS REGRESSION E CHNG
DOCUMENT EDITOR CREATOR R AZ_MEDS_DOCS E CHNG
DOCUMENT EDITOR CREATOR R DATAWAREHOUSE_1_DOCS E CHNG
DOCUMENT EDITOR CREATOR R DATAWAREHOUSE_1_DOCS DATAWAREHOUSE E CHNG
DOCUMENT
EDITOR CREATOR C HR_RECRUITING_AND_STAFFING HR_RECRUITING-STAFFING E CHNG
DOCUMENT
EDITOR CREATOR R HR_RECRUITING_AND_STAFFING HR_RECRUITING-STAFFING E CHNG
DOCUMENT EDITOR CREATOR R IL_ADVANCED_CARE_DOCS IL_ADVANCED_CARE E CHNG
DOCUMENT EDITOR CREATOR R IL_ARCHITECTURE_DOCS E CHNG
DOCUMENT EDITOR CREATOR R IL_ARCHITECTURE_DOCS IL_ARCHITECTURE E CHNG
DOCUMENT EDITOR CREATOR R IL_ARCHITECTURE_DOCS IL_ARCHITECTURE_2 E CHNG
DOCUMENT EDITOR CREATOR R IL_ARCHITECTURE_DOCS IL_ARCHITECTURE_PROD E CHNG
DOCUMENT EDITOR CREATOR R IL_CLINICAL_SERVICES_SYSTEMS_DOCS E CHNG
DOCUMENT
EDITOR CREATOR R IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM E CHNG
DOCUMENT
EDITOR CREATOR R IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM_2 E CHNG
DOCUMENT
EDITOR CREATOR R IL_CLINICAL_SERVICES_SYSTEMS_DOCS IL_CLINICAL_SERVICES_SYSTEM_PROD E CHNG

KARL DEWEY said:
Why not fix the records?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I get Rid of the Circumflex Accent (Caret) 2
How do I include the blank fields in a Query? 2
How do I delete all blank records within a table using a query? 1
How do I get totals with blank fields (Nulls)? 1
How do I get rid of blank pages between each page of reports 2
How can I get rid of blank pages when generating a report? 1
How do I get rid of 2nd page? It is blank. 1
How do I get rid of blank pages in a report? 3

Top