combining 2 query/tables

I have 2 tables/query that i would like to combine to make a simple
form/report for.  The data was downloaded from the system and I am not
able to change the format.  What i need to do is make a simple report
that compare the 2 queries result that show that there is no
variance.  Thank you.

Query 1 sample
model 	Type	           Amount
1	freight cost	        100
1	processing cost	    200
2	freight cost	        150
2	processing cost	    175
3	freight cost	        210
3	processing cost	    234

Query 2 sample
model 	freight 	processing cost
1	    100	            200
2	    150	            175
3	     210	    234
0
Paphiat
11/30/2007 5:31:39 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
655 Views

Similar Articles

[PageSpeed] 7

Use a union query to normalize the data in query two

SELECT Model, "Freight Cost" as [Type], Freight as Amount
FROM Query2
UNION ALL
SELECT Model, "ProcessingCost" as [Type], [Processing Cost] as Amount
FROM Query2

Then you can join the union query to query one

SELECT QueryOne.Model
, QueryOne.Type
, QueryOne.Amount
, QueryTwo.Amount
FROM QueryOne LEFT JOIN queryUnion
ON QueryOne.Model = QueryUnion.Model
AND QueryOne.Type = QueryUnion.Type
WHERE QueryOne.Amount <> QueryUnion.Amount
OR QueryUnion.Amount is Null
-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

<Paphiat@gmail.com> wrote in message 
news:36ff112a-44ba-4337-81f8-504977878444@l1g2000hsa.googlegroups.com...
>I have 2 tables/query that i would like to combine to make a simple
> form/report for.  The data was downloaded from the system and I am not
> able to change the format.  What i need to do is make a simple report
> that compare the 2 queries result that show that there is no
> variance.  Thank you.
>
> Query 1 sample
> model Type            Amount
> 1 freight cost         100
> 1 processing cost     200
> 2 freight cost         150
> 2 processing cost     175
> 3 freight cost         210
> 3 processing cost     234
>
> Query 2 sample
> model freight processing cost
> 1     100             200
> 2     150             175
> 3      210     234 


0
John
11/30/2007 5:53:06 PM
Reply:

Similar Artilces:

Proper Table(s) Layout
I have created a couple of Dbs which the table structure seemed to simply fall into place, it was just logical in my head. That said, I am working on a new db and for some reason I am doubting myself and wanted a second opinion. The db is basically a contract db to input all the info, and there is a lot of info, for each contract. Where I am 'lost' is the fact that the contracts are broken into categories: clients, components,engineering, warantee... For all of the components (with the exception of clients) there are a number of fields but only 1 entry per contract....

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Table link documentation
I am having trouble trying to locate A/P check data that has project related costs. I found the check data but it is does not indicate the projects, I found the project data but can not determine thye logical link between the two tables, I may be using the wrong tables the tables I am using are PM80500 and PA31102. Is there any documentation of how all the tables in the system are logically link. I am trying to write reports in MS Access, but there are 1500+ tables in GP (version 10) -- Dave F In an effort to find the correct table you can do a number of things (believe me I do)....

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

