One Report using data from multiple queries with criterias set

[FranCorona]

I have 4 queries that have all the same info except the last column.

Column Headings for query1: Groups, Unit, Model, Admin, FMC Cost Out with the criteria of "is not null" for FMC Cost Out.


Column Headings for query2: Groups, Unit, Model, Admin, NMC Cost Out with the criteria of "is not null" for NMC Cost Out.
Column Headings for query3: Groups, Unit, Model, Admin, RnR with the criteria of "is not null" for RnR.
Column Headings for query4: Groups, Unit, Model, Admin, Warranty with the criteria of "is not null" for Warranty.

I tried creating one query but because of the criteria it only shows records for FMC Cost Out because that is the first criteria. I then looked at creating a union query but all data is from one table and it didnt work using the 4 queries. I tried putting subreports but that didnt look right either. I was trying to figure out crosstab queries but the one I tried didn't work either.....any suggestions? Thanks and have a great weekend!

~Fran~

[June7]

A UNION should be possible. Are those 4 fields all cost data? You want them combined into one field?
SELECT Groups, Unit, Model, Admin, [FMC Cost Out] AS Costs, "FMC" AS Source FROM tablename WHERE Not [FMC Cost Out] Is Null
UNION SELECT Groups, Unit, Model, Admin, [NMC Cost Out], "NMC" FROM tablename WHERE Not [NMC Cost Out] Is Null
UNION SELECT Groups, Unit, Model, Admin, RnR, "RR" FROM tablename WHERE Not RnR Is Null
UNION SELECT Groups, Unit, Model, Admin, Warranty, "War" FROM tablename WHERE Not Warranty Is Null;

That UNION query could be the source for a CROSSTAB.

What is the desired output - aggregate data?
Did you try a GROUP BY (Totals) query with the source table?
Maybe a report using Grouping and Sorting with aggregate calcs in footers?

[FranCorona]

Hey you! It's not cost data; in those columns the clerk enters a + sign in the field of the form if it applies. I don't know why the supervisor wants the + sign but that is what they have been using. If I do the query as you suggest do I just leave out the AS Costs?

The desired result is a report that shows only what has the + signs for each column leaving out all the other data without the + signs. That way we get a picture of what equipment was cost out (FMC, NMC, for Warranty or RnR). We don't track the actual cost of them only what equipment was cost out. I hope I am making sense....ding dang it....LOL I am gonna put it away for now and pick up next week. Thank you again for your help! You have a great weekend OK!

[FranCorona]

Hey it worked! Yay! It asks me for the perameter for the source but if you leave it blank and click ok it goes to the report and shows all the data I need. Why is it asking for the perameter?

[June7]

Use some other alias name besides Costs, whatever you want to call that field. The first line of UNION establishes field names.

Did you put the Source values within quote marks?

Post your attempted sql statement for analysis.

[FranCorona]

Here is the SQL statement that works but brings up the parameter box: (I had to put in the [] on all of the fields because it said it could not find them)SELECT [BCT Unit], [Grid Model], [Admin], [FMC Cost Out], "FMC Cost Out" AS Source From [Rotational Grid] Where Not [FMC Cost Out] Is Null
UNION SELECT [BCT Unit], [Grid Model], [Admin], [NMC Cost Out], “NMC Cost Out" FROM [Rotational Grid] Where Not [NMC Cost Out] Is Null
UNION SELECT [BCT Unit], [Grid Model], [Admin], [Warranty], "Warranty" FROM [Rotational Grid] Where Not [Warranty] Is Null;
When I try to put in the AS Costs into the SQL statement it comes up with this message when I try to run it:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

[June7]

I don't know why it brings up parameter box. I also don't know why it says there is a reserved word. That UNION syntax works for me.

I do see that the post shows a quote mark in front of "NMC Cost Out" that is different from the others, which is odd. I have run into this before when copy/paste from web. Access doesn't like that quote mark.

[FranCorona]

There isn't much I can do today....all of post is without power until 3pm so I won't be able to mess with it at all....I will get back to you and see if I type it all into Access myself, if it does the same thing....have a great day!

