Probably insanely simple

[lisacollins]

I'm trying to make a report to display certain entries from 2 different columns in a table. In the query, I designate which criteria I want from each category, but on the report, it shows more than what I've asked for.
I have an idea of what needs to happen, I just don't know how to do it.

 

[Sammy B]

Look and see if the query itself is giving you what you want. If not then the problem is probably in the query and not the report.

Sam

 

[reclusivemonkey]

I'm trying to make a report to display certain entries from 2 different columns in a table. In the query, I designate which criteria I want from each category, but on the report, it shows more than what I've asked for.
I have an idea of what needs to happen, I just don't know how to do it.

More detail would help. What do you mean by "more than what I've asked for"? Are you getting duplicates? Are you getting data which should of been filtered out? More fields than you requested? You may need to refine your query, or maybe group your data. Does each record have a value in both columns?

 

[lisacollins]

vague

There is data in both fields I've queried. I only want to show particular values in each field. Here's a pic of what I have. I want a report that counts how many records have those particular Issues and those particular Jurisdictions only. There are other Issues and Jurisdictions that are showing up on the report. For example, because some of the "Discipline" Issues are in the Jurisdiction of "DOR", the report counts the Jurisdiction for "DOR" also. I only want those Jurisdictions shown in the query.
Does that make sense?

 

[reclusivemonkey]

Lisa,

Screenshot helps a lot

Ok, if you ONLY want "Discipline" or "I.G. Reports" that are ALSO "AMFSCME" or "PERC" etc. you need all your criteria on one line. Currently, your query is getting ALL "Discipline" and "I.G. Reports" regardless of Jurisdiction. Its also then getting ALL "AMFSCME","PERC" Jurisdictions, regardless of "Issue 1". Hope this helps.

 

[lisacollins]

But...

...I don't want to count only the Disciplines in "PERC", "DOAH", etc. I want to count all the Disciplines, period, no matter in which Jurisdiction they are in, but...I don't want to count any other Jurisdictions on my report. I want it to look like the attached. Here are my query results, too.
Thanks for your help Monkey.

 

[reclusivemonkey]

Ok there are a couple of issues here. Firstly, you are wanting a subtotal report. Secondly you are wanting to group totals from different fields. Not impossible, but not completely straightforward.

You can easily get a count of how many "Discipline" or "I.G. Reports" you have. Create a query with just the fields "Start Date" and "Issue 1", turn on Grouping, and make sure it groups on "Issue 1" and change the query to "Count" on "Start Date". This will give you totals of each type of "Issue 1", PROVIDING you have a start date in every record. If not, then you need to count a field which is present for every record. A Primary ID key would be better, but I don't know any more about the structure of your DB. You can then do the same with "Jurisdiction", just have that and the start date. Now if you want the results of these two queries together, you can join them with a UNION query. To do this you will need fields with the same names, so when creating your initial queries, I would manually change the field names so you have something like "Title: [Issue 1]" and "Count [Start Date]". Before we get into Union queries, see how you get on with creating the "Grouped" queries and we'll take it from there.

 

[lisacollins]

ugh

I don't understand what you mean by turn on grouping. Do you mean in the report? I also don't understand change the query to "Count" on "Start Date".
I made 2 different queries: one for Issue and one for Jurisdiction. They both seem to show me what I want. I also played around with the Union Query. I can't get fields with the same name. I know you said manually change the field names so you have something like "Title: [Issue 1]" and "Count [Start Date]" but I'm not quite sure what you mean.
Sorry, you're dealing with a noob.

 

[reclusivemonkey]

I don't understand what you mean by turn on grouping. Do you mean in the report? I also don't understand change the query to "Count" on "Start Date".
I made 2 different queries: one for Issue and one for Jurisdiction. They both seem to show me what I want. I also played around with the Union Query. I can't get fields with the same name. I know you said manually change the field names so you have something like "Title: [Issue 1]" and "Count [Start Date]" but I'm not quite sure what you mean.
Sorry, you're dealing with a noob.

You really need to read up on queries in this case; Its as simple as "View --> Totals" to turn on grouping. The Totals appear under the table line in your query view. You then select your total type; you need one field to group on (this would be Issue/Jurisdiction), then a field to count for your total (this needs to be a field for which every record has a value).

With regard to renaming your fields, put exactly what I said in the query field. If you want Issue 1 to have "Title" as the field name, you put Title: [Issue 1] in the query field.

I don't mind helping "noobs" at all, but if me answering your questions raises more questions, then I'll very quickly get bored. I would suggest you have a look at a) the Access help b) any sample databases you have (i.e. Northwind) c) any Access tutorials on the net. Search the forum as there are many links to these. Best of luck.

 

[lisacollins]

Thanks Monkey. You've been a great help. I've almost got it.

 

[reclusivemonkey]

No problem, glad you are getting there. One other thing I would mention; you are querying on two criteria here; can you link these two criteria into one table? If all your Issues belong to several Jurisdictions, you could have all these in a table like;

ID Issue Jurisdiction
================================
1 Contracts DOR
2 Contracts DOAH
3 Contracts N/A
4 Discipline DOR
5 Discipline DOAH
6 Discipline N/A

...and so on. Now only you will be able to tell whether this is worthwhile. I don't whether this is a DB you have inherited, or are working on. But with the above table, you can filter the Issue and Jurisdiction with one field. This should also save on data entry once you have this table set up.

Hope this helps.

 

[lisacollins]

This DB is pretty old and I didn't start it. Your idea sounds great to me, but the powers that be want it to be JUST like it looked in Excel.
I've got my Select Queries doing exactly what I want. My question now is the Union Query. In my Select Queries, I have duplicates, and I want that. Unfortunately, when I run the Union Query, it gets rid of all duplicates.
Here's what my Union Query looks like. Pretty simple as far as I can see in the Access book, but...

 

[reclusivemonkey]

This DB is pretty old and I didn't start it. Your idea sounds great to me, but the powers that be want it to be JUST like it looked in Excel.

Thats understandable. However, this doesn't stop you creating the database correctly. If your structure isn't right, your database will only become more unwieldly and difficult the more you use it; the format of your eventual output will not resemble your table structure at all. Also, you can recreate everything in Excel in an Access report; they are very different beasts. If the data is needed in Excel, you can always export your data out from Access to Excel and then format the data in Excel.

I've got my Select Queries doing exactly what I want. My question now is the Union Query. In my Select Queries, I have duplicates, and I want that. Unfortunately, when I run the Union Query, it gets rid of all duplicates.
Here's what my Union Query looks like. Pretty simple as far as I can see in the Access book, but...

You need to add a unique field to each select query to prevent the Union query adding the data together. Again, this would be solved by correctly setting up your tables.

 

[lisacollins]

Got it!! Its perfect. Thanks for all your help.