sorry but I.m desperate...
I.m a beginner. The issue is this one (simplified)
I've got 2 tables: table1 with 1 key and a table2 (which is also a subform) with 4 primary keys.
I have established a relationship between STUDYID in table1 and STUDYID in table 2 (which are the same).
Then I would like a query that gives me TAble 1 fields repeated depending on whether Table2 contents have been entered or not, LInked by STUDYID. INSTEAD, when I do a query IT gives me absolutely all values of studyID of table1 and then it repeats the values of table2 again and again.
I would like only those STUDies of table1 which studyID in table2 (subform) have been entered. You understand what I mean? I have just started using this and I.ve already spent hours but cannot find the solution by myself. THANK you very much in advance. I am attaching screenshots
Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).
[Solved] Query between 2 tables
[Solved] Query between 2 tables
Last edited by flamen on Thu Jan 07, 2016 11:23 am, edited 1 time in total.
OpenOffice 4.1.1. Mac OS.X. 10.9.5.
Re: PLEASE HELP. Desperate
I think you want something like
Code: Select all
SELECT * FROM "Main table" WHERE "Main table"."STUDYID" IN (SELECT DISTINCT "studyID" FROM "subform")
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: PLEASE HELP. Desperate
Hey, thanks. I think that I have not explained myself properly, but yes. That code selects only those records for which subforms have been entered which would be studyID=1 and studyid=2
STUDYid=1 has 3 other entries in the subform (table2) and studyID=2 Has a single entry in the subform (table2)
What I want is a table that gives me:
StudyID=1 (all variables *) and then 1 different record for each of the entries in the subform(table2) . This would mean that STUDYID=1 (table1) would be repeated 3 times followed by the table2 fields.
STUDYid=1 has 3 other entries in the subform (table2) and studyID=2 Has a single entry in the subform (table2)
What I want is a table that gives me:
StudyID=1 (all variables *) and then 1 different record for each of the entries in the subform(table2) . This would mean that STUDYID=1 (table1) would be repeated 3 times followed by the table2 fields.
OpenOffice 4.1.1. Mac OS.X. 10.9.5.
Re: Query between 2 tables
HEY!! thanks. I have added a little something and now I.ve got what I wanted.
SELECT "Main_table".*, "Subform".* FROM "Subform", "Main_table" WHERE "Subform"."studyID" = "Main_table"."STUDYID" AND "Main_table"."STUDYID" IN ( SELECT DISTINCT "studyID" FROM "Subform" )
Sorry but I have just started and I learn as I go. Thanks very much
SELECT "Main_table".*, "Subform".* FROM "Subform", "Main_table" WHERE "Subform"."studyID" = "Main_table"."STUDYID" AND "Main_table"."STUDYID" IN ( SELECT DISTINCT "studyID" FROM "Subform" )
Sorry but I have just started and I learn as I go. Thanks very much
OpenOffice 4.1.1. Mac OS.X. 10.9.5.
Re: Query between 2 tables
Try
Code: Select all
SELECT * FROM "Main table" INNER JOIN "subform" ON "subform"."studyID" = "Main table"."STUDYID"
Edit: Oops, I didn't see the last response. |
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Query between 2 tables
HEY FJCC, that one works very well too. Thank you very much for your responses. I really appreciate it.
Best wishes
Best wishes
OpenOffice 4.1.1. Mac OS.X. 10.9.5.