How do I combine 2 tables?

I have 2 tables in a database. Each table has 1 data field, "Slot", in common 
with the other.

How do I create a new table or query that combines data in one table with 
data in the other table based on the slot number? In other words I have the 
following information:

Table 1      Table 2
A 1 2 3 4    A 4 7 2 9
B 3 5 1 3    B 4 9 1 2
C 0 7 8 3    C 5 1 4 2

Where the capital letter is the data for the slot field, the desired result 
is a new table or query that looks like this:

Table 3
A 1 2 3 4 4 7 2 9
B 3 5 1 3 4 9 1 2
C 0 7 8 3 5 1 4 2

I am new to this so please be detailed. Thanks
0
Utf
2/1/2010 4:57:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
761 Views

Similar Articles

[PageSpeed] 20

Create a new query in design view.

Add Table1.

Add Table2.

Drag "Slot" from Table1 to Table2.  This "joins" them.

Select the fields from Table1 you wish to display.

Select the fields from Table2 you wish to display.

Run the query.

(if you have questions about how to use a query, check Access HELP.)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"sabow71" <sabow71@discussions.microsoft.com> wrote in message 
news:1A170F83-C125-4100-9F6D-EC68592620DA@microsoft.com...
>I have 2 tables in a database. Each table has 1 data field, "Slot", in 
>common
> with the other.
>
> How do I create a new table or query that combines data in one table with
> data in the other table based on the slot number? In other words I have 
> the
> following information:
>
> Table 1      Table 2
> A 1 2 3 4    A 4 7 2 9
> B 3 5 1 3    B 4 9 1 2
> C 0 7 8 3    C 5 1 4 2
>
> Where the capital letter is the data for the slot field, the desired 
> result
> is a new table or query that looks like this:
>
> Table 3
> A 1 2 3 4 4 7 2 9
> B 3 5 1 3 4 9 1 2
> C 0 7 8 3 5 1 4 2
>
> I am new to this so please be detailed. Thanks 


0
Jeff
2/1/2010 5:04:37 PM
The actual table and field names would help us to help you. 

Also are the numbers all in one field or each in their own field?

Can there be more than one record with the same slot in each table? In other 
words, could 'table1' have more than one record with slot A?
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"sabow71" wrote:

> I have 2 tables in a database. Each table has 1 data field, "Slot", in common 
> with the other.
> 
> How do I create a new table or query that combines data in one table with 
> data in the other table based on the slot number? In other words I have the 
> following information:
> 
> Table 1      Table 2
> A 1 2 3 4    A 4 7 2 9
> B 3 5 1 3    B 4 9 1 2
> C 0 7 8 3    C 5 1 4 2
> 
> Where the capital letter is the data for the slot field, the desired result 
> is a new table or query that looks like this:
> 
> Table 3
> A 1 2 3 4 4 7 2 9
> B 3 5 1 3 4 9 1 2
> C 0 7 8 3 5 1 4 2
> 
> I am new to this so please be detailed. Thanks
0
Utf
2/1/2010 5:16:01 PM
Table 1 is called Positions. Table 2 is called Personnel. The slot field is a 
unique identifier in each table. Each character in the sample tables I gave 
in my opening post represents a data field. So I want to create a new table 
that takes record slot A in Table 1 and combines it with record slot A in 
Table 2 (see Table 3).

"Jerry Whittle" wrote:

> The actual table and field names would help us to help you. 
> 
> Also are the numbers all in one field or each in their own field?
> 
> Can there be more than one record with the same slot in each table? In other 
> words, could 'table1' have more than one record with slot A?
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "sabow71" wrote:
> 
> > I have 2 tables in a database. Each table has 1 data field, "Slot", in common 
> > with the other.
> > 
> > How do I create a new table or query that combines data in one table with 
> > data in the other table based on the slot number? In other words I have the 
> > following information:
> > 
> > Table 1      Table 2
> > A 1 2 3 4    A 4 7 2 9
> > B 3 5 1 3    B 4 9 1 2
> > C 0 7 8 3    C 5 1 4 2
> > 
> > Where the capital letter is the data for the slot field, the desired result 
> > is a new table or query that looks like this:
> > 
> > Table 3
> > A 1 2 3 4 4 7 2 9
> > B 3 5 1 3 4 9 1 2
> > C 0 7 8 3 5 1 4 2
> > 
> > I am new to this so please be detailed. Thanks
0
Utf
2/1/2010 5:31:01 PM
Reply:

Similar Artilces:

Address Book Problem #2
Outlook version: 2003 When in a Distribution List, I click on [Select Members] and get this MS Office Outlook message: The list could not be displayed. The Contacts Folder associated with this address could not be opened; It may have been moved or delected, or you do not have permissions. For information on how to remove this folder from the Outlook Address Book, see Outlook Office Outlook Help. [OK] This is a recent problem. Advice? -- Larry Bohen www.audiobooksonline.com Sounds like you have an invalid reference to a Contacts Folder. reset those references here: Tools > E-mail a...

