#### SPECIAL QUERY

```HI ALL!

I have a table with following 3 fields:
Account_1, Account_2, Amount

Any query that would result into following layout?

Account (Containing Unique Names From BOTH Account_1, Account_2)
Amount_1 (Containing Amounts of Accounts Listed In Account_1)
Amount_2 (Containing Amounts of Accounts Listed In Account_2)

For instance, consider the following sample:

Account_1, Account_2, Amount
======   =======  =====
AccountA    AccountB     1000
AccountC    AccountA     2000
AccountB    AccountC     3000

To be generating a result as follows:

Account,     Amount_1,   Amount_2
======   =======  =======
AccountA    1000          2000
AccountB    3000          1000
AccountC    2000          3000

--
Best Regards,

Faraz

```
 0
Utf
2/10/2010 10:26:01 AM
access.queries 6343 articles. 1 followers.

1 Replies
756 Views

Similar Articles

[PageSpeed] 53

```If you don't have a table of the unique accounts then you will need a query to
get that as the first step.

SELECT Account_1 as Account
FROM SomeTable
UNION
SELECT Account_2
FROM SomeTable

Now you can write this query, assuming that the table and field names follow
the naming conventions of consisting of only letters, numbers, and the
underscore character.

SELECT Account, A1.Amount, A2.Amount
FROM (qUnionAccounts LEFT JOIN
(SELECT Account_1, Amount
FROM SomeTable) as A1
ON qUnionAccounts = A1.Account_1)
LEFT JOIN
(SELECT Account_2, Amount
FROM SomeTable) as A2
ON qUnionAccounts = A2.Account_2

Another option would be to use a union all query and then run a crosstab
against that

SELECT Account_1 as Account, Amount, "Amount1" as TheAmount
FROM SomeTable
UNION ALL
SELECT Account_2 as Account, Amount, "Amount2" as TheAmount
FROM SomeTable

Now use that in a crosstab query
TRANSFORM Sum(Amount)
SELECT Account
FROM qUnionQuery
GROUP BY Account
PIVOT TheAmount

If you need further instructions on how to build these queries post back.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Faraz A. Qureshi wrote:
> HI ALL!
>
> I have a table with following 3 fields:
> Account_1, Account_2, Amount
>
> Any query that would result into following layout?
>
> Account (Containing Unique Names From BOTH Account_1, Account_2)
> Amount_1 (Containing Amounts of Accounts Listed In Account_1)
> Amount_2 (Containing Amounts of Accounts Listed In Account_2)
>
> For instance, consider the following sample:
>
> Account_1, Account_2, Amount
> ======   =======  =====
> AccountA    AccountB     1000
> AccountC    AccountA     2000
> AccountB    AccountC     3000
>
> To be generating a result as follows:
>
> Account,     Amount_1,   Amount_2
> ======   =======  =======
> AccountA    1000          2000
> AccountB    3000          1000
> AccountC    2000          3000
>
> Looking forward for your expertise!
>
```
 0
John
2/10/2010 1:01:51 PM

Similar Artilces:

Create Email From Access W/Information From Form or Query in
I have designed Databases for many years now, but never attempted this: I need to know how to create an email with information located in the active form or off of a query. I would like to have the email automatically generated with the To Address, The Subject Line and the Body of the Text. I would like that email to open but not to send until the operator has a chance to make modifications. (one record per email). -- Mark Matzke Hi Mark, You could try the following to start: Place a button on a form and place the following behind the on click event Dim objApp as New Outlook.Applic...

Special Characters
Is there a quick way to convert all special characters to XML format for example '&' to &amp; or '+' sign to ? ? MAF wrote: > Is there a quick way to convert all special characters to XML format for > example '&' to &amp; or '+' sign to ? ? '&' needs to be escaped as &amp; but the '+' sign does not need to be escaped in XML. How you do it depends on the .NET APIs you use, if you use an XmlTextWriter to create XML then the WriteString method for instance does the escaping for you. If you use the DOM th...

Current Date in Query
Hi All, Hoping you can help. I'm using the following to get current age in a query: NewCurrAge: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs " & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos" My BIG problem is that I also want to pull certain ages, especially 9 to 13 for one query and 14 to 17 in another query. So, in the same query for criteria I used: Between 9 yrs 0 mos And 13 yrs 12 mos Error: the expression contains invalid syntax. you entered an operator witho...

Count or sum help on query
This is my SQL code for a query I'm running that is working great for my report. What I need now is that I need the total of Y's and N's for each column. So I need to know how to make my sql put at the bottom on report Total count for how many Y's for conductor casing,and how many N's, how many Y's for surface casing, How many N's, How many Y's for Prod casing, how many N's, How many CMPS Y's and how many N's and How many Wells on Prod Y's and N's count. Then my report will be complete. This is my code so far and my query is run...

