Merge 2 tables with conditions

Hi!

I have 2 tables:

Table Users:
    ID
    Name
    Password
    Birth

Table Status:
    ID
    Status

Both tables are related by "ID" field, but both tables have not the
same number of elements

I want to obtain a new table with this structure:

Table Result:
     ID
     Name
     Password
     Birth
     Status

As I said before both tables have not the same number of elements
(rows) so in my result table, "Status" field should be empty for those
"ID"'s not found in "Status" table.

Example:

Users:  1              2              3
           John         Mary         Peter
           1234         3333         3456
           5/11/81     9/3/80       7/7/69

Staus:  1              3
           CODE1     CODE5

Result: 1              2              3
           John         Mary         Peter
           1234         3333         3456
           5/11/81     9/3/80       7/7/69
           CODE1    (EMPTY)   CODE5

I am working with access, but I have knwoledge of SQL.

Thanks in advance.

0
Vicente
11/6/2007 8:26:48 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
665 Views

Similar Articles

[PageSpeed] 37

1. In the database window go to queries and click on new
2. Add your 2 tables to the query designer
3. Join you 2 ID fields (drag 1 ID field name to the other ID field name)
4. Double click on the join line itself and click in "Include all recods 
from the 'Users" table (this gives you an outer join so that you include all 
the records in users, and only those records in Status where the ID filds 
match)
5. Double click on each filed name required (ID, name, password, birth, 
status) to add the fields to your query
6. On the menu select Query / Make-table query and enter the name of your 
new table when prompted
7. Run your query



"Vicente" wrote:

> Hi!
> 
> I have 2 tables:
> 
> Table Users:
>     ID
>     Name
>     Password
>     Birth
> 
> Table Status:
>     ID
>     Status
> 
> Both tables are related by "ID" field, but both tables have not the
> same number of elements
> 
> I want to obtain a new table with this structure:
> 
> Table Result:
>      ID
>      Name
>      Password
>      Birth
>      Status
> 
> As I said before both tables have not the same number of elements
> (rows) so in my result table, "Status" field should be empty for those
> "ID"'s not found in "Status" table.
> 
> Example:
> 
> Users:  1              2              3
>            John         Mary         Peter
>            1234         3333         3456
>            5/11/81     9/3/80       7/7/69
> 
> Staus:  1              3
>            CODE1     CODE5
> 
> Result: 1              2              3
>            John         Mary         Peter
>            1234         3333         3456
>            5/11/81     9/3/80       7/7/69
>            CODE1    (EMPTY)   CODE5
> 
> I am working with access, but I have knwoledge of SQL.
> 
> Thanks in advance.
> 
> 
0
Utf
11/6/2007 2:59:01 PM
SELECT users.id, users.name, users.password, status.status, users.birth
FROM users LEFT JOIN status
    ON users.id = status.id



In the query designer, you can switch from the SQL view to the graphical 
view to see how your could have done it, graphically.


Hoping it may help,
Vanderghast, Access MVP


"Vicente" <uchan@ono.com> wrote in message 
news:1194337608.699680.106990@d55g2000hsg.googlegroups.com...
> Hi!
>
> I have 2 tables:
>
> Table Users:
>    ID
>    Name
>    Password
>    Birth
>
> Table Status:
>    ID
>    Status
>
> Both tables are related by "ID" field, but both tables have not the
> same number of elements
>
> I want to obtain a new table with this structure:
>
> Table Result:
>     ID
>     Name
>     Password
>     Birth
>     Status
>
> As I said before both tables have not the same number of elements
> (rows) so in my result table, "Status" field should be empty for those
> "ID"'s not found in "Status" table.
>
> Example:
>
> Users:  1              2              3
>           John         Mary         Peter
>           1234         3333         3456
>           5/11/81     9/3/80       7/7/69
>
> Staus:  1              3
>           CODE1     CODE5
>
> Result: 1              2              3
>           John         Mary         Peter
>           1234         3333         3456
>           5/11/81     9/3/80       7/7/69
>           CODE1    (EMPTY)   CODE5
>
> I am working with access, but I have knwoledge of SQL.
>
> Thanks in advance.
> 