Upgrade Path sbs2000/sbs2003r2 and CRM1.2/3.0
We currently have CRM 1.2 installed on a sbs2000 server. We are looking to upgrade this server to sbs2003r2 and we also want to upgrade CRM from 1.2 to 3.0. Can someone recommend the best upgrade path? This is partial book smarts and partial experience... Book smarts: Upgrade to CRM 3.0 on the SBS2000 box (assuming SQL 2000) Experience: Redeploy to SBS2k3R2 box using CRM 3.0 redeployment tool Upgrade to Rollup 1 My experience was to take it from a 3.0 to 3.0 on R2, so I had a SQL 2000 -> SQL 2005 on a new domain. I recommend that you backup and follow the directions to a T. The up...

outlook 2003 has 2 "send" labels in the send receive drop down
hi, sometimes, i have to reboot my machine because the send recieve button in outlook does not work, when i go to the drop down menu, i dont see any of my "accounts" listed, and I see the word "send", then the next line is duplicated and also says 'send". the only way to get back to "normal" is to reboot the whole system until it happens again. This usually happens once per day. Close Outlook. Do a search for the outcmd.dat file (this is a hidden file so you'll have to either tell Windows Explorer to unhide it or search hidden files and folder...

Email forwarding #2
Is there anyway to get your outlook emails forwarded automatically???? Hi! Microsoft Outlook, not Express? 1). Tools\Rules and Alerts... 2). Our Redirect add-in (www.mapilab.com), it redirect email and new recipient will receive the full copy of message, including internet headers and the same sender as in original email. > -----Original Message----- > From: Jason [mailto:Jason@nts.uk.net] > Posted At: Wednesday, June 30, 2004 3:46 PM > Posted To: microsoft.public.outlook > Conversation: Email forwarding > Subject: Email forwarding > > Is there anyway to get your ...

calendar #2
is there a formula for a continues monthly calendar? Hi tg not exactly sure what you're after here but if you enter 01/01/05 in a cell and drag down (autofill handle, little back cross bottom right of cell) it will increment by a day for each row. If you'ld like more assistance please feel free to post back with additional details. Cheers JulieD "tg" <anonymous@discussions.microsoft.com> wrote in message news:429d01c4afae$b110b910$a601280a@phx.gbl... > is there a formula for a continues monthly calendar? ...

Setting up Outlook 2003 #2
I am in the process of setting up Outlook 2003 on my new computer. I believe I may have click the wrong answer and have put in incorrect information for outlook to start. I keep getting a box that ask for Username and passcode. When I put in the information I think it should be, I receive this message: Unable to open your default e-mail. The file C:\Documents and Setting\Owner\Local Setting\Application Data\Microsoft\Outlook\outlook.ost is not an offline folder file. Please tell me what I should do. When I click OK. Outlook closes. Thanks. -- Trying to keep up with technology G...

Linked Table Manager in ACCESS
Hi, I am trying to change a field in an ACCESS table and get an error message that says the table is a linked table and fields can't be changed. After googling for some answers, I think I should be able to find out the link using "Linked Table Manager" in ACCESS. However, the "Linked Table Manager" button is grayed out. Any ideas/suggestions are welcome. Thanks. Richard Open the table in Design View. Reduce the window so that you can see the window's top bar. Right click in the top bar of the window (usually blue in color) and select ...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

Set Cell Padding on word tables with merged cells
Dear Experts: Below macro sets certain padding values for the selected table. Regrettably it fails to work on tables with merged cells. Have you got any idea how to re-write the code so that tables featuring merged cells also get worked on? Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub SetPadding Dim myCell As Cell Dim myRow As row Dim myTable As Table Set myTable = Selection.Tables(1) For Each myRow In myTable.rows For Each myCell In myRow.Cells myCell.TopPadding = CentimetersToPoints(0) myCell.BottomPadding = C...

RSS feed #2
I do not want Outlook as an RSS client. Every time I click on a feed, Outlook grabs it! How can I stop this so I can use live bookmarks etc? THANKS Robin "a novice" Hello RC Crabbe, I can do it for you. Please reply if you want it. Regards Andrej > I do not want Outlook as an RSS client. Every time I click on a feed, > Outlook grabs it! How can I stop this so I can use live bookmarks etc? > > > THANKS > Robin "a novice" > ...