[Clayton]

Here is the SQL statement that works but brings up the parameter box: (I had to put in the [] on all of the fields because it said it could not find them)SELECT [BCT Unit], [Grid Model], [Admin], [FMC Cost Out], "FMC Cost Out" AS Source From [Rotational Grid] Where Not [FMC Cost Out] Is Null
UNION SELECT [BCT Unit], [Grid Model], [Admin], [NMC Cost Out], “NMC Cost Out" FROM [Rotational Grid] Where Not [NMC Cost Out] Is Null
UNION SELECT [BCT Unit], [Grid Model], [Admin], [Warranty], "Warranty" FROM [Rotational Grid] Where Not [Warranty] Is Null;
When I try to put in the AS Costs into the SQL statement it comes up with this message when I try to run it:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Try "UNION ALL"? UNION command filters out non-unique records. Might help?

Also, to OP:
You mentioned the user would "enter" a value into your report? Access Reports cannot have data entered, are you really making a form, or do you print the reports and they mark them by hand?

Also also, to OP:
Did you want your report to look something like:
"| Groups | Unit | Model | Admin | FMC Cost Out |"
<Records>
"| Groups | Unit | Model | Admin | NMC Cost Out |"
<Records>
"| Groups | Unit | Model | Admin | RnR |"
<Records>
"| Groups | Unit | Model | Admin | Warranty |"
<Records>
If so, just create a report for each of FMC, NMC, RnR, and Warranty, then create a new blank repor in design mode, then click-and-drag those 4 reports into the blank report where they will now be "Subreports"

Did you want:
"| Groups | Unit | Model | Admin | FMC Cost Out | NMC Cost Out | RNR | Warranty |"
<Records>
If so, try something like
SELECT [Table].[Groups], [Table].[Unit], [Table].[Admin], [Table].[FMC Cost Out], [Table].[NMC Cost Out], [Table].[RNR], [Table].[Warranty]
FROM [Table]
WHERE ([Table].[FMC Cost Out] Is Not Null or [Table].[NMC Cost Out] Is Not Null or [Table].[RNR] Is Not Null or [Table].[Warranty] Is Not Null);

[FranCorona]

Hey There! The UNION SELECT ALL worked perfectly! Thank you so very much! You are awesome!

What I wanted and got:

Headers: Unit, Model, Admin
Group by Source (FMC Cost Out, NMC Cost Out, Warranty)
Then the data

It works wonderfully!

[FranCorona]

Hey there! It's me yet again..... I was wondering if you could help me with an archiving code. Not to move the record but only so that it does not print on the operator's ID card..... I have been looking through the posts about archiving and can see how it is not a good idea to create a new table with this info but still need it not to print with the active employees. I did get it to move to an archive table but only if it is the whole employee record that is being archived. If I try to archive only a piece of equipment for example it does not associate that record with the employee....do I make sence? I YI YI I'm sorry....I know I'm a pain....LOL Thanks again for any and all help! Have a great afternoon!


Fran

[June7]

Inactive employees?

Create another field in employees table, either a Yes/No or a DateTime or text. Call it IsActive or DeactiveDate or something like that. Filter records with criteria for this field.

[FranCorona]

I figured that out! Thank you for the quick response....LOL I have a habit of making things more difficult than they really are.....

[FranCorona]

Good Morning! Ok. I got the employee records to filter accordingly, but with the equipment I'm having a problem. This is the senario: Say an employee is licensed on equipment type one and then that piece of equipment is turned in and no longer on the contract, we don't want that to show on the license anymore but to stay in his training record. Now I know to set the criteria but what I need it to do is automatically archive on the ALL employees training records when the box is checked on the equipment form. I have added the fields: Archive (yes/no) checkbox and Archive Date to the training tables from the equipment table but when I check the box in the equipment form it is not updating in the employee training table.....

[June7]

Now you want the equipment listed on employee's license card dependent on a contract terms? What kind of contract? Why should this matter? So the card shows all equipment employee is licensed for. If the equipment isn't there, then they won't use it.

You have a table that associates contracts and equipment?