0
Michel
11/6/2007 3:29:55 PM
Reply:

Similar Artilces:

NOTEINDX in Batch Header Table
THere is a noteindex field in sy00500, pm00400, pm10100 tables. does any one know what is the purpose of this field? I am getting an error while importing data on these tables. everytime i run the import it says "Null value is not allowed" in this field eventhough i am exporting value for this field. It didnt happen with my other clients. is it ok if we didnt update this field in these tables during integration? Hi Bab The NoteIndex field is used to link to the SY_Record_Notes_MSTR(SY03900) to allow record level notes to be stored against the Batch Header This is no NoteInde...

Table format
Hi When I run the code below I get "External table is not in the expected format" error at line rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText Option Explicit Public Sub SelectFromAccess() Dim rsData As ADODB.Recordset Dim sPath As String Dim sConnect As String Dim sSQL As String 'Clear the destination worksheet Sheets(1).UsedRange.Clear 'Get the database path (same as this workbook) sPath = ThisWorkbook.Path If Right$(sPath, 1) <> "\" Then sPath = sPath & "\" 'Create the connection s...

Viewing IMAP messages #2
I connect to a Linux IMAP server remotely for my emails. When I click on an email in Outlook P once the headers have been updated the system seems to download the email and the attachement. All I want to do is read the message before I download the attachment - Is this possible? Its a bit of a problem if an email has a 3mb attachement and all I want to do is read the mail content before deciding to download the attachment - especially a pain if I am CC into some mail. Regards Andy --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Ve...

Sumproduct #2
Hi all! I am trying to use Sumproduct with dates that come off an SQL databas and are in the format of 02/12/2003 15:30. This is forcing me to use the following formula =SUMPRODUCT((Data!$E$2:$E$3893>G$3)*(Data!$E$2:$E$3893<G$2)*(Data!$C$2:$C$3893=$A4)*(Data!$G$2:$G$3893)) where $G3 is 02/12/2003 00:01 and $G2 is 02/12/2003 23:59 Is there any way I can just look at the date only rather than the time as well, so that anyone can enter a date from, and a date to In anticipatio ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and p...

pasting into merged cells #2
Every time I try to copy and paste data into a merged cell I get error messages about the cells not being the same size. Even if I try to just paste values only. Is there any workaround or fix for this??? Dennis Try pasting into the formula bar. It's a pain, but it works. HTH Carole >-----Original Message----- >Every time I try to copy and paste data into a merged cell I get error >messages about the cells not being the same size. Even if I try to just >paste values only. Is there any workaround or fix for this??? > >Dennis > > >. > See my res...

Work out overtime hours for individuals #2
Hi All I have a question which i am hoping someone will eb able to help me with, i have a spreadsheet which contains infromation for part time hours worked during the course of a month for individuals (e.g. below) Name Start End Hours Persona 1:30 2:30 1 Persona 3:30 4:30 1 Persona 9:30 11:30 person b person b person c person c person d etc etc What i need is a formula that will allo wme to add an extra column which gives me the total hours person a worked, person be worked and so on. The original data contains multiple lines (i.e persona could have 10 lines, personb15 lines etc etc), and...

Conditional Formatting Equation Question
Greetings, I am trying to get a Conditional Format to do something if there is "NO" entry in A2. Any ideas as to what this formula would look like? Any help would be appreciated. TIA -Minitman =A2="NO" or, if by "NO" entry, you mean a blank cell: =A2="" HTH Jason Atlanta, GA >-----Original Message----- >Greetings, > >I am trying to get a Conditional Format to do something if there is >"NO" entry in A2. > >Any ideas as to what this formula would look like? > >Any help would be appreciated. > >TIA >...

3.0 Mail Merge
Is anyone experiencing this issue post upgrade to 3.0.....? When attempting to Print Quote for Customer in Outlook, we select the quote template, then Microsoft Word opens. From here the mail merge functions would begin. However, the mail merge toolbar is grayed out and we cannot merge to new document. We can't do anything. Is the process for Print Quote for Customer different in CRM 3.0? Or is this an issue? If so, can anyone provide assistance on this. Thank you! Cayla Will the templates we had set up to use in CRM 1.2 (for mail merging) still function in 3.0? Or are there ...

