Joining Queries
Aug 23, 2006
I have two tables. One stores details of all money travelling from A to B, one from B to A.
I have created queries 'qry_A_to_B' and 'qry_B_to_A' to get each set of information.
Since there are User IDs in each table that don't appear in the other, I'm using the following two queries to return all the TO and FROM values. By using both LEFT joins, I appear to be getting all entries.
'A to B data
'------------
SELECT
[qry_A_to_B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth
FROM
[qry_A to B Yearly]
LEFT JOIN [qry_B to A]
ON [qry_A to B Yearly].[ABUserID] = [qry_B to A Yearly].BAUserID
GROUP BY
[qry_A to B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth;
'B to A data
'------------
SELECT
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth
FROM
[qry_B to A Yearly]
LEFT JOIN [qry_A to B]
ON [qry_A to B Yearly].[ABUserID] = [qry_B to A Yearly].BAUserID
GROUP BY
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth;
How can I join these two, to provide a list of ALL information, from all ten columns, regardless of whether or not someone has both a TO and FROM value? i.e.
If they only have a TO entry, I want to see it; the other five columns would be blank.
If they only have a FROM entry I want to see it; the other five columns would be blank.
If they have both, I want them to line up on one line.
However I try to phrase the query, I seem to miss at least some of the information from one or both tables.
View Replies
ADVERTISEMENT
Jul 31, 2013
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
View 6 Replies
View Related
Sep 14, 2004
is it possible to join these two queries together, so that the 2nd query appears at the bottom of the first?
Code:SELECT OEEModelMCTotals.Machine, OEEModelMCTotals.EventCode, OEEModelMCTotals.CodeDescription, OEEModelMCTotals.SumOfTotalTime, OEEModelMCTotals.Occur, OEEModelMCTotals.PlannedTime, [OEEModelMCTotals]![SumOfTotalTime]/[OEEModelMCTotals]![PlannedTime] AS [%Schedule], [OEEModelMCTotals]![SumOfTotalTime]/[OEEModelMCTotals]![Occur] AS Avg, OEEModelMCTotals.Output, OEEModelMCTotals.Throughput, OEEModelMCTotals.ValueAddedimeFROM OEEModelMCTotalsWHERE (((OEEModelMCTotals.EventCode) Not Like "n204" And (OEEModelMCTotals.EventCode)<>"n301" And (OEEModelMCTotals.EventCode)<>"n303" And (OEEModelMCTotals.EventCode)<>"x104" And (OEEModelMCTotals.EventCode)<>"x117"));
and
Code:SELECT OEEModelOtherTotals.EventCode, OEEModelOtherTotals.CodeDescription, OEEModelOtherTotals.Machine, Sum(OEEModelOtherTotals.TotalTime) AS SumOfTotalTime, Sum(OEEModelOtherTotals.Occur) AS SumOfOccurFROM OEEModelOtherTotalsGROUP BY OEEModelOtherTotals.EventCode, OEEModelOtherTotals.CodeDescription, OEEModelOtherTotals.Machine;
i have attached a spreadsheet with the outcome i am after....i dont want it in excel but have used this for my demonstration.
you will see at the bottom of the sheet i have highlighted the info added in blue.
can this be achieved ?
if so how please.
cheers
Andy
View 1 Replies
View Related
Nov 26, 2007
Hi,
My SQL is fairly basic so excuse me if this is something simple.
This isn't my database, I am trying to create a report on someone else's and it's a poor structure (sound familiar :-).
If I have created two queries; one of which selects results on students from one table and the other selects results on students from a completely different table.
This means I have two Hi,
My SQL ability is fairly basic so excuse me if this is something simple.
This isn't my database, I am trying to create a report on someone else's and it's a poor structure (sound familiar :-).
If I have created two queries; one of which selects results on students from one table and the other selects results on students from a completely different table, how do I combine these results into one query so I can run a report on it?
To outline the situation, I have two different queries with identical data types but from two different tables. I need to query these two queries and (possibly) link in another ‘student information’ table so that I can display the results from both queries as if they all came from the same place.
The addition of the extra table in the final query would only be so I can add extra student data into the final report.
Hope this makes sense!
Thanks,
Matt.
different queries with identical data types
View 2 Replies
View Related
Nov 25, 2014
I have a question regarding making a query with joint data that are not identical, as I'm not sure whether it's possible.
I have to look at certain serum levels at te start of a new medication regimen, and serum levels after 5 years.
I have one database with:
Patient number
Start date medication
Start date medication + 5 years
And one database with:Patient number
Date of blood test
Serum level molecule A
Serum level molecule B
What I want to do is make a query where the patient numbers are joined. The problem is that the "start date of the medication" and the "start date + 5 years" are not identical to the dates of the blood tests. E.g.: I have a patient that started medication on 01/March/2006, but he has had blood tests done every 3 months from 28/November/2003 till now, and not on 01/March/2006.
I would need the results of the blood tests (of molecule A and B) at the start of the medication and after 5 years, but if the test date differs by a day, Access already sees they're not identical.
I would like to have access select a blood test date that is as close to the medication start date as possible, within a time frame of e.g. +3 and -3 months. Is this at all possible?
View 4 Replies
View Related
Jul 7, 2013
Any way to join 6 rows into one and calculating average.
So I have temperature data in rows in time intervals of 10 minutes. I would like to join rows in one hour interval, and at the same time calculate average temperature in temperature field.
View 3 Replies
View Related
Apr 30, 2014
I'm new to access. Basically I've put this formula in a field within my query(Query A):
PERIOD: [YEAR] & "-" & [Month Number]
And I want to use this newly created field "Period" to join another table (Table A) without having to create another query.
PERIOD(Query A) -> PERIOD(Table A) = Month Name (Table A)
I need the month Name from Table A but because Period(Query A) is a formula I created, I don't know any way of joining it to Table A without creating another query. Is there another way...
View 5 Replies
View Related
Dec 26, 2014
I have tried to combine data from 3 different tables (unrelated) to make a new one. However, as I understood I can not do this because these 3 tables are unrelated. In my case my 3 tables are for 3 labs' material requirements. I am planning that each lab will fill a form which directly related to there field and direct to me. That is why I intend to have separate tables for each. However, I want to create a table where I will be able to see all the requirements of different Labs altogether. This new table will be kind of Orders.
View 7 Replies
View Related
May 31, 2013
I have a query joining two tables. TableInvoice in the query is Product number and Date purchased. TablePrices contains the same fields and contains prices (quarterly) over the past five years.
Joined by PONumber, I want the query to find the price charged for that date. Right now, if the Product number (TableInvoice) is not listed in TablePrices, it won't show. So, 100 Records might return only 80 if TablePrices does not have all of the Product numbers.
Is there a better way to return all 100 records in TableInvoice and show (Blank) price data if the item is not in TablePrices?
View 1 Replies
View Related
Jun 4, 2014
I have 2 tables: one for repairs and the other for the billing for those repairs. There is a foreign key(record_num) in the billing table to match the primary key(prikey) in the repairs table. This works fine as long as the unit repair has been completed.
Now an employee wants to see records even if they are not completed and wants the rate to be $0.00 if the unit has not been completed. But by this method there is no record in the billing table.
My problem is if I have the 2 tables joined then I only see records that match both tables. Here is my SQL for the query:
SELECT DISTINCTROW tbl_module_repairs.end_user, tbl_module_repairs.pickup_date, tbl_module_repairs.complete_date, IIf([pickup_entity]="Storm","APS Storm","APS Field Tech") AS [Repair Pickup], tbl_module_repairs.mfg_part_num, tbl_module_repairs.manufacturer, tbl_module_repairs.module_type, tbl_module_repairs.incoming_module_sn,
[Code] ....
View 2 Replies
View Related
May 8, 2013
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
View 1 Replies
View Related
Apr 2, 2013
I am trying to use a join query to join two tables in order to create a form. I have done this on two other occasions in my database with no issues. Now it is only pulling the ID and I want it to pull the description. I have looked at the SQL view for the other queries that are similar as well as the design view and all of them are set up the same way with the exception that this new join query is pulling CertID rather than the CertDescription.
Is there any reason why it would be doing that? Is there an error that I could possibly be doing? It seems according to my notes that I have created all the queries the same but this last one does not pull the same info as the other queries.
View 2 Replies
View Related
Jun 29, 2013
I've got a query that uses a key from one table to pull back records from another.
My question is...
On the table with the key there is another field that I want to use to pull back data from the same table from above.
A bit more info...
Table one holds colleagues details
Table two holds records
Within table one there are 2 different fields that hold different ID's for the same colleague, currently I run two different queries to pull all data - I want to know if I can simply run one query that will return every thing?
Schedule an import or export operation
View 11 Replies
View Related
Apr 5, 2013
I have two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.
I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:
SELECT TableA.*
FROM TableA
Union
SELECT TableB.*
FROM TableB;
(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )
View 8 Replies
View Related
Oct 25, 2006
Hi
I have an Access database that contains 2 tables. Both of these tables have the same structure and have the same field names. So:
Table 1 Fields :
Name
Address
Telephone
Fax
Table 2 Fields :
Name
Address
Telephone
Fax
Is there a way to write a query, that will show the results of both tables in one go. (None of the information in table 1 is duplicated in table 2 - I want to show all records from both tables in one new table). So, if both tables had 3 records each, the query would return:
Name - Address - Telephone - Fax
jon-uk-12345-54321 (from Table 1)
julie-uk-21451-41541 (from Table 1)
paul-ir-98545-11241 (from Table 1)
pat-uk-99585-63362 (from Table 2)
phil-uk-99985-99631 (from Table 2)
ted-uk-44444-55555 (from Table 2)
These 2 tables need to be kept separate, so I can't copy and paste the records from Table 1 into the bottom of table 2.
The field names are the same in both tables.
Can this be done?
Many Thanks
View 1 Replies
View Related
Jan 2, 2007
Hi All!
I have two tables. Table Heffalump which has field "DealGroup" and other fields as well... and table 8-YTD Principal Investments whic has field "book" and other fields as well... I need to use BOOK and DEAL Group as a combination primary key to compare against table 8-YTD Principal Investments. If there is a new DEAL GROUP then append into table "8 - YTD Principal Investments"
THANKS IN ADVANCE,
B
View 2 Replies
View Related
Mar 31, 2008
Hi Guys,
I have a problem which I cannot figure out.
I have a table which has an ID field and also a field with data. The ID field is 'not' unique.
I need to join all of the data fields which have the same ID field into one field. So it would be ID; Data 1, Data 2...
Any ideas? I would appreciate any help you could offer!
View 7 Replies
View Related
Aug 23, 2005
Seems like a simple question but I am stuck.
I need to join two strings from different records in a table
table eg
ID Desc
1 blah blah
1 more blah
2 blahring
2 blah
3 and last one
I want to join all the "Desc" strings that have the same ID to get
1 blah blah more blah
2 blahring blah
3 and last one
Can anyone help?? :o
Thanks
Paul
View 1 Replies
View Related
Oct 4, 2005
I'm trying to create a Purchase Order Database that requires a monthly summary of all the purchase orders in that month.
Examples of the fields i'm using are: Date, Vendor, Description, Item Number, Unit Price, Quantity, Sub-total, Total, etc.
Each table is a single purchase order, and there are around 3 or so every month. What I am trying to do is create a query in which all related fields are joined, and can be viewed as a single field.
For example:
Field 1 is DATE for Purchase Order (PO) 1, 2, and 3.
What I want to do is join all entries in DATE from PO1, PO2, and PO3 in a single query.
I want it to look like:
[DATE]
PO1......
PO2......
PO3......
I've tried several approaches to this problem, but they all have the same end result. It creates a seperate field for each table:
[PO1-DATE][PO2-DATE][PO3-DATE]
Any suggestions?
View 2 Replies
View Related
Feb 16, 2007
I am building a simple task tracker to keep a record of tasks being passed back and forth between our team. The schema is fairly simple as illustrated in the image below.http://img2.freeimagehosting.net/uploads/f4f3a05c21.gifI want a recordset as follows out of this database (+/- few columns. But these are the most essential ones needed) -ixProject | Project.sTitle | ixTask | Task.sTitle | TaskHistory.dtEntry | sStatus | sEmployeeAssignor | sEmployeeAssigneeThis is basically a recordset of the history of the task (who assigned task to whom) and the status (assigned, resolved, closed). I can get the query to return the user id (ixEmployee) without any problems. But to display this in the interface I will need the employees name (sUsername).I tried the following query -SELECT Employee.sUsername AS sEmployeeAssignor, Employee.sUsername AS sEmployeeAssignee, Project.sTitle AS sProjectTitle, Task.sTitle, TaskHistory.dtEntry, TaskHistory.sDescription FROM(((Project INNER JOIN Task ON Project.ixProject = Task.ixProject) INNER JOIN (Employee INNER JOIN TaskHistory ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignor)ON Task.ixTask = TaskHistory.ixTask)INNER JOIN Employee ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignee <<< THIS IS THE PROBLEM LINE)WHERE (SELECT LAST(TaskHistory.ixEmployeeAssignee) FROM TaskHistory) = 1ORDER BY TaskHistory.ixTask, TaskHistory.ixTaskHistoryThis is the error I get -Join expression not supported.State:S1000,Native:-3530,Origin:[Microsoft][ODBC Microsoft Access Driver]Can someone explain how I can solve this problem? I am currently running two queries - one to retrieve the usernames, and the other to retrieve the history with the user id's - and replacing the user id's with usernames when displaying the records, but would like to have a cleaner solution.
View 2 Replies
View Related
May 30, 2007
This gets even more confusing as I go.
Two tables again needing to be joined. But only need to fill the data in the first line.... yes, Pain in the ARSE> here's a sample.
PAYLINES TABLE
EMPLOYEE --------- DATE -------- Paytype
121614000 -------- 12/31/2007 --- 300
121614000 -------- 12/31/2007 --- 122
121614000 -------- 12/31/2007 --- 100
121614000 -------- 12/23/2007 --- 100
TAXTABLE
EMPLOYEE --------- DATE -------- CPP ---- EI
121614000 -------- 12/31/2007 --- 1.23 --- 2.35
121614000 -------- 12/23/2007 --- 2.32 --- 1.35
The end Query I need is
EMPLOYEE --------- DATE -------- Paytype --- CPP ---- EI
121614000 -------- 12/31/2007 --- 300 ------- 1.23 ---- 2.35
121614000 -------- 12/31/2007 --- 122 ------- NULL ---- NULL
121614000 -------- 12/31/2007 --- 100 ------- NULL ---- NULL
121614000 -------- 12/23/2007 --- 100 ------- 2.32 ---- 1.35
The TAXTABLE only needs to assign it's data to the FIRST row of the resulting query
View 3 Replies
View Related
Jan 30, 2008
Hello! I need to join 2 tables, 1 table with all of the fields, and another with just 2.
The link between the 2 tables is an ID field in field 2 wich is long integer.
The problem is, the ID field in table 1 that correlates to the ID field in table 2, is in the datatype text.
I cant change the datatype of the field in table 1 because it is a linked table to a txt file. And some of the values in there are dashes, indicating no ID. (not sure why).
So, is there ANY way to link the2 tables with the 2 different datatypes
Thanks in advance!
View 5 Replies
View Related
Feb 17, 2005
I have two tables in my database. one is linked to a Client.txt and the other is tblNewClient.
the idea is that this database is a portable version of the actual database, meaning that it will be operating on its own without a connection to the actual back end.
Now the problem is when a new customer is added, i want it to add itself to the tblNewClient.
but when i go into my Find CUSTOMERS FORM i need to be able to search threw both tables.
i want to creat somthing like a union query but in vba.
I figure i need to have 2 recordsets in vba. one for each table and then i need to have a 3rd recordset wich will hold all the info.
im not sure how to copy a whole recordset and set to another table.
Im thinking of somthing like
set RsAll = RsClient & RsNewClient
This obviously does not work but it is what i want to accomplish ,and i want the Rsall to be an actual table and not a public Recordset
View 3 Replies
View Related
Feb 7, 2007
I hope the title isn't misleading. I just need some advise. I'm trying to bend my mind around how to accomplish this.
I'm putting together a database for a trucking company. The employees are paid according to the loads they haul. I have a setup right now where the manager can put in all the information (including driver, $$ rate, date, etc), and the invoice will be calculated and created in a report. Further, it also creates paystubs for each driver. This is where I am having trouble. You see, I can use a query and detail section in a report to add up the amount earned for each driver from each load. No problem there. My problem is that deductions are made to these paychecks for various things which change every week. I haven't quite figured out yet what the best solution would be for inputting this deduction information and then joining it to the payment information.
I hope this is somewhat clear what I am doing. If not, please let me know and I'll clarify. I can try to strip a database, but it still might be too big.
Thanks in advance for any help.
-opiv6ix
PS - If anyone has an eloquent way of choosing a pay period, I'd love to know about it. Right now I'm building the report off of undefined fields [Startdate] and [Enddate] in the query, prompting the user when s/he opens the report.
View 5 Replies
View Related
Dec 15, 2005
Hi whats the syntax for left joining more than 2 tables if each of the tables has a common column???
View 4 Replies
View Related
Feb 10, 2006
I need to do a join on two tables, one is in the current database, but the onther one is an another database. I am struggling with the syntax. Can anyone advise where I am going wrong?
This is the code, but it says the usual 'Syntax error in FROM clause'
SELECT za06_report_2006.wbs, za06_report_2006.cost_cntr, wbslist.description
FROM za06_report_2006 LEFT JOIN wbslist in '\ukdewgs002gdatapcdatafinancedocumentwbslis t.mdb' on za06_report_2006.wbs= wbslist.[wbs element];
All table names , variable names, and paths are correct.
Thanks for any suggestions
(I am very new to SQL)
View 1 Replies
View Related