Duplicate Rows
I have an extract from a student information system in Excel that looks like this. Student Class Grade Quarter John Chemistry 70 1 John Chemistry 80 2 John Math 95 1 John Math 100 2 Alice Chemistry 67 1 Alice Chemistry 47 2 Alice Math 88 1 Alice Math 85 2 What I would like is this: John 70 80 95 100 Alice 67 47 88 85 However, since there are hundreds of students, this would be an extreme pain to do by hand. Is there any built-in formula or function in Excel that can do this? What is it that you actually want to do? (The best approach depends on what your desired end r...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

VCR to DVD Part 2
Many thanks for previous answers. Yes, finalizing the DVD worked I found that DVD-R need finalizing but DVD+RW disks do not But why was I able to play the unfinalized DVD-R on one machine and not on two others. What was special about the first PC? -- www.stfx.ca/people/bliengme Bernard Liengme wrote: > Yes, finalizing the DVD worked > I found that DVD-R need finalizing but DVD+RW disks do not > But why was I able to play the unfinalized DVD-R on one machine and not on > two others. > What was special about the first PC? Almost anything will play a finalised D...

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

set print area #2
Where is the "set print area" button in Excel 2002 that was in Excel '97? =?Utf-8?B?Q2Fyb2wgTWM=?= wrote > Where is the "set print area" button in Excel 2002 that was in Excel '97? > Are you sure it was "standard"? Only way I could get in was to right-click the toolbar|Customise and drag it to the toolbar from Commands|File -- David ...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

OEM publisher 2002 Sp1-2
I have the OEM version of Publisher 2002 and cannot find the Export as web page under File. Is there a way I can get that feature with a standalone disk? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Chicagogal <anonymous@discussions.microsoft.com>... > I have the OEM version of Publisher 2002 and cannot find the Export > as web page under File. Is there a way I can get that feature with a > standalone disk? The OEM version of Publisher is exactly the same as the release in every way but the license, so the standalone version will be no differen...

GP SP#2 hangs with sa and Collections Issue
I just installed 8.0 SP#2 (ran the script that it says to run cuz we had upgraded from previous version to remove dups in smartlist tables) and when I log into GP as sa, it hangs and CPU sits at 100%. When I was able to log in previously to SP#2, I constantly received errors about the CN_Collector_MAIN table with invalid column names etc. I've deleted the table, then re-ran the create tables, but it keeps coming back with this error. I've even gone into file > maintenance > sql and deleted and recreated the table, but then get "number of results doesn't match ...

Pivot Table Defaults
In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"? Hi The rule that the PT Wizard adopts is, If all the values in the field being added to the data area are Numeric, then it uses Sum. If any of the values are Text or BLANK, then it uses Count. It sounds as though you have defin...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

How can I print on 8 1/2"x17" paper?
I have cut down an 11x17 paper to 8 1/2" x 17". I cannot figure out how to change the paper size to accept and print anything bigger than 14." any suggestions would be greatly appreciated First make the size settings in the Printer Setup. You may need to use the Custom selection. -- Don Vancouver, USA "mickilena" <mickilena@discussions.microsoft.com> wrote in message news:1AD99AFE-7FE7-4A07-9D20-1B1AFF9856BD@microsoft.com... >I have cut down an 11x17 paper to 8 1/2" x 17". I cannot figure out how to > change the paper size to accept a...

when opening an excel file, 2 files open (one is book1)?
When I click on an excel file, 2 excel documents open up. One document is called Book1 and the other document is the actual document that I want to open. How do I stop this from happening? Brian To prevent Book1 from opening you can append /e to your shortcut for opening Excel. "C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL.EXE" /e Alternative to the above............ Go to Start>Settings>Folder>Options>File Types. Scroll down to MS Excel Worksheet. Then if running Win98 OS Edit> select "Open" and Edit. If using WinXP OS you would scroll down to...

Money Updates #2
Was wondering about money updates. What exactly is the program downloading during the update process? And if the program is updated on a daily basis, why would I ever need to upgrade to a newer version? In microsoft.public.money, invalid wrote: >Was wondering about money updates. What exactly is the program downloading >during the update process? And if the program is updated on a daily basis, >why would I ever need to upgrade to a newer version? It is mainly changes in online bank setup. Despite appearances is typically does not change anywhere daily. "invalid" <...

Can't create the item #2
I use Outlook from MS Office 2003 Business Edition. Everything has worked fine until the past few days. Now, whenever I click on an E-mail address link inside a webpage, I get a message from Outlook that says "Can't create the item." One person on this group gave me the following advice: >Close Outlook, find and rename the frmscache.dat to .old I did searches on all my drives and the file named above was not found. I doubled checked the search to confirm that it included hidden files and system folders in the search. Still no luck. More thoughts? ...

Does POS 2.0 support price-embedded barcodes?
Does anyone know if Microsoft Point of Sale 2.0 supports price-embedded barcodes (also known as random weight barcodes)? I have Digi scales that print out 12-digit barcodes with UPC and price info. I would like to implement POS and not RMS because it is much less complex. Thanks for any info. POS 2.0 does not support price-embedded barcodes. "Martin" wrote: > Does anyone know if Microsoft Point of Sale 2.0 supports price-embedded > barcodes (also known as random weight barcodes)? > > I have Digi scales that print out 12-digit barcodes with UPC and price info. &...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...