Synchronize data across 2 sheets
Hi all Excel 2007 Workbook with a sheet called 2010 around 700 rows/records and a sheet called Previous around 5000 rows records. Both have 31 columns and identical column headers On the 2010 sheet there are records that have changed, how do I get the changed records details onto and overwite the same record on the Previous sheet. Not sure if it would help but each record has a unique reference number. How do i do this ? Hope this makes sense thanks for any help offered Hi If you know which colums are to be changed I would use vlookup function. Vlookup(lookup valu...

MS Office VBA Automation Specialist #2
Hi there, is there such a certificate? MS Office VBA Automation Specialist, I tried googling it on microsoft website and couldn't find any info. I just passed the excel 2k expert exam and I want to take the excel instructor exam, I haven't however been an instructor. is it possible to take the exam without an instructor experience? last but not least, I am thinking about putting together some excel webcasts for beginners for free. I have always liked to learn by audio/visual versus reading so I am thinking about implementing this... here is the link for the first test webcast ...

V4 Mail Merge on Custom entities
Hello We have noticed that new entities added at v4 appear in the mail merge list but that custom entities carried over when upgrading a v3 system do not. Does anyone have any information on how this is controlled and whether there is an option to set an entity as included once it has been created ? All info gratefully received Thanks...Phil That should help: http://blog.sonomapartners.com/2008/01/enabling-mail-m.html Cheers -- George Doubinski CRM Certified Professional - Developer Blog: http://crm.georged.id.au/ "Phil Kedward" <Phil Kedward@discussions.microsoft.com...

Making cards --2 to a 8 1/2 x 11 page
I want to make cards to send out for memorials, etc. I want 2 cards to a page, top folded 5 1/2 x 8 1/2. What happened to the old Picture It! Publisher 2001? Do you want to make these cards in Publisher? If you do, look at page setup, folded card, tent card, width 4.25, height 5.5. Publisher will say it will print two pages per sheet -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "A Holt" <A Holt@discussions.microsoft.com> wrote in message news:F5F75EEF-B186-4960-9AEB-AF1C678492B7@microsoft.com... >I want t...

problem with pivot table
Hello all: I am trying to make a pivot table from my date. My data includes Names, Dates (which I formatted to month, "m" in the format cells option)and Amount due. In designing the pt, I have "Name" in the row field, "Date" in the columnn field and "Amount due" in the Data field. I want my data to be grouped as follows: Month 1 2 3 4 Name $100 $20 $30 $20 Instead I am getting more than 1 month column i.e. Month 1 1 2 2 2 3 Name $80 $20 $30 $20 Do you know why...

Tables/ queries question
I have a table for recording college attendances, some of the courses are at Campus 1 and some are held at Campus 2. I've got a field for course code, course anme and a check box to show if the student is at campus 1. When I enter the student ID number into the form, at the moment the name of the course comes up automatically in the text book, as I think it's reading the information from another database on the system. At the moment I've got a check box on the Form to enter manually into the table whether the student is at Campus 1 or Campus 2. What I wondered is if...

Excel Formulae #2
Hi, I have a spreadsheet with value in one column & a series of dates in 5 other columns.For each row depending on the value in the first colunm a date may be applicable in one or more of the other columns.e.g 550 in the first column will mean dates will be inserted in the next 2 columns similar to authority levels. My qn. is how can I show what's outstanding if the relevant authority levels have not signed off-basically this is used as a tracker of invoices & I would like to show what is outstanding awaiting approval at anytime. Thanks Raj ----------------------------------...

Creating an Excel table from Access
Hi, I have an Access database which I use to log downtime for systems. I have a requirement to produce a monthly report based on this data, however, this needs to be exported to excel in a specific format. Down the left side of the report need to go the names of the services, with the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I need to count the number of times each service was down on a give day, and insert that information into the necessary cell - so if intranet services had been down twice on 16th March, for example, there would be a 2 in column 16, whi...

Payroll w/h tables
When can we expect new payroll withholding tables for Ohio? In December our gov announced a late year change repealing a prior payroll credit previously announced. We have been told that the withholding tables are not correct yet GP/Microsoft has not released new tables yet. When can these be expected since we are practically done with January now??? Thanks. According the information I have, there are no pending changes for OH. You may need to contact Support about this issue. -- Charles Allen, MVP "INC" wrote: > When can we expect new payroll withh...

Download email #2
How to download email from Microsoft exchange 2000 to Outlook Express?? without leave a copy at Exchange server. Outlook Express cannot download mail from Exchange. Why are you trying to do so without leaving a copy of the mail on Exchange? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Edi asked: | How to download email from Microsoft exchange 2000 to | Outlook E...

Cannot insert explicit value for identity column in table x
Sql Server 2005 9.00.4035. We have a hub and spoke topology which includes 6 tables with identity columns. I need to add a new subscription to a new server (same version/sp). I took backup from another subscriber and restored to new server, ran snapshot, push subscription. To make sure a new record would replicate, I added a record at the publisher with the followig text: Insert into tblicimagefolder (imagefolder) values ('dummy') A conflict occurs with the following: This failure can be caused by a constraint violation. Cannot insert explicit value for identity c...

Dynamics 7.0 tables in SQL server 2000
I need to know what should be the content of each of the tables with names starting with "GL" in SQL Server 2000 when using 7.0. Is there a reference that I can go to in Customersource that tells me this information? Thanks. -- Dave Christman System Developer There is an online reference: Tools >> Resource Descriptions >> Tables. Also there is a SDK avaailable on the Great Plains CD's. "Dave Christman" wrote: > I need to know what should be the content of each of the tables with names > starting with "GL" in SQL Server 2000 when...

modify the Type of Field in a Table From another MDB through a Command button
Hello to all! I hope can help me. By technical questions that would be to me very long to explain, the following necessity arises to me: I need To modify the Type of Field in a Table From another MDB through a Command button. The field at issue would happen to be "Number" to "TEXT". Is this possible one? I wait for alternatives. Thanks in advance, and sorry for my poor english. Dreamer. - Hi Is it not possible to open the other database and simply open the table in design view and then change the field. It seems very complex to do the codeing what will most pro...

conditional format #3
Fra: "Niels B�ge Egstrand" <nbenospam@djoef.dk> Emne: Re: Condition Formatting! Dato: 2. november 2004 11:55 is it possibel to opperate with 9 different collors in conditional format ? No, you get up to 3 formats under conditional formatting (plus the normal format). If you need more formats, you could use some event macro. "Niels B�ge Egstrand" wrote: > > Fra: "Niels B�ge Egstrand" <nbenospam@djoef.dk> > Emne: Re: Condition Formatting! > Dato: 2. november 2004 11:55 > > is it possibel to opperate with 9 different collors in...

Field Chooser #2
Guys-is it possible that some of the fields in Outlook 2000 (from Field Chooser) can simply disappear or could the problem be that somebody else is accessing my mailbox? It happened to me that the From field and Subject field have simply disappered twice without my action? Could it be a bug or something similar? Is it possible those fields are already in the table view? I've been spooked by that one before. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstar...

authentication #2
I seem to keep getting a password and ID prompt in outlook for my HTTP Hotmail account even though I am entering the correct password and ID because i can get to this account over the internet. remember my password is also checked in tools\account\properties etc. Version of Outlook? Does it work if you log into the site in a browser? -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Exchange Messaging Outlook ...

MsgBox #2
I have been search the web for hours looking for example spreadsheets using MsgBox to no avail. Can someone direct me in the right direction? What I am looking to do is ask a question and if the answer is "yes" do 1 thing and if "no" do something else. "Ed Davis" <ed.davis1@verizon.net> wrote in message news:4EBF4D2F-7C3F-4BAB-8366-A1C744276744@microsoft.com... >I have been search the web for hours looking for example spreadsheets using >MsgBox to no avail. > Can someone direct me in the right direction? > Hi Look at this: Sub test()...