Queries :: Join Two Tables For Further Manipulation
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.
I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.
I have two tables
Table1 will have data in a column that is 9 characters long ULCABC123 ULCABC124 ULCABC125 PLTABC123 PLTABC124
Table2 will have data in a column that is 6 characters long ULCABC PLTABC
Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1
I was thinking something like this. (but then I can't enter design mode) Query1 - Test
Code: SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6) GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.
Query2
Code: SELECT Left([ORDERDET],6) AS NEWORDDET FROM Table1;
Query3
Code: SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
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 |
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.
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..
I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible. I think that all of the possible permutations are summed-up by the following examples :
123 A text string 2-8 Another text string A-C Another text string here 3-20 And some more text 3A-126B More text Some text without any numbers or letters at the left
What I need to do is :
Move the alphanumeric data at the left to the right. So the data would end up looking like this :
A text string 123 Another text string 2-8 Another text string here A-C And some more text 3-20 More text 3A-126B Some text without any numbers or letters at the left
I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and
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?
Okay - the other database is in the works and is going sloooow (the one that is being created with normalization).
Meanwhile, my other database that has no normalization I am having a problem running a query (now I know why its so important to have good structure).
To give you an idea on how bad this is...:o .....
3 tables - 94 fields to each table - each table has identicle fields, just different data. :eek:
Anyways, I have students in each table (each table is an Annual Report). I want to be able to run a simple query and combine all the students into one.
How would I achieve this?
Thank you!
And for all who are thinking about designing a database - READ READ READ and do some more reading on normalization!!!
HELP!!! :confused: I do have a 5 tables 4 tables do have a foreign key of the main table. I join the 4 tables with the main table but when I am editing the information I cannot edit it. and no error appears. so I am just wondering what happen with my joined tables? please help me!!!!Thank you in advance
How do I join two tables. I have a table and a lookup table. My table has products on there that are listed as custom or basic. I have a look up table that has an ID for basic and Custom. In my table, i want it to read what the id is for each product instead of it reading "basic" or custom. Someone said that I need to join the two tables and do an update query, but I don't understand how to.
That is the question: Whether 'tis nobler in the mind to suffer with VBA Programming and the outrageous errors, Or to take up arms against a sea of Access troubles.
Sorry I was in the mood for Shakespeare.
A quick summary first: In the attached file I have my Relationships. One main table, Workorders with various one-to-many relationships back to their respective tables. If you look at the attachment and see a field with 'wrk' that's my foreign key.
What I'm trying to do is this: Where-ever there is a 'wrk' field I want to add in all the fields (minus the ID Primary Key) into the main Workorders Form.
Then on the form itself I should just be able to enter in the data that is required for the fields.
So here's what I'm thinking for the coding is to Join the various tables to thlet me know if I'm on track or not. I'll start with a small one, because if I can get that right, the rest should be simple.
SELECT Model.ComputerID, Make.ComputerID FROM Computer INNER JOIN wrkComputerID ON Model.ComputerID = Make.ComputerID;
there must be a way to do this, but i haven't been able to figure it out yet...
table one has three fields i need to be concerned with, member_ID, range_start, and range_end. no part of any range spanning range_start through range_end will be duplicated.
table two has several fields i need, but the field of primary concern is a person_ID that will fall somewhere within a range designated in the first table.
how in the world can i join these tables, since none of the IDs in the second table are actually listed in the first, but fall in between values?
i need to get member_ID from table one, person_ID and several other fields from table two.
i have 2 tables, A and B A table StockDate Price 02/04/2001 1.12 04/05/2001 1.15 14/08/2002 1.14 18/09/2003 1.26 and so on 1.48 up to 2005
B Table StockDate Price 02/04/2003 1.12 04/05/2003 1.15 14/08/2003 1.14 18/09/2004 1.26 and so on 1.48 up to 2005
i like to know how to write codes to join 2 tables into 1 table like C Table StockDate Price 02/04/2001 1.12 04/05/2001 1.15 14/08/2002 1.14 18/09/2003 1.26 02/04/2003 1.12 --> B Table 04/05/2003 1.15 14/08/2003 1.14 18/09/2004 1.26 and so on 1.48
Is there anyway to strip all characters except numeric out of a value? For example: 999/777-5555. I want to strip the / and the hyphen. The characters can vary so is there anyway to recognize just numeric?
I have a table that stores text in a number of different languages. Each entry has a unique ID number. Each language version of the same text shares the ID number. I have a column of tick boxes that show where this text is used. ie 1.0 English_text_record_1 tickcol1=y tickcol2=n tickcol3=y 1.0 French_text_record_1 tickcol1=y tickcol2=n tickcol3=y 1.0 Spanish_text_record_1 tickcol1=y tickcol2=n tickcol3=y 2.0 English_text_record_1 tickcol1=y tickcol2=y tickcol3=y 2.0 French_text_record_1 tickcol1=y tickcol2=y tickcol3=y 2.0 Spanish_text_record_1 tickcol1=y tickcol2=y tickcol3=y
I am trying to set the tick boxes so that, if I tick one in an ID range, all the rest tick on as well. ie if I tick record 1.0 English tick_col_2, then the French and spanish will be ticked as well. Any suggestions will be greatly appreciated.
I work for the school dist. and we have 2 tables for 2 different groups. I need to pull data from them both and create a new Query. Example Table 1: Name, address, phone
Table 2: Name, address, phone
(No similarities in ether database, and we'd like to NOT export data)
I'm hoping it's an easy fix that I've overlooked. Michelle