sum by coding without 0 #2
hi in the following program if there is no value in the cells a1 and b1 it returns 0 Public Sub Add2Cells() Range("C1").Value = Range("A1").Value + Range("B1").Value End Sub i need only the output if the both the cells have the data will you can tell me that Public Sub Add2Cells() If Range("A1").Value <> "" And .Range("B1").Value <> "" Then Range("C1").Value = Range("A1").Value + Range("B1").Value End If End Sub -- HTH Bob (there's no email, no ...

help !!! lost floppy 2 / 2 of my back up file
I still have floppy number 1 and it's fine, Is there a way I can recover at least the data on that floppy? On doing the restore, after finishing floppy 1, asks for floppy 2, but i don't have any and it aborts the restore process. Any idea, what can i do? In microsoft.public.money, Fife wrote: >I still have floppy number 1 and it's fine, >Is there a way I can recover at least the data on that floppy? No. > >On doing the restore, after finishing floppy 1, asks for floppy 2, but i >don't have any and it aborts the restore process. > >Any idea, what...

Automatic settling of EDC Batch #2
Is there a way to stelle an EDC batch automatically in RMS, or an add-on that accomplishes this? This would be very handy. Thank you in advance. ...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Notification if no external e-mail recieved for over 2 hours.
Recently the SMTP authentication settings were changed so that no external e-mails could be recieved. There was no way to see that this was the case until someone complained. Is there any way to monitor externally recieved e-mails so that if none are recieved after a certain amount of time for a notification to be generated? In this case a script using telnet could be used to check if e-mails could be recieved externally, but I was looking more for something like if no external e-mails recieved by any users for two hours then create notification. I can't seem to think of any way to che...

how to use temporary tables in dex
Hi Somebody has one example of how to use temporary tables in dex Cesar Hi Cesar. Define temp tables just as you would define a SQL table in Dex - but with physical name as "temp" without the quotation. I recommend using database type ctree for performance as it would just create a temp file in your directory. From there on, just use the temp table as you would with normal dex table but remember that once you are done with your routine, your temp table will be deleted automatically. Use temp table as a temporary repository for your calculations, reports, etc. ---Darryl Baj...

BillPay #2
When will Money start using the the free MyCheckFree.com? This a free service now. What gives. Boo Microsoft. I may need to switch. Yikes! So why am I socked with a $5.95 monthly fee? Just because I use it within the Money program? I don't use my bank so not sure why I get the fee. Thanks! "Bogee" <Bogee@discussions.microsoft.com> wrote in message news:C06B9868-442D-4841-98D3-0C85524F86ED@microsoft.com... > When will Money start using the the free MyCheckFree.com? This a free > service > now. What gives. > > Boo Microsoft. I may need to switch. ...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Make Table Query
How do you add (in run-time) primary key and indexing to a table (during creation or after creation) when it has been created through a make-table query? Either from a designed query or VBA SQL statement. ...

forecast function help #2
Just to clarify that formula, am i right to say this? =ROUND(FORECAST($E$1,B2:D2,$B$1:$D$1),0) $E$1 - is selecting the forcast for year 2004 B2:D2 - is selecting all the data bar the years in the top row $B$1:$D$1 - is being used to predict the outcome for 2004 Is that right? Thanks again -- metho ----------------------------------------------------------------------- method's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1503 View this thread: http://www.excelforum.com/showthread.php?threadid=26644 Hi B1:D1 are the x values there B2:D2 the associated y ...

convert #2
I have my checks on version 6 of quicken. It's backup file is qdb. It wouldn't run in this new vista machine so I bought the 2009 version. It says in black and white in a hard to find instruction that it has changed format and will not import my old files, it uses qif. I returned it and got my refund check today. How about that? My question. If ms money will import my old files, I want to buy it. If it will not, I don't want it. I have been looking and can not find anywhere it says one or the other. Glenn In microsoft.public.money, Glenn wrote: >I have my checks on ...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

RANDOM WEIGHT (PRICE EMBEDDED) EAN/UPC BARCODES IN STORE VER 2.0
HELLO , I CREATED THE RANDOM WEIGHT CODE STARTING WITH (201000100000) THE BREAK UP OF THE CODE IS 20 = PREFIX 10001=ITEM LOOKUPCODE 00000=WEIGHING PRICE AFTER SETTING THE EAN13 CODE FOR THE WEIGHING ITEM I TRIED TO SCAN THE BARCODE WHICH IS CREATED IN WEIGHING SCALE . BUT THE POS IS NOT PICKING UP THIS CODE PLEASE ADVICE THE SOLUTION FOR THIS 1. make sure you've turn the utilize EAN/UPC option on. 2. HHIIIIIPPPDDC HH - header identifier, must be started w/ 02 or 20 IIIII - item lookup code (SKU in the scale) PPP - price to the left decimal DD - price to the right decimal C - che...

Time Formula #2
Hi I run Excel 2K I download data from a mainframe. This data has a date & time format in it (dd/mm/yyyy hh:mm:ss) I work in a place that has a 3 shift cycle - day shift, afternoon shift, night shift. Day shift starts at 7:20am and ends at 15:29pm Afternoon shift starts at 15:30pm and ends at 23:19pm Night shift starts at 23:20pm amd ends at 7:19am I need a formula that looks at the cell with the date/time in it and displays the word DAY (for the time frame of day shift), AFTERNOON (for the timeframe of afternoon shift) and NIGHT (for the timeframe of night shift) In my previous ...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...