Can't Copy and Paste or Paste Special between Excel Workbooks
We have a number of Excel users in our office who cannot copy and paste between Excel workbooks. They can copy and paste between worksheets. When you highlight the section to copy and then go to the new workbook both the paste and paste special are "grayed out". This is true whether you right-click the mouse, go to the edit menu, or use control keys. This occurs with any data type and the most simple workbooks. I have seen some suggestions here but none have worked for this particular problem. I have reset the menus and renamed the .xlb files and neither helps. You can open t...

Showing data that does not match Querie
Hi Guys/Girls this is my first time actually using Access fully but I would like to know if i can do one thing, I can import my 2 worksheets Emp1 and Emp2 the problem I have is I want to display a listing where if 2 queries match from emp1 to emp2. What it needs to do is this i know that there are entries that do not appear in emp2 and I want to see what does not appear. so basically to show data that does not match the Querie. Thanks in advance for any help. There is an unmatched query wizard that will build a query for you to show rows in table one that don't exist in table two....

Crosstab Query with query parameter used to create a report
My crosstab query using a query parameter works fine; however, once I try to use it to create a report I am asked multiple times to re-enter my query parameters. The report generates the correct results; however, why do I need to re-enter my query parameter multiple times?? Thanks.. Open the crosstab in design view, click on menu Query - Parameters. This opens another window. Type in your parameter names and data type. Save. -- KARL DEWEY Build a little - Test a little "JJ_Access" wrote: > My crosstab query using a query parameter works fine; however, once I try to ...

Query left outer join
I have the following query that I wish to return ALL vehicles and then show all trips applicable to those vehicles for the current date (pulled from a form). The problem I'm having is that the query returns only the vehicles that have trips for the day, not ALL vehicles. SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to, trips.pickup_date, trips.drop_date, trips.closed, trips.timeField, vehicle.SubContractor FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField]) AND (...

Trying to rename query
I'm trying to rename a query. I right click on the name and choose Rename. However, the box only stays open for a second and I don't have enough time to actually type anything. Suggestions? Thanks! "blemerson" <blemerson@discussions.microsoft.com> wrote in message news:0A126EDB-D7D4-4E55-8F18-22C586D862E1@microsoft.com... > I'm trying to rename a query. I right click on the name and choose Rename. > However, the box only stays open for a second and I don't have enough time > to > actually type anything. I haven't heard of this particular ...

Query Button Problem
On my form, I need to put a button to run a query. When using the toolbox in the Design View, I use the Command Button Toolbar, then use MIscellaneous, then Run Query. I select the correct one, and save it to the form. When I try to use it, I get an error message, and it will not work. What is going wrong? Can't figure it out if you don't provide an error message. On Fri, 1 Feb 2008 10:52:00 -0800, Shari <Shari@discussions.microsoft.com> wrote: >On my form, I need to put a button to run a query. When using the toolbox in >the Design View, I use the Command Button To...

A simpler way to do a pseudo-autonumber in a query
Hello all, Isn't there a simpler way of doing a pseudo-autonumber in a query? I saw in one post someone mentioned doing a subquery (IncNum: (SELECT Count(*) FROM thetable As X WHERE X.sortfield <= thetable.sortfield)). That might work on tables with small number of rows, but I'm working with a table that has 112,241 records in it. I did a query (Query1) to trim that down to 17,741 records, and then in Query2 I set up my pseudo-autonumber column based on Query1. Now I'm guessing that for every record in Query2, Query1 would have to be rerun twice on 112,241 records!!! ...

Text entry query
I am entering 5 lines of text into a block of cells and have Merge Cells selected in Format | Cells. When the last line exceeds a particular point in the text all that shows when I try and enter it is a single long line of #s. That line is not the longest line. Help anyone? Brian Tozer Try formatting the cell as General (not Text). If you need more than 1000 characters to show up in the cell, add a few alt-enters to force a new line--you didn't ask--but it's a common question with cells containing a lot of text. Brian Tozer wrote: > > I am entering 5 lines of text into a...

Specialized ComboBox
Hi. I'm trying to create a combo box, dropdown style, which will enter the text in the edit field to the list box upon hitting enter key, and allow deletion of items from the list box. I'm affraid i have no clue as to how to do this - i thought about deriving from CComboBox and adding handler to keydown. but no idea as to how to exactly. Help will be much appreciated. Ori, You first need to find out if the user has swapped left & right mouse buttons (GetSystemMetrics(SM_SWAPBUTTON)). Then if the user has swapped mouse buttons you need to hook WM_LBUTTONUP, if the user has...

