Filtering in Access 2003 or 2010

[jtalbot0001]

Hi All, I am new at all of this, only been using access for over a week now. I have a database made and have made a report which works nicely with my table. Now I need to start filtering or running a query, which ever is the correct way of what I need it to do. Basically I have a table which has 10 columns or so, three of which contains prices. Lets assume these three columns that have prices are called BRAND NEW, SECOND HAND and VERY OLD (I just made that up), anyhow, and so an example row/line contains the following, the BRAND NEW column has a price of 10.99, the SECOND HAND price is 7.99 and the VERY OLD price is 3.99 Now I want to filter so that anything that is 4.99 or over, as an example, is removed, but it MUST NOT remove the line, only the field 3.99, so when I generate a report, it will show the row and show the fields/columns BRAND NEW 10.99, SECOND HAND 7.99 and VERY OLD as being blank. And if we go back and change the value that we filtered from 4.99 to say 8.00, then the new result would be that the row still shows but only shows a value under the BRAND NEW column, and has no values under the next two columns since those two fall under 8.00. I hope that makes sense? And this filtering then must also be clever enough to remove rows that have no values at all in the table IE, if a particular row shows nothing under BRAND NEW, nothing under SECOND HAND and nothing under VERY OLD, then there is no need for my report to show that line of info as it is pointless for my report. A am a novice at all this, so please try and explain if you can in simple terms. I will be exceptionally grateful to anyone who can help me. To me it is a quite a complex query? If you need me to upload a tiny version of my table so that you can play around with it I am happy for you to do that. Just let me know. I assume I also would need to link my report to this new query rather than the table as that will have the data I need. Thanks in advance to anyone who replies. Sorry if this is under the wrong heading in this forum! Thanks, Jon.

[June7]

To exclude records with no price data in any field, use filter criteria in the query report is based on, under each of the price fields: Not Is Null

As far as not displaying the price in selected records, use an expression in textbox or on a report: IIf([field name]<[enter value], Null, [field name])

How the [enter value] parameter is populated is the real trick. This could be by reference to a textbox on a form.

[jtalbot0001]

Thanks very much for the reply. Let me keep things a little simple for now and concentrate on just removing lines that have no values in all three columns for the time being. I tried the Not is Null idea, adding it under all three columns (is that right?), and I can see it working, but then if one column is empty, and the other two next to it have values, it is completely removing the line which is wrong, it needs to be clever and check further along in the other colums for values before removing the line. Basicaly using the Not Is Null idea is returning only lines that have values in all three columns, which is wrong, or rather not what I want it to do, it must still show lines that have at least one column which has a value in it. Thanks.

[June7]

Then try putting each parameter on a different criteria row (will look like stair steps) to invoke the OR operator.

[jtalbot0001]

Brilliant, from what I can see that has solved that issue, thanks very much. Initially I created my report which is linked to the table. So this query that I have now made, do I link my report to the query in order to get the new info? How do I do that? I had a go at changing the control source in my report from my table to the query but when I then run the report it then comes up with the word error where ever ever I changed the control source, so not so sure that is what you do?

[June7]

Change the report RecordSource property.

[jtalbot0001]

Thanks! I have another issue now which I never noticed before. On my report it is sorting my info on the sub-title line from A-Z and if there are two sub-titles with a similar name but with numbers at the end, then it will sort the two similar ones by numbers 1 upwards. I can't seem to stop it from sorting, it seems you only have a choice of ascending or descending which I do not want, is there any way I can disable it? My table is actually all in the right order so if I can disable sorting as a whole in my report or query, where ever it needs disabling, then that will be ok as it should then hopefully use the order my table is in.

[June7]

Interesting, not something I've had to deal with so just tested. Even with no primary key designated and no indexing, the report insists on sorting records even though no sort order is defined. I think you might need a unique ID and use that field as sort criteria. Create a field and set as autonumber. This should generate a unique ID in the sequence records were entered.

[ssanfu]

My table is actually all in the right order

Just so you are aware,
Tables do not have an "order". Records just "are" - a table is a big, unsequenced bucket. The records might be in the order you want today, maybe tomorrow, maybe the next day. But they might appear in a different order at any time.

Indexes and queries provide order to the records in the bucket.


PS
Welcome to the forum

[jtalbot0001]

Thanks two both of you for the replies. June7, your idea is correct but then I have other issues now. I added field and called it Unique ID in my table and set it to auto number and that went fine. I made a new query to include this field, that is fine, and if I make a new report with the same layout as before on the query and make the grouping levels with Unique ID first, then that sorts out the issue of some items being in a different order. However, since I now have Unique ID as the top group level, with next field called country as the second group level and sub-title as my third, I now have the country repeated as well as my sub-title repeated, which I do not want, it should only repeat the country once per page or column etc and then group the sub-title together and not repeat it throughout. I have pictures of what I mean to explain. I just don't understand why the grouping levels have to be sorted, why cant it be switched off? Picture A produces picture A1 (how I want it to look but sorting it incorrectly) and Picture B produces picture B1 (how I don't want it to look but then it sorts it correctly). Any ideas? The pictures seem small here, not sure if you can see them properly?

[June7]

Unique ID should not be a grouping level, it would be a sorting level. The report Grouping & Sorting interface is different in Access 2007 but I think the concepts are the same.

If you want to provide db for analysis, follow instructions at bottom of my post.

[jtalbot0001]

I see. Well I have given a small part of my database with report etc so you can play around with it. You will know when you have got it right when you look at Australia! If you run my report before you do anything you will notice Postage Due items from 1902 come first, but if you close that and just look at the table you will notice they are towards the end of the section of Australia, which is how I want it to be. Thanks for looking at the file.

[June7]

Now I see and understand better. Report groups automatically have a sort order. The unique record ID alone will not help. Need a unique group value that defines the order. Try:

1. create a field in the query with expression:
GroupID: DFirst("[Unique ID]","allbas","[sub-title]='" & Replace([sub-title],"'","''") & "'")

The Replace function is because of apostrophes used in some sub-title values. Apostrophe and quote marks are special characters to SQL statements and domain aggregate function WHERE argument follows SQL syntax.

2. in the report, instead of grouping on the sub-title field, group on GroupID, just change the field reference for the group parameter, no need to change textboxes


BTW, advise no spaces or special characters/punctuation (underscore is exception) in names. I would remove the hyphen from sub-title as this is a math operator (special character), same for the / and & in other names. Otherwise must remember to enclose in [].

[jtalbot0001]

Thanks for the reply. I have tried to do exactly what you have said, and I can't quite see where I have gone wrong? Your idea is working fine for me almost (I'm so close now), and can see that you told it to asign numbers to each different subtitle, am I right in understading that it should then covert those numbers back into the corresponding sub-title? Just that mine is leaving the numbers rather than puting the sub-title in? I probably did something wrong somewhere? ..... I Just had a brainstorm and managed to fix it while typing this, but I don't think it was the way you intended! In the GroupID line in my report I added another box which links to the sub-title and that is now working, and I have made the GroupID box size zero and hidden. Since your way is sorting my report on the GroupID, my extra sub-title box is corresponding to the relevant GroupID (as it is still there but hidden) and that is the only reason why I think it is working for me now. So having done all that it looks all right to me now, but I would like for you to see what I did wrong with your way as mine is a bit more complicated, especially if anybody has similar issues and wants to read this thread, and even for me to try again in the future. Look at my "Query" and that was me trying to do it your way, and if you try "Query2" that was my modded way on top of what you said to make it look right. Where did I go wrong I wonder?

[June7]

I just noticed you have field names with quote marks. This is not a good idea - see my note in previous post about naming conventions.

The DLookup expression in the query is correct.

There was no need to change any of the textboxes on the report. Only had to change the sub-title header to a GroupID header.

I don't see any difference between reports Query1 and Query2. These show the design I used and the result I achieved. I do not see a textbox for the GroupID. If used, you can set it as not Visible.