9 Replies - 1677 Views - Last Post: 31 August 2015 - 10:15 AM

#1 ricososwavay   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 113
  • Joined: 11-February 14

Check the values in two rows from two tables on one row

Posted 26 August 2015 - 06:25 PM

This may be a little hard to understand , but I am new to access. I am trying to get the values from two fields in one table then trying to match those values against another field in another table and create a new table with a result.I do not want a primary key on my second table.

For example

Table1:

ID          FIELD1          FIELD2          FIELD3            
1           111111            1             10/2/2012
2           111111            2             10/2/2012
3           111111            3             10/26/2012
4           111111            4             10/26/2012  


Table2:

           FIELD1          FIELD2          
           1                 2             
           1                 8            
           1                 7             
           1                 6              
         



Now I want to check Field2 from Table1 and see if it matches with either Field1 or Field2 from Table2 and if Field2 from Table1 matches Field2 from Table2 with the same date as Field3 from Table1

So the desired result for Table 3 Should like this:
ID          FIELD1          FIELD2          FIELD3       FIELD4     
1           111111            1             10/2/2012       2



I have tried this sql result which gives me back the data I want but I dont know how to get it into another table within the same query.
SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 INNER JOIN Table2 
ON Table1.Field2 = Table2.Field1
OR Table1.Field2 = Table2.Field2
GROUP BY Table1.Field1, Table1.Field2, Table1.Field3;



Is This A Good Question/Topic? 0
  • +

Replies To: Check the values in two rows from two tables on one row

#2 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Check the values in two rows from two tables on one row

Posted 26 August 2015 - 06:39 PM

You have you options either using an INSERT INTO SELECT FROM or a SELECT INTO FROM. If you are inserting into another physical table, or a temporary table that has already been created then you would use INSERT INTO SELECT FROM. If you want to put it into a temp table that has not already been defined then you use SELECT INTO FROM.

Here are some syntax examples:

INSERT INTO <Table> (<Columns>)
SELECT <Values>
FROM <Table1>
INNER JOIN <Table2>
    ON <Criteria>



SELECT <Values>
INTO #TempTable
FROM <Table1>
INNER JOIN <Table2>
    ON <Criteria>


Was This Post Helpful? 0
  • +
  • -

#3 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3040
  • View blog
  • Posts: 11,683
  • Joined: 03-December 12

Re: Check the values in two rows from two tables on one row

Posted 26 August 2015 - 06:51 PM

Any reason why you are creating a new table with the data?
Was This Post Helpful? 0
  • +
  • -

#4 ricososwavay   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 113
  • Joined: 11-February 14

Re: Check the values in two rows from two tables on one row

Posted 26 August 2015 - 06:56 PM

I have updated my code to
SELECT Table1.Field1, Table1.Field2, Table1.Field3 INTO Table3
FROM Table1 INNER JOIN Table2 
ON Table1.Field2 = Table2.Field1
OR Table1.Field2 = Table2.Field2 
GROUP BY Table1.Field1, Table1.Field2, Table1.Field3;


//But I am still getting this result

ID          FIELD1          FIELD2          FIELD3          
1           111111            1             10/2/2012
1           111111            2             10/2/2012

//I Want this Result

ID          FIELD1          FIELD2          FIELD3          FIELD4
1           111111            1             10/2/2012          2



View Postastonecipher, on 27 August 2015 - 01:51 AM, said:

Any reason why you are creating a new table with the data?

I dont need to but Im not sure how to update the fourth field where it meets my criteria
Was This Post Helpful? 0
  • +
  • -

#5 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3040
  • View blog
  • Posts: 11,683
  • Joined: 03-December 12

Re: Check the values in two rows from two tables on one row

Posted 26 August 2015 - 07:05 PM

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field2 as FIELD4 INTO Table3
FROM Table1 INNER JOIN Table2 
ON Table1.Field2 = Table2.Field1
OR Table1.Field2 = Table2.Field2 
GROUP BY Table1.Field1, Table1.Field2, Table1.Field3;

This post has been edited by astonecipher: 26 August 2015 - 07:05 PM

Was This Post Helpful? 0
  • +
  • -

#6 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Check the values in two rows from two tables on one row

Posted 26 August 2015 - 07:33 PM

so what exactly is field 4? is it a count of the number of records that match the given criteria? if so then you can use the COUNT() function to get the results you want.

This post has been edited by rgfirefly24: 26 August 2015 - 07:34 PM

Was This Post Helpful? 0
  • +
  • -

#7 ricososwavay   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 113
  • Joined: 11-February 14

Re: Check the values in two rows from two tables on one row

Posted 26 August 2015 - 07:52 PM

No field4 is not a count basically table2 has 2 codes that are stored in field1 and field2 and if table one has both codes on same date I want it to provide the opposite code that is in table2 for the code displayed in Table1
Was This Post Helpful? 0
  • +
  • -

#8 mojo666   User is offline

  • D.I.C Addict
  • member icon

Reputation: 409
  • View blog
  • Posts: 883
  • Joined: 27-June 09

Re: Check the values in two rows from two tables on one row

Posted 27 August 2015 - 04:00 PM

If I understand what you are looking for exactly, then I think you will need 2 joins. Basically, TABLE1 has all of your data but you are trying to pivot pairs of records to be in 1 row. This is easily accomplished by joining TABLE1 to itself by FIELD1 and FIELD3.

The result set of this join would have the following data available

FIELD1	FIELD3	a.FIELD2	b.FIELD2
111111	10/2/2012	1	1
111111	10/2/2012	1	2
111111	10/2/2012	2	1
111111	10/2/2012	2	2
111111	10/26/2012	3	3
111111	10/26/2012	3	4
111111	10/26/2012	4	3
111111	10/26/2012	4	4



Then you need to join this result set to TABLE2 to filter the records.


SELECT t1a.FIELD1, t2.FIELD1, t1a.FIELD3, t2.FIELD2
FROM TABLE1 t1a
INNER JOIN TABLE1 t1b
t1a.FIELD1=t1b.FIELD1 AND t1a.FIELD3=t1b.FIELD3
INNER JOIN TABLE2 t2
ON t2.FIELD1=t1a.FIELD2 AND t2.FIELD2=t1b.FIELD2
Was This Post Helpful? 0
  • +
  • -

#9 ricososwavay   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 113
  • Joined: 11-February 14

Re: Check the values in two rows from two tables on one row

Posted 27 August 2015 - 06:09 PM

I get a syntax error in from clause is the possible in access, I know it is in sql server but im new to access
Was This Post Helpful? 0
  • +
  • -

#10 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Check the values in two rows from two tables on one row

Posted 31 August 2015 - 10:15 AM

We can't help you if we don't know what code your trying to run. Post your updated query as well as the exact error message you are seeing.

Also, are you working on Microsoft Sql Server, or Access?

This post has been edited by rgfirefly24: 31 August 2015 - 10:16 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1