escaping special characters with DataSet.WriteXml?
Howdy, Is there a way to explicitly escape special characters (particularly & (&amp;) and ' (&apos;)) when writing a data set with the WriteXml method? I know this is supposed to happen automatically (according to MS), but in my case, it doesn't. I'm pulling data from MS-SQL with c#, filling data tables with SqlDataAdapter and a query, adding tables to a DataSet, then WriteXml to a file. Special characters aren't getting escaped. Yes, I've confirmed this by viewing the text directly, not viewing the file through IE, which will convert entities back to chara...

Form not passing parameter to query
Hi All, Right, I have form with an unbound drop down list using a column in a table for a row source. I also have a query returning results from the same table. I have setup the query to use critieria [Forms]![frmname]![dropdownlistname] on the column concerned to look at the above form and the drop down list for the paramter. I have even double checked that I'm doing it correctly here http://office.microsoft.com/en-us/access/HA011170771033.aspx. However, for some reason the form just doesnt pass through the parameter from the drop down list into the query. The query runs but doe...

Help with queries
Hi Guys, This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query. Any help will be highly appreciated!! Here is what I need... I have relatively small ms acces database with about 1000 I have 3 colums date ipaddress sitename 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 33.4.4.4 B 12/09 33.4.4.55 C What i need is if an ipadress is recorded more t...

Append Query
I am trying to append data from tblitemline to tblitemlist. I only want to append new records. The unique field is ListID. I want to append 4 fields total. I can't get it to limit it to new records. Any help would be appreciated. Thanks! ...

Need Query help
Hello, [SQL2000] CREATE TABLE toll_drive ( Amount INT, dte DATETIME ); GO INSERT INTO toll_drive SELECT 10,'JAN 1 2010'; INSERT INTO toll_drive SELECT 100,'JAN 11 2010'; INSERT INTO toll_drive SELECT 65,'JAN 30 2010'; INSERT INTO toll_drive SELECT 10,'JAN 31 2010'; INSERT INTO toll_drive SELECT 24,'FEB 07 2010'; INSERT INTO toll_drive SELECT 50,'MAR 03 2010'; INSERT INTO toll_drive SELECT 60,'MAR 30 2010'; INSERT INTO toll_drive SELECT 90,'MAR 31 2010'; INSERT INTO toll_drive SELECT 100,'APR 14 2010'; ...

Special character in text column
I have a column defined as text field in MS Access 2003. I want to insert a string with prefixed of hex02, followed by Carriage Return, followed by Line Feed and ended with hex03. How do I do that manually? eg. (hex02 character)....This is a test.....(Carriage Return, Line Feed, hex03) Thanks Hex(2) & "....This is a test...." & vbCrLf & Hex(3) -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Alan T" <alanNOSPAMpltse@yahoo.com.au> wrote in message news:u1gDZXnUIHA.4280@TK2MSFTNGP06.phx.gbl... >...

Specials
We are having a special on item X for the month of December. With a the purchase of X comes a free A, B, and C. Now all these are compelety different items. Is there a way to set this up? I only see buy one get one free, buy x amount and get x% discount. Unfortunately i dont think RMS can deal with these kind of promotions - i have these sort running all the time, you can use the mix and match, but it makes the last item scanned the free one rather than the cheapest one, this is workable as long as the till staff know that the main item must be scanned first and then the items which a...

Access 2007 Query
I have a query that uses wild cards, what and parameter query box to ask for user input and then shows a report later on. what I want to do is create a form that override the parameter query box and instead through the form user can input for example name or number and click a button to show the report. how can I do it? Thanks in advance. Submitted via EggHeadCafe - Software Developer Portal of Choice Essential ASP.NET with Examples in VB.NET http://www.eggheadcafe.com/tutorials/aspnet/36a6a9ea-52d5-42b4-b84c-e77501d4a63b/essential-aspnet-with-ex.aspx On Fri, 01 Jan 2010 12:...

Payables Management query
Hi everybody - can somebody tell me which table holds the invoice information for payables management. I have to make a query that show which checks paid for which invoices and I have to include the invoice description as well. Thanks!! PM10000 - PM Transaction WORK File PM20000 - PM Transaction OPEN File PM30200 - PM Paid Transaction History File Hope this helps, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www.gp2themax.blogspot.com Michael, I have posted code for a SQL view that may already have everything you need except the invoice descrip...

2006 Special Offers
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C59420.040B7050 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable For a while I was getting notice to participate in the special offers (I = downloaded 06). I would always opt in but it would give me a server = error message. Now that notice has not come back in a few weeks. = Anyone have an ideas what is going on? adam ------=_NextPart_000_0006_01C59420.040B7050 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable &l...

How to get the info from form to query to report
I have a form that uses 5 tables for the information so I built a query to have the info all in one place. I also want to use the submit button to send the information to a report to be emailed, right now the report comes in blank. Please help I am fairly new to access and trying to build this for work. Base you report on a query. Use the form reference as criteria in the query like this --- [Forms]![YourFormName]![YourObject] [YourObject] is displaying the data from the 5 tables. -- KARL DEWEY Build a little - Test a little "sandyL" wrote: > I have a form that us...

a running sum (by date) in a query?
Is this possible? I have dates (some of which are the same) and I would like to use them to create a running sum of another column in my query. Is this possible? On Jul 24, 4:54 pm, grantschnei...@gmail.com wrote: > Is this possible? I have dates (some of which are the same) and I > would like to use them to create a running sum of another column in my > query. Is this possible? Sorry, not only do i need to create a running sum by date but it also has to be PER sales person. So even though all the sales people are in one table and I am querying the table different ways, I need to m...