Newbie Cannot get Query to work

[KDM2005]

Thanks for all the responses to my last post last week. I am going to try to re-explain my problem in order to maybe get clearer results. I've tried several suggestions and I'm either too new at this or I'm making it harder than it needs to be.

I have a 2 tables and I am trying to build a query. I am pulling the following info from each table:
Table 1: Table 2:
ID # (primary key) Name
Date of Class
Name of Class
Total Credits
Category 1 Credits
Category 2 Credits
Category 3 Credits

I have built a parameter into the query that asks for the name of the person. When I type it in, I get all information - works great. HOWEVER - I only want Category 1, 2 and 3 Credits to show if >0

As it is now I have a page filled with 0's and it's making it difficult to read with as many classes as I track. The credit hours the participants receive is getting "lost" in the zeros.

1- Is there a way to do this?
2- Am I setting it up incorrectly? Is there a better way?

THANK YOU IN ADVANCE.

 

[GingerR]

I don't understand the relationship between the two tables. However, it does appear to me that you may not have a normalized database. Do you really have fields names "Category1", "Category2" and "Category3"?

Can you please re-state your table structure, and provide some sample data?

Is the TOTAL CREDITS field a calculation based on adding Cat1+Cat2+Cat3 credits?

IF all you want is to display rows where TotalCredits > 0, the just put ">0" in the query criteria.

Also please post here your current query's SQL code.

Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KDM2005]

No the actual field names are different.
This is what I want my query to pull (IN CAPS):

NAME
DATE OF CLASS
CLASS TITLE
TOTAL CREDITS AWARDED total is 25.00
(this is NOT based on adding the following)
MRI CREDITS total is 3.00
CT CREDITS total is 1.00
PEDIATRIC CREDITS total is 10.00
NEURO CREDITS total is 0.00
ULTRASOUND CREDITS total is 20.00


Would I insert ">0" into the criteria for each category (MRI, CT, etc...) So in the above example, everything would show EXCEPT for "NEURO CREDITS" because there are zero...correct?

 

[Golom]

I gather that you are attempting to produce a variable number of fields in your output (i.e. show only those credits with non-zero values). Unfortunately, SQL requires that each record have exactly the same fields as every other record as specified in the SELECT clause.

In your example, if you specify [NEURO CREDITS] > 0 then the whole record will be ignored ... not just that field.

 

[GingerR]

Hi. Again, can you please provide your table structure and some sample data from each table, along with your query and the results you want. This will help us help you. Otherwise I think we will all be going around in circles. Thanks.

Sample Table Structure:

Table Name: Classes
Field: ClassID (Autonumber)
Field: ClassName (text)
Field: ClassCredit (integer)

Table Name: EmployeeClasses
Field: EmployeeID (Integer from table Employees)
Field: ClassID (integer from table Classes)
Field: ClassDate (date/time)


Thanks.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

This is what I want my query to pull (IN CAPS):
Seems you want a REPORT with some conditional formatting in the detail section.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KDM2005]

Table Name: DoctorInformation
Field: MDNumber (primary key, text)
Field: LastName (text)
Field: FirstName (text)

Table Name: CME
Field: LineNumber (primary key, autonumber)
Field: MDNumber (text)
Field: ConferenceDate (date/time)
Field: ConferenceTitle (memo)
Field: TotalCreditsAwarded
Field: Miscellaneous
Field: Body
Field: Breast MRI
Field: BreastUltrasound
Field: Chest
Field: CT
Field: ER
Field: Neuroradiology
Field: MRI

Fields Total through MRI... I used "currency fixed" becuase it was the only I could get it to work with 2 decimal places (0.00, 3.75, 3.50) -- every other way I tried, Access rounded the number up or I got an error. ??


I want my query to display all credits for a particular MD number. This I have working just fine. I just don't want it to SHOW 0.00, 0.00, 0.00, 0.00 over and over again when the physician did not obtain credits in certain categories. Maybe PHV is right and I'm trying to attempt with a query in what should be a REPORT. ?? If this is the case, I'm still stuck - but maybe it would help in narrowing my problem.

 

[lespaul]

then the issue is that your table is not normalized. You should have:


Table Name: DoctorInformation
Field: MDNumber (primary key, text)
Field: LastName (text)
Field: FirstName (text)


Table Name: CME Courses
Field: CourseNumber
Field: CourseName

{These should be RECORDS in CME Courses:
Field: Miscellaneous
Field: Body
Field: Breast MRI
Field: BreastUltrasound
Field: Chest
Field: CT
Field: ER
Field: Neuroradiology
Field: MRI}

Table Name: DoctorCME
Field: MDNumber (text)
Field: CourseNumber
Field: ConferenceDate (date/time)
Field: ConferenceTitle (memo)
Field: TotalCreditsAwarded

Depending on how far along you are in this project, you may want to change your design to a more normalized structure (see Fundamentals of Relational Database Design for more on normalization). If you can't change the structure then you can consider a query to normalize the data and use that query as your source:

SELECT MDNumber, "Breast MRI" As Course, [Breast MRI] As Credits FROM CME WHERE [Breast MRI] <> 0
UNION
SELECT MDNumber, "BreastUltrasound", [BreastUltrasound] FROM CME WHERE [BreastUltrasound] <> 0
UNION
etc for all your fields.

now you will have a recordset that only has the courses where the dr has credits.

MDNumber Course Credits
1 1 3.75
1 8 2.50
2 2 4.00
2 6 5.00

now you can use this query as the source of your report.

HTH




Leslie

 

[KDM2005]

I'm not too far into this, so I'm open to any structure changes.

I will do some more research with the link you provided me. I am still pretty lost.

 

[GingerR]

to not show 0.00: you can put in formulas

=iif([Blah] = 0,null,[Blah])

where Blah is the field name.

Otherwise, I agree with lespaul that you should change your table structure, or you will be crazy for the rest of this db's life.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244