Query question -- Access 2000

[HandJT]

Hi

I am trying to create a query that will pull information off of two tables. Table one and table two. In table one, I have two fields, scraps and repairs. In table two, I have two fields, scraps and repairs. The tables are from two different department. What I want the query to do is add everything in field one, field two, field three, and field four. Not together, just.... basically, adding the columns so I get four totals. I want to use these totals later for a chart to compare the scraps and repairs in each department. When I create a query to do this, it doesn't work, however if I create two seperate queries for the two seperate tables, it works great!! However, when I use the chart wizard, I can only choose one table or query, not both. So, I'm stuck in the middle. Does anybody have any ideas on how to create this query

 

[roppetech]

It should be simple to create a query with these four fields.
Try this if you haven't already:
Create a new query in design view.
click the 'add table' button
add both of your tables
double-click each desired field in the field lists
You should see all four fields on the bottom of the screen
click run (exclamation point) on the toolbar

Does it error off at this point? if so, what error message does it give you?


- RoppeTech

 

[HandJT]

It doesn't give me an error. Just when I run it, the sums don't show up, when I use both tables. If I just use one table it will work. I tried unlinking the tables, that didn't work, and I did exactly what you mentioned but it didn't work. This seems to be an easy thing to do, but it doesn't seem to work.

 

[fneily]

Create two queries. One's a duplicate of the other with just a differenct table. Your query will have two columns Scraps and Repairs. Click on the Sum button to bring up the Total row. Change Group By to Count. You will now have one record with the counts of scraps and repairs from one table. Do the same with the other table but change the column headings to Scraps1 and Repair1 or something. You get another record with just a sum. Create a third query that brings these two queries together with no joins. It will have four fields Scraps, Repairs, Scraps1, Repairs1. It will produce one record with all sums. Use as input to chart.

 

[fneily]

I reread your post. Here's a revise version.
For table1, create a query. Headings in query are:First column has PS1:Scraps, second column has Sum1:Scraps. Click the Summation button to make the Total row. Under the first column, it should say Group By. Under the second column, it should say Count. This produces the counts of different scraps from the first table.

For table2, create a query. Headings in query are:First column has PS2:Scraps, second column has Sum2:Scraps. Create a Total row. First column has Group By, second Count. This creates the counts of different scraps from table 2.

Create a query but go into SQL View. Type in:
Select PS2 AS PS3
FROM [NAME_Of_Second_query]
Union Select PS1
FROM [Name_Of_First_query]; (NEED THE [])

Create a fourth query that has the above queries in it. Join the last query to the first two. So join PS3 To PS1 AND PS2. Double click on the join lines and select number 2. So you created a left outer join between the last query and the two count queries. In the grid, bring down
PS3, PS1, Sum1, PS2, Sum2.

Your output will then show the ALL the different Scraps names under PS3 and the corresponding names and sums of the two tables.

 

[fneily]

My above rationale is that the two departments could have the same type of scrap item or one table may have one type and the the other doesn't have that type. So the output will show ALL types and then the individual types in each table. Now, if you want to look special, create a histogram comparing the two tables. Bar and column charts are boring and people are impressed by this.

Create a Histogram:
Make the output of one of your sum columns, such as Sum2, have negative numbers.
You'll create a chart using the PS3, Sum1, Sum2 columns.
1. Create a 2D bar chart. Use the Subtype Clustered Bar.
2. Select the vertical axis and access the Format Axis dialog box. Click the patterns tab and remove all tick marks. Set the Tick mark labels option to Low. This keeps the axis in the center of the chart, but displays the axis labels at the left side.
2a. Apply the following custom number format to the horizontal axis: 0;0;0 This eliminates negative signs.
3. Select either of the data series and then access the Format Data Series box. Click the Options tab and set the Overlap to 100 and the Gap width to 0.
4. Delete the legend
5. Add two labels to the chart to substitute for the legend.

 

[HandJT]

I'm kind of new to Access so you completely lost me there, however I did create 3 queries. One for Table one, which gives me the total for the scraps and then the total for the repairs. I did the same thing for table two. Then the third query I created, I just had it display the sums of the scraps and repairs from both tables. I now would like to create an ongoing timeline chart which the vertical line on the bottom indicates dates and the horizontal line on the left indicates numbers 10-150 or so, then in the grid part of the chart, I would like it to compare the scraps from both tables (which are based on departments in the company). Then I want to create another chart doing the exact same thing only comparing the repairs from both tables. I've played around with the charts and have been very unsuccessful on accomplishing this so any advice or hints or tricks you have would be greatly appreciated. Thank you so much for your help. You gave me the idea from what I accomplished and for that I thank you again.