Join Two Queries
May 2, 2006
Dear friends
I maintain a document database wherein query is generated for individual status like approved, pending & due. approved and pending are generated through "like text*" in status filed. But for due it is separate query using >=Now() in date field.
I want to join these two queries and result should be in one query as :
Approved document
Pending Document
and from balance document using Now query - due.
One report is attached which will help understand - blank yellow field requires due query.
Please help
Thanks - Navkesh Chawla
View Replies
ADVERTISEMENT
May 3, 2006
Ok, I have having a problem joing these two queries. Can anyone please help me out here. These are the results of my two queries:
These are the values in the A1 field of QueryA
COCKBURN 2
GT KW 1
GTN 1
IPP GT1
IPP GT2
KALG F5
KALG F6
KWIN C6
MUJA 1
MUJA 2
MUJA 3
MUJA 4
MUJA C5
MUJA C6
MUJA D7
MUJA D8
These are the values in fields A1 and X1 in QueryB:
COCKBURN 86.83
GT KW1 10.44
GTN 1 16.13
IPP GT1 62.84
IPP GT2 62.56
KALG F5 15.12
KALG F6 20.83
KWIN C6 37.78
MUJA 1 23.44
MUJA 3 8.69
MUJA 4 23.44
MUJA C5 84.56
MUJA C6 89.01
MUJA D7 86.22
MUJA D8 84.65
In QueryB, the entry MUJA 2 is missing. What I need is to take the X1 field from QueryB and join those values with the A1 field from QueryA as follows:
COCKBURN 86.83
GT KW1 10.44
GTN 1 16.13
IPP GT1 62.84
IPP GT2 62.56
KALG F5 15.12
KALG F6 20.83
KWIN C6 37.78
MUJA 1 23.44
MUJA 3 8.69
MUJA 2
MUJA 4 23.44
MUJA C5 84.56
MUJA C6 89.01
MUJA D7 86.22
MUJA D8 84.65
How can this be done. I need this in another query. I have tried the join statements, but I get errors every time. Thanks in advance.
View 12 Replies
View Related
Mar 21, 2007
I have three queries that I would like to make into one query, but I'm having trouble. How do I combine the three queries into one? The three queries are below. The qryGetMaxChapter3 is the one that I will use, i.e. it is built on qryGetMaxChapter2, and qryGetMaxChapter2 is built on qryGetMaxChapter1.
qryGetMaxChapter1
SELECT tblCourt.CourtID, tblCourt.ID_Number, tblCourt.ActionDate, tblCourt.Chapter
FROM tblCourt
WHERE (((tblCourt.CourtActionsID)=1 Or (tblCourt.CourtActionsID)=2));
qryGetMaxChapter2
SELECT tblCourt.ID_Number, Max(tblCourt.ActionDate) AS MaxOfActionDate
FROM tblCourt INNER JOIN qryGetMaxChapter1 ON tblCourt.CourtID = qryGetMaxChapter1.CourtID
GROUP BY tblCourt.ID_Number;
qryGetMaxChapter3
SELECT tblCourt.ID_Number, tblCourt.ActionDate, tblCourt.Chapter
FROM tblCourt INNER JOIN qryGetMaxChapter2 ON tblCourt.ID_Number = qryGetMaxChapter2.ID_Number
WHERE (((tblCourt.ActionDate)=[MaxOfActionDate]));
View 5 Replies
View Related
Dec 1, 2004
I am having a problem trying to join two queries. The first query, which is called qry_Planned_By_Month comes from a table (tbl_planned) that captures budget planning information. I created an expression in this query called SumOfPlanned. It takes the sum of all common budget line items (BLI) and is broken down by month.
The second query is called qry_Actuals_By_Month, which comes from a table (tbl_actual) that captures the actual amount spend against each BLI. Again, I created an expression in this query called SumOfActuals. It takes the sum of all common BLIs broken down by month.
Problem: I am trying to create a third query where I can compare planned versus actual. The formulas that I am using to do this are accurate. However the result set contains duplicate data. For example, I may have three entries on the planned side (March, June, and December) and on the actual side for the same BLI, I only have data in February. It displays the February data three times (because it is in the same row as the March, June, and December data.
Is there anything I can do? Thanks very much in advance. URL
View 1 Replies
View Related
Jan 2, 2014
An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?
View 7 Replies
View Related
Jul 28, 2013
I tried doing a search but couldnt find the i needed.
Category_name Sku
cars 1
Trucks 1
Bikes 1
Phones 2
Tablets 2
Hats 3
and what i need to is something that will link all sku 1 category names in a new field with a | divider and then all the sku 2 category names together with | as a divider and so on. the amount of categories is different for every sku.
so it looks like this
Joined_categories sku
Cars|Trucks|Bikes 1
Phones|Tablets 2
Hats 3
And if this is easy enough, its not that important though but to delete duplicate category names when it transfers them across to a new field joined together.
View 14 Replies
View Related
Jul 30, 2015
I have my main linked table VI, and 4 of VI's fields are to be filtered in a query.
To do so, I have 4 very very tiny local tables (less than 20 records) called respectively "soff", "skus", "warr" and "typ", in which the user will only put what he wants in the result, and by doing Inner Joins, I filter it easily.
Problem is, doing more than 1 Inner Join slow the query so slow it's unbelievable.
If I run the query:
Code:
SELECT VI.*
FROM VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku;
It runs in 0.7 seconds. It's about the same if I replace SKU by SOFF, WARR, or TYP, the query takes about 0.5 to 1.5 sec.
Now if I run:
SELECT VI.*
FROM (VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku)
INNER JOIN SOFF ON VI.SOff = SOFF.Soff;
It takes either 15 seconds, or up to 100 seconds.
I tried by doing 4 successive queries, it's the same. The 1st query runs well, and then it grinds to a near-halt.
Running either the 4 queries or a query with the 4 inner joins takes me about 200 seconds, sometimes 400-500.
It's not even a hard query, VI has only almost 1mil rows, and the fields are indexed. The result is 800 rows.
If each join on VI takes 1 second, it should do 1sec+1sec+1sec+1sec, taking in account the fact that the left side of the join grows smaller at each step, it should even do something like 1+0.8+0.5+0.2, or something. Why does having these joins together, or follow each other, make things so damn slow?
View 7 Replies
View Related
Jul 29, 2013
I currently have two tables each containing information that I need to complete a query, however the results of suming this information is incorrect.
Below is the structure of the table, one contains more fileds than the other however the joins are on the Date and the Name fields. I have added a few dummy results to show the format
Table 1
"Date" "Name" "Times Logged In"
10/01/2013 Chris 1
11/01/2013 James 3
Table 2
"Date" "Region" "Manager Name" "Name" "Times Logged Out"
10/01/2013 Scotland Cindy Chris 1
11/01/2013 Wales Robert James 2
Query Fields
"Data" "Region" "Name" "SUM Times Logged In" "SUM Times Logged Out"
I can see what the issue is, if someone hasnt logged out there would be no entry in the table so the result query would show "Times Logged In" as X and a blank value at "Times Logged Out." However if there is a value at "Times Logged Out", "Times Logged In" is displayed again and causes the SUM calculations to be incorrect.
how do I overcome this issue though? I can do two spereate queries on the tables to produce results for Times Logged In and Times Logged Out but cant seem to create one query to display both these results in one table.
What I want is IF the date in Table 2 = Date in Table 1 AND IF Name in Table 2 = Name in Table 1 THEN take the value from Logged In and place that in a column and the value from Logged Out and place that in a column.
View 2 Replies
View Related
Jan 9, 2015
I have a table and a query. The first 4 fields of the table correspond to that of the query. The query does not have any other fields, but the table has 26 more fields. Is it possible to use SQL OUTER JOIN or UNION or whatever to append the data of the query to the table or do I have to go with recordsets of VBA?
View 6 Replies
View Related
Oct 16, 2013
I am looking to create a query using a main table and 3 related tables to produce a count of evaluations completed and evaluations passed by month (using the 3 related tables) and site location (using main table) which is joined by agent name. I have the following so far but:
Code:
SELECT
[MainTbl].Location,
Format$([Tbl1].[EvaluationDate],'mmmm yyyy') AS [EvaluationPeriod],
Count([Tbl1].[Agent Name]) AS [NoCompleted],
Count(IIF([EvaluationScore]>=0.9,0)) AS [NoPassed]
[Code] .....
But I get the following results:
Code:
Location EvaluationPeriod NoCompleted NoPassed
Location1 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location1 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location1 Month1 Tbl3.NoCompleted Tbl3.NoPassed
Location2 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location2 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location2 Month1 Tbl3.NoCompleted Tbl3.NoPassed
etc..
View 4 Replies
View Related
Mar 21, 2014
I'm having problems doing a left join with 3 fields. I know I don't need the 3 fields for this example to align the data but, I deal with much more than this and need this functionality.
The query object I'm looking into is named 'left join' and the sql is as follows:
SELECT Act_Base.CustID, Act_Base.Chipset_ID, Act_Base.Product, Act_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE
FROM Act_Base LEFT JOIN SE_Base ON (Act_Base.Product = SE_Base.Product) AND (Act_Base.Qtr = SE_Base.Qtr) AND (Act_Base.CustID = SE_Base.CustID);
I'm expecting to see 150 units in the SE.Qty field.
[URL] ....
View 5 Replies
View Related
Feb 1, 2015
I have two tables: products and orders.I would like to query the products only with orders using the product code and/or model number and join up all product and order information for further manipulation.
View 2 Replies
View Related
Sep 3, 2014
I have two tables
first table : customer name,QUANTITY ,delivery date,product type
second table : customer name,QUANTITY ,delivery date,product type
i want to make query to sum the QUANTITY from the two tables and group by customer name and delivery date
View 14 Replies
View Related
Dec 18, 2013
I am using vb.Net to connect to an access db. In access 2010 this query works fine, but in vb.Net, it errors with 'JOIN expression not supported'.
Code:
sql = "SELECT Boxes.Box, Boxes.CustRef, Boxes.Customer " &
"FROM (Requests INNER JOIN [Request Boxes] ON Requests.[Request no] = [Request Boxes].[Request no]) INNER JOIN Boxes ON [Request Boxes].Box = Boxes.Box" &
"WHERE (Requests.[Request no]) = '" & item & "' " &
"AND ((Boxes.Customer) = '" & customer2 & "'))"
View 3 Replies
View Related
Oct 19, 2013
I am trying to combine two peculiar tables in Microsoft Access and have been unable to do so even after doing a lot of brainstorming and searching on the internet.
The two tables are spend and export
Spend
+-------------+--------+-------+-------+
| Country | Metal | Month | Spend |
+-------------+--------+-------+-------+
| China | Iron | Jan | 100 |
| China | Iron | Feb | 200 |
| China | Iron | March | 300 |
[code]...
View 2 Replies
View Related
Nov 10, 2006
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows
Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
View 7 Replies
View Related
Nov 16, 2013
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
View 6 Replies
View Related
Aug 29, 2013
I was working on an update query while joined to another table - and the error I was receiving was the query was not updatable. Er... The table that was being updated sure seemed able to be updated...
Then I wondered if the reason this didn't work was because the other table I was updating from was a query whose records were sum'd and group'd by..I ended up testing the idea by inserting the query's records into a temp table and then did the update to the target table from the temp table... which worked fine.
View 1 Replies
View Related
May 3, 2015
I have two tables with name of accone and the second with the name of acctwo. These two tables are same according to number of columns and also same according to data types and also same according to the column names just the data are difference and also one column (attribute) with the name of ID is same in both tables. Their is a primary key relationship (one-to-one) between these two IDs. I need a query that can combine the data of both of them and can be updated using query. I mean that data of first table and second table must become under one same column not two columns one for first table and second one for second tabel.
View 4 Replies
View Related
Nov 20, 2013
Bit of a strange question / problem. I am using a Dlookup between two queries. Everything runs fine is I leave the join as option 1: only include rows where the joined fields in both tables are equal. But if I change it to number 3 Include ALL records in right table in left it returns a syntax error on the query.
I can run the same query with same relationships of another table and it is fine!
View 3 Replies
View Related
May 7, 2013
How to return data from table1 only if table key matches table2 key.
I tried this but doesnt return me the data i want.
SELECT A.*
FROM tbA AS A INNER JOIN tbB AS B ON A.[ keyfld] = B.[keyfld];
View 9 Replies
View Related
Feb 11, 2014
I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data
Order ID OrderNum Parent order id
1 MISC 2013-10
2 MISC 2013-10A MISC 2013-10
3 MISC 2013-10B MISC 2013-10
Now I am creating a query that shows the lifecycle of the order
MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached
SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum
FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID
WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));
How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..
View 1 Replies
View Related
Apr 2, 2013
I need to do a report that takes data from many different tables (like 10).
To do this I have a big query with many LEFT JOIN to link all the tables together. I have made this query last year and it worked well and efficiently until now.
But I have to add something there, and the problem comes with it :
One of the tables is a currency exchange rate table, with the exchange rate of USD to 50 different other currencies and for each day since Jan 1st 2011.
But in this table there are some dates when a currency does not have an exchange rate (example : it was a holiday in that country, so there is no official exchange rate with this currency on that day).
However, I need to have rates even if they are not present in the table.
I need to get the nearest rate, for example if the rate of EUR on 31st December is not available, I can take the one of the 30th, or 29th or January 1st...
Ideally I would like to do the following :
Add a new LEFT JOIN to my already big query that would look like :
LEFT JOIN CurrencyExchangeRate ON ABS(RateDate - Instrument.Date) < 3
But to take only the closest rate.
I tried with a subquery :
LEFT JOIN (SELECT TOP 1 * FROM CurrencyExchangeRate WHERE ForeignCurrency = Instrument.Currency1 AND ABS(RateDate - Instrument.Date) < 3) AS MRate ON MRate.ForeignCurrency = Instrument.Currency1)
But the subquery does not accept Instrument.Currency1 because it is not part of the table CurrencyExchangeRate
I have a solution that should work, it is to search for this rate in a subquery in the select clause instead of doing a left join. But the tables are quite big (hundreds of thousands of entries) and my work machine is not really 'fast' so it would take hours and hours (when I tried I stopped after 3 hours).
I know there is the solution to make a macro to "fill" the missing rates with the rate of the previous day... But I would prefer not to go to this extent.
View 2 Replies
View Related
Jan 28, 2014
I have a query:
Code:
SELECT purchaseOrder.poId, purchaseOrder.poYear, purchaseOrder.poDate, purchaseOrder.userId, purchaseOrder.currencyId, purchaseOrder.poSendDate, partnerDetails.name
FROM purchaseOrder INNER JOIN (orderDetails INNER JOIN partnerDetails ON orderDetails.partnerDetailsId = partnerDetails.partnerDetailsId) ON Cstr(Format(purchaseOrder.poId,"00000")) = Nz(MID(orderDetails.poNo,6,5),"0");
Simple join query gives dup entries also, i need in single entry.When using Distinct query for the form control source, the record is uneditable, can i get the records without dups.
View 1 Replies
View Related
Jul 20, 2015
I want a query that gives me something like this from 2 different tables:
table1 - AA, AC, DE
table2 - AA01, AA02, AA03, AC01, DE01, DE02
query -
column 1 - column 2
AA - AA01
------ AA02
------ AA03
AC - AC01
DE - DE01
------ DE02
Is that possible?
View 3 Replies
View Related
Apr 9, 2013
I have a table with Part No. and their correlating descriptions. I have a second table with a Product Number and its assosciated Part No. I want to view all of my Part No. with descriptions and IF there is a match to the Part No on the second table, I want to see the associated Product Number. Below is the layout and SQL on my test tables.
Test_Items1 (My Table)
Part No = 1317667223606 Desc1 = ABC123
Part No = 1317667223614 Desc1 = ABC456
Test_Products1 (Customer Data)
Product Number = 14_01 Referenced Item = 1317667223606 1317667223735 1317667224517 1317667225406
SQL (Which isn't working for me)
SELECT Test_Items1.[Part No], Test_Items1.[Item SX Descrip1], Test_Products1.[Product Number]
FROM Test_Items1 LEFT JOIN Test_Products1 ON Test_Items1.[Part No] LIKE "*" & Test_Products1.[Referenced Item] & "*";
The Desired Results from my above scenario would be this
Part No = 1317667223606 Desc1 = ABC123 Product Number = 14_01
Part No = 1317667223614 Desc1 = ABC456 Product Number = Null/Blank
The Part No from Test_Items1 may not always be the first string from the Test_Products1.Referenced Item. It may appear anywhere withing that group of Part Nos. The customer has them separated by spaces.
View 9 Replies
View Related
