Report for rows with value lower than 2

[dntel123]

Hi People

I basically need help with a report for showing every record in my student tables column 'Modules Passed' with a value lower than 2. The Table looks like so:

I've tried to no avail :/

Thanks!

 

[boblarson]

You need to set up a QUERY as the underlying recordsource of the report. See here on how to change from a table to a query. And then you just need, is to add the Modules Passed column again with the Display checkbox unchecked and the criteria under it set to

< 2

 

[dntel123]

I think I understand what you mean, except I make the report that shows their ID and there modules taken.

And I assume you create the underlying query to modify the results?

Except my query doesnt seem to have a record source in properties

This is exactly what I've done so far:

1) Create New report from Student Table
2) Add the Students ID field and the Modules Taken Field: This gives me a Report Looking like this:

Now I assume I am supposed to change the record source to a QUERY that tells it to only shows ones with <2 right?

Except I do not see the record source

Thanks a lot btw

 

[boblarson]

I think I understand what you mean, except I make the report that shows their ID and there modules taken.

And I assume you create the underlying query to modify the results?

Except my query doesnt seem to have a record source in properties

This is exactly what I've done so far:

1) Create New report from Student Table
2) Add the Students ID field and the Modules Taken Field: This gives me a Report Looking like this:

Now I assume I am supposed to change the record source to a QUERY that tells it to only shows ones with <2 right?

Except I do not see the record source

Thanks a lot btw

Okay, 1 - please upload screenshots too instead of just inlining them as we can't get imageshack, photobucket and other such sites at work. So, I don't see your screenshot. If you upload it too then at least those of us who can't see them directly can check them out.

Second, you need to open your report in DESIGN view and then there is a RECORD SOURCE for the REPORT in the report's properties.

 

[dntel123]

Oh sorry, I didn't realise

I Attached what report looks like.

And sorry, was being dumb, Record Source was on the all tab. And also correct when you click gives that warning shown in your sites tutorial.

Also Attached the picture qrydesign.png that show whats happens after I click to change the source and use the table.

Now in the query design do I just add the modules taken column again and put the criteria as <2 ?

Like so.. (refer to query complete on attachments)

Thanks a lot for your help!

 

[boblarson]

You don't show that you added * (which selects all of the fields, like having the table). You could alternatively just select all of the fields and drag and drop them into the query designer and then under Modules-Taken you can use the criteria of <2.

Oh, you should also not use special characters in your field or object names. using the dash ( - ) in your field is not suggested as it can have bad consequences sometimes if used thusly. If you want to separate words use the underscore ( _ ) but I, like many developers use CamelCase where the first letter is capitalized so you can have ModulesTaken and it is easily read and also causes no problems where you would need to have brackets for spaces, etc.

 

[dntel123]

Attached qryDesign.jpg

I double clicked the star your right forgot tht step.

(I do normally use CamelCase myself but i was in a group they did it differently to me lol)

The attached is what it looks like, 1st when i went to the report it showed nothing, then i closed and opened it showed to random IDs with modules taken 3 and 10 which are definitely over 2 lol.

Can you see from that what I have done wrong?

 

[dntel123]

No worries, done it! thanks!

 

[boblarson]

1. you need to uncheck the box for the Modules-Taken field you added next to the * part. That will keep it from displaying twice in the query.

2. If you can upload your database then perhaps we can sort this quicker.

 

[dntel123]

its ok I've fixed it, thanks a lot for your help, my next step will probably be having a form and when you click a button that report is displayed, or a subform with that report appears