Queries :: Joining 3 Queries And Displaying Results In Separate Columns
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.
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.
I have been given the task of organising a mail-merge with a sharepoint list, but the names and emails attached to each object are seperated by a delimiter. Furthermore each person is associated with many objects, and they want the merge programmed to only send 1 email to each person.
So what I need to do is split the data in one column into three separate columns, and then perform a concatenate. The concatenate isn't an issue, but how to split the data into 3 new columns within Access?
If worst comes to worst I'll tell them they will have to use the text-to-columns function in excel first, but would like to try and avoid that where possible.
I have 1 table that I duplicated to make 3 tables total. I did this b/c I am trying to create separate columns from the same field and table. The field is Workorder. Each workorder list the workorder number followed by a dash and then code. I am wanting to put all of the workorders with the same code in it's own column.
I have 5 codes that i am searching for. The first column list the workorder and a code (123456789-AD). The second column (123456789-BC). I'm good to this point but my problem occurs next.
The third column i am trying to put 3 types of workorder and it's code in the same column. As follows, (123456789-CD, 123456789-TC, and 123456789-PTC. However, when I do this it takes the results from the 3rd column and applies it to the 3rd column but also the 1st and 2nd column. I tried a UNION query and unless I am doing it incorrectly it does not work.
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type Candidate short text Unit short text EV1 Date Date EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date TH1 10 07/05/2015 25/05/15 TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Date' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query: SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate FROM [Candidate AC Dates] GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
I'm trying to make a search form that makes use of queries that search a table based on what fields I have. How would I go about having the query display all the fields for the record(s) that match the search criteria? This is for Access 2010.
As far as the query is set up, I have several fields with a search criteria, and the others are for displaying the relevant information about the results (since they're not search criteria, I used "Like '*'"). When I ran the query, it doesn't come up with anything - even if the record actually exists in the table with specified criteria.
I have a simple database with a query that "SUM"s an amount.
I created a form with a sub form that is linked by Account number. I want to be able to open the form in datasheet format and it to display the SUM value from the query.
When I open the form it responds with #Type until I click the + symbol to open the linked query then it updates the #type field with the data from the query.
Im making a database about past bid results. In one table i have a list of bidders and which place they came in (along with some other information). I have 20 positions (1st place, second place and so on...), so i made 20 queries that search through the table for my company's name so we can see how many times we came in first, second, third and so on. Now i would like to bring the number of records in each query to a single form. I tried making another query to using the count feature on the other 20 queries but it keeps returning 0 as the number of results per query. But when i open each query up there are obvisouly more that 0 results in each. Is there a way i can bring up the number of results per query all on one form?
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
I have the following query which returns 2 columns, where 2 fields are summed :-
Code:
SELECT Sum(Stats.[No of Invoices Checked]) AS [SumOfNo of Invoices Checked], Sum(Stats.[No of Incorrect invoices]) AS [SumOfNo of Incorrect invoices] FROM Stats WHERE (((Stats.Period)=[Forms]![frmMain]![cboSingleMonth].[Value]));
Code: Outfits: ID | Top Color ID | Bottom Color ID 1 2 1 2 3 4
Colors: ID | Name 1 Red 2 Blue 3 Green 4 Orange
I'd like to have a query in design view to have the following result
Code: Outfit ID | Top Color Name | Bottom Color Name 1 Blue Red 2 Green Orange
In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name".
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.
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.
I have a sign up form where people select their name for the time slot they want, there are 2 spots open per time slot, so I have Interviewer A and Interviewer B. Records can then either just be a name in A or a name in A and B.
I would like to run a report that shows how many times all the interviewers have signed up in a date range.
I can do this individually by looking up one name at a time with this query:
SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted FROM Interview_Schedule WHERE (((Interview_Schedule.Interviewer_B)=[Forms]![Main]![NavigationSubform]![cboSup])) OR (((Interview_Schedule.Interviewer_A)=[Forms]![Main]![NavigationSubform]![cboSup]) AND ((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]));
My question is how do I get a report to show all agents at once with their own interview count?
I can make 2 separate queries to look up each field, for interviewer A and Interviewer B, then run a join query, but this results in 2 counts for each agent and I can't sum the two totals together....
SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted, Interview_Schedule.Interviewer_A FROM Interview_Schedule
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?
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.
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Code: Function CohortQ(InputDate As Date) As Integer If InputDate = 0 Then CohortQ = 0 Exit Function End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows. If I put the function into the query,
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...
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.
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?
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,
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?
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.
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?
