Every week I publish construction update reports for a big group of people. I used to cut, copy, paste, format, and scale things from MS Access into MS Excel, but we’ve run into problems (like nobody else knows how to do it, and it’s just too freakin’ tedious).
The past two weeks, I’ve been fooling around with Access and want to do a one or two click process which exports my stuff into a Word document. In any case, I’ve ginned up a report, but it has all my database groups:
For example:
*Advertised Projects
Awarded Projects
Awaiting Paperwork
Under Construction
Punchlist
Warranty-Related
Completed
*
Out of this list, I only want say Advertised Projects, Awarded Projects, Under Construction, and Punchlist.
In any case, I can’t get my report to do only certain groups. Is there a way to filter out the ones I don’t want?
Tripler
Thanks. I promise beer to anyone that can help (and I’m serious about that!)
I’m not entirely sure I’m following you. Are you basing your report off of a table or a query?
If you’re basing it off a query, can’t you just add a criterion line at the bottom that’ll filter your data as needed?
If I’m way off base, can you maybe post the SQL for the query off of which your report is based?
Daniel
PS For help from the leet Access Ninja Squad, check out www.utteraccess.com . I go there whenever I have a tough question, and always get a great answer.
If I understand your question correctly, you need use a query to feed your report.
So create a new query with the applicable tables that you want to report.
Go into the Design View of the query, and at the bottom of the screen is a place for you to enter the fields that you want reported from the query. On the bottom of this field list is a row labelled Criteria.
Click and drag the important field from the table list on the top of the screen to the Field list at the bottom. Then in the criteria section, set your criteria in the query to:
“Advertised Projects”
<OR> “Awarded Projects”
<OR> “Under Construction”
<OR> “Punchlist”
with each value in the next row beneath the field name. Access should insert the <OR> for you
Then create your report based on the query.
There are a number of other ways to do this, but this is the easiest to explain.
I thought that you could base a report on a table. Just have it display the fields that you specify. Open a New Report and Access will ask if you want to base it on a table or a query. Choose the table or query and drag the fields into the report. You can format it after that to get the look that you want. You can even use the wizard and it will assist you in setting it up. Queries are good for totaling and such but if you don’t need that then use the table only.Good Luck
Toddly, the reason to use a query is so that you can set up criteria – if you do it straight from a table, you can’t use criteria.
Now, you can make a query attached to a report that doesn’t show up anywhere else in Access; open the report’s properties, go to the “Data” tab, and click the three dots next to “data source.” This will open up a new query screen, which you can then use for designing your report’s query. If you won’t use the query for multiple reports, this is a good thing to do; if you will, though, you might want to design and save the query separately, and then you can reference it with each report, rather than starting over with each report.
Eliphat, thanks a lot for giving a much more thorough and eloquent answer than I did – now YOU’RE gonna be the one that gets the beer! and welcome to the boards.
Do you have a table somewhere that has entries (one per group) describing all possible groups? - if not, make one, add a boolean field, create a datasheet form based on the table (or just view the table in datasheet view) - click the checkbox next to the fields describing the groups you want (or enter True, depending on what it looks like).
Now add the table to the query that feeds your report; join it on group to the projects main table and add the boolean field (from the ‘groups’ table) to the query design grid - in the criteria row below, type True - that should do it.
and welcome to the boards.