Queries :: Joining Table Without Making Another Query
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 Replies
ADVERTISEMENT
Jan 21, 2014
I have made a new access 2013 database. I have created a linked table that has imported a substantial amount of data from an external data source, (an Excel spreadsheet). So far no problem. I created a select query that plucked data from the original table mentioned. Again, no problem. Then I decided to create another table, using certain fields only from the select query. Microsoft's guide tells me to start with CREATE, then Table design. I am happy to use just 4 fields from my query, but what I keep ending up with is a table, that, when I double click on it gives me the following:
ID Field1 Field2 Click to add
(New)
It is presumably expecting me to enter an ID number and it will come up with some record, but I want a complete table that should show several hundred records.
View 1 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 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
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
Feb 17, 2006
Let's say that a table has been broken in two and I'd like to rejoin them with a query. Let's say 100 records in table1, 20 records in table 2. Here's a random example, I just made the names up off the top of my head:
PersonTable (100 records)
Person # (unique key)
Name
Address
SpouseTable (20 records)
Person # (lookup wizard to Table1, cascading edit/delete relationship, 1 to 1)
WeightOfSpouse
HeightOfSpouse
Many people aren't "married" and so have no corresponding value in the Spouse table. If I do an inner join on the two tables, on Person#, then it will cut the total # or returned records in the query to 20, to match the total in SpouseTable. Essentially, I want to append the results of SpouseTable to the end of the corresponding rows in PersonTable.
View 1 Replies
View Related
Mar 14, 2006
Hey guys I have a problem on creating the append-query and the new table
I want to join couple different fields from 2 COMPLETELY different table with TOTALLY DIFFERENT fields.
>>What I have right now are the following:
Table 1
Fields: meta_theme, theme, sub_theme, root, count
Table 2
Fields: meta_AG, AG, AG_description
>>What I want at the end:
A new Table 3 that contains the following information AND is a form, which when you open will have :
1.meta_theme: automatically populated with the records from table 1
2.theme: automatically populated with the records from table 2
3.AG: This column is to be filled when someone run the form, and it will be a combo box with value from the records in Table 2-AG field
4. Comments - completely new
>>could you guys help? My brain is totally in disfunction mode... :(
View 1 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
Jun 11, 2013
I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed.
View 2 Replies
View Related
Jul 1, 2013
I'd like to create the chart from the attached table.
But some part numbers don't have quantity in certain month.
So those month won't show up in the report as you can see from attached picture (May is missing).
is there a way to create the query so that it shows 0 for May?
I was going to use crosstab query but it won't work somehow.
View 1 Replies
View Related
Aug 15, 2006
Hi guys,
Does anyone know how to turn a query into a table? There is like 400,000 records in my query so the easy copy and paste option won't work.
Any help would be greatly appreciated,
Aidan.
View 1 Replies
View Related
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
Feb 16, 2005
hi - i have tried search for the solution to my query - but to no avail, so here goes:
I have a query which is based on more than one table. In the query, I have specified a calculation, eg. Final Price: SUM([Sale Price] - [Discount])
From this query I have a form, just showing everything. Details are put into the form, and viola, they appear in the query if checked. However, they do not appear in the table
My assumption why this is not happening was because the Final Price is no longer "record source"d from the original table. How can I combat this so that it does appear in the table?
Thank you (sorry if it is easy - but i dont have a clue!)
View 6 Replies
View Related
Jul 10, 2013
Here i have a situation i have a table where like below
Name | orderno | mail no | contact No |
---------------------------------------
a |123 |5555 | 553453 |
b |321 |8569 | 52353 |
a |123 |2344 | 553453 |
c |143 |567 | 553453 |
d |173 |6787865| 553453 |
But i need a separate table where the entry of all the entries where a is coming only once.
View 2 Replies
View Related
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 9 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
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
Sep 21, 2004
Hi...Well I have 2 table with same characteristic [ Id_no(Autonumber (primary key)), Name(text), Address(text)]...is there a way I can combine this 2 table into one...is there any issue regarding the uniqueness of the primary key..
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 6, 2007
TABLE 1(x)RN|Flat | Init 238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1243238NKCR1246TABLE 2(xx)RN|Eq |Nr|Rpt 21RSFLC1055SHFLDML 238NKCR1243CHSTNRH238NKCR1246CHSTNRHI'm trying to make a query that Joins FLat to Eq and Init to Nr. The reason I am making this join is to COUNT all instances of Init, so it should count 7. Then I want that 7 to correspond to the Rpt CHSTNRH in table 2. So my finalized query should look like...RN Rpt Count238 CHSTNRH 7When I create a Join in a SELECT query it says "Type Mismatch expression". Is there a special query I need to use? Here is my SQL.SELECT x.RN, Count(x.[Init]), xx.[Rpt]FROM x INNER JOIN xx ON (x.[Init] = xx.[Nr]) AND (x.[Flat] = xx.[Eq])GROUP BY x.RN, xx.[Rpt];
View 6 Replies
View Related
Jul 31, 2006
I have a database and i have one field call fdr. that field is full of data but i realized it wood be much more capable to do what i need if i use a joined table with every single fdr. so i created a table with fdr and a id column and set primary key as id. I filled in every single fdr into the table but now I need to replace the original table with the id and then have the fdr # from the new table show up.
Does anyone know how to create the update query and create the proper relationship for what i need to do?
thanks
View 14 Replies
View Related
Sep 12, 2014
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies
View Related
