January 28, 2016 at 4:28 am
Hi,
Im going to try and explain this as best as I can. 2 Tables both of which have the same Field Names - Table1.ExampleA Table1.ExampleB Table2.ExampleA Table2.ExampleB
Table 1 links to table 2 using the ExampleB field and for those entries Table2.ExampleA is NULL
what I am trying to do is create a query which displays all results For Table1 which are value Table1.ExampleA AND Table2.ExampleB
Also instead of outputting NULL for Table2.ExampleA can I populate its relating field which is Table1.ExampleA?
Thanks
January 28, 2016 at 4:41 am
COALESCE(Table2.ExampleA,Table1.ExampleA)
John
January 28, 2016 at 4:55 am
Hi John thanks for the reply. So Im guessing COALESCE allows you to define a value for 2 Fields which are the same or have I completely missed the point? I put my question in the newbies section so please go easy on me 🙂
January 28, 2016 at 5:13 am
No, COALESCE returns the first non-null value from the list of arguments (or NULL if all the arguments are null). If you post some table DDL and a few rows of sample data (INSERT statements), I can show you what I mean.
John
January 28, 2016 at 5:17 am
Ah ok I think I understand, the picture below shows Table 1 with Null values but as you can see they are the values in Table 2 - this is what I'm trying to output to a single results table (if possible)
Attached is screen shot.
Thanks
January 28, 2016 at 2:05 pm
I think I know what you are after. But to prevent me guessing and you possibly using a solution that works fine on your test data only, I will not post it until you give us more information.
You will need to post:
1. The structure of the tables involved, **as CREATE TABLE statements**. Much clearer than fuzzy descriptions or pictures.
2. Some illustrative rows of sample data **as INSERT statements**. Again, much clearer. Plus we can copy 1 and 2, paste it in SSMS, and run it to set up our own test bed.
3. Expected results. Based on the sample data you posted. So that we can verify that the results of our tests.
Or if you want to learn to do this yourself (relevant if you think you ever need to do this again in the future), read up on joining tables. Study both inner and outer joins. Then make sure to understand what COALESCE actually does. Books Online is a great resource.
January 29, 2016 at 1:47 am
Hi,
Thanks for your reply, Im not a complete newbie as I have an Access background and so I do understand joins and relationships. What I struggle with is working out how to produce a report based on those joins and relationships OR if Im looking at 2 tables which appear to have a relationship but possibly don't or they do but via a third party table which forms the link.
I shall get some sample data if that's ok and post it, not because Im being lazy but I have been trying to work this out for ages and am hitting a brick wall.
Thanks again
January 29, 2016 at 2:59 am
Shabbaranks (1/29/2016)
Hi,Thanks for your reply, Im not a complete newbie as I have an Access background and so I do understand joins and relationships. What I struggle with is working out how to produce a report based on those joins and relationships OR if Im looking at 2 tables which appear to have a relationship but possibly don't or they do but via a third party table which forms the link.
I shall get some sample data if that's ok and post it, not because Im being lazy but I have been trying to work this out for ages and am hitting a brick wall.
Thanks again
If you already have the tables in SQL Server, you can easily get the CREATE TABLE script by right-clicking the table in Object Explorer. Make sure to first go to the options and in the scripting tab, ensure that the options to script indexes and script constraints are checked. If the table has dozens of columns that are irrelevant for the question, then by all means remove them. If you want to anonimyze by changing column names, no problem - but do make sure to test the CREATE TABLE in an empty database if you modified it in any way.
For the INSERT statements, the data does not have to be real. If you work with sensitive data, I do not even want to see it. Instead, make up a few rows of fake data that illustrate the issue (perhaps the same you showed in the picture attached to a previous post?), write them out as INSERT statements, test them in that same empty database, and then post them. And then add the expected results for that test data.
January 29, 2016 at 3:22 am
Hi Hugo,
Again thanks for taking the time to assist its really appreciated. I didn't really want to mess with the SQL server and the DB where the data is stored so I linked and imported it to Access (I think there is a free access viewer available if you don't have M$ Office).
The data isn't really that sensitive as these tables just contain material numbers I've modified the vendor names just in case but other than that its raw data from the DB.
So from that data you'll see 2 tables the Material_Trans table holds all the records Im after but where it links to the Material_Req table (using the material_req field) the material_req.material field is blank - this is what Im trying to populate within a report\query.
I don't use dropbox so I just found this site which allows you to upload files. I hope Ive explained myself correctly?
Thanks
January 29, 2016 at 3:47 am
Shabbaranks (1/29/2016)
Hi Hugo,Again thanks for taking the time to assist its really appreciated. I didn't really want to mess with the SQL server and the DB where the data is stored so I linked and imported it to Access (I think there is a free access viewer available if you don't have M$ Office).
The data isn't really that sensitive as these tables just contain material numbers I've modified the vendor names just in case but other than that its raw data from the DB.
So from that data you'll see 2 tables the Material_Trans table holds all the records Im after but where it links to the Material_Req table (using the material_req field) the material_req.material field is blank - this is what Im trying to populate within a report\query.
I don't use dropbox so I just found this site which allows you to upload files. I hope Ive explained myself correctly?
Thanks
I still dont see an example required result set (??)....please can you give the results you require (just one example row will be fine ) based on the access db data you have supplied along with relevant comments
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 29, 2016 at 3:56 am
Sorry (and looking at your signature Im guilty... the you can lead a user to data)
Anyway so Im looking to get from the 2 tables see attachment as the formatting went to pot when I typed it
Thanks
January 29, 2016 at 4:34 am
I actually recommend that you *should* open SSMS and query the database correctly. Last time I looked, most queries fired from Access involved transporting all data over the network and then doing the logic in Access, which can cause huge network overhead. Much better to develop the query in SSMS, test it in SSMS, then enter it as a linked query in Access.
The reason I ask for CREATE TABLE and INSERT statements is that I want to be able to just copy/paste. I have limited time set aside for providing free help on forums, so I want to make that time count. Manually re-creating someone else's tables and data is not a good use of that limited time, so I will not do it.
The link you posted to filedropper.com opened a signup screen for me. I closed that screen. Too many accounts on too many sites already.
I'll give you a generic idea of how I think you can solve your query.
First, you need to join the two tables. The column to join on in table2 is obviously the key. In table1, it is the Material_Req column, when it is not null. When it is null, the join becomes irrelevant because in that case you don't need the data from table2 - you'll use Material from table1 instead.
For such a case, an outer join is ideal. When Material_Req is null, it will never find a match; but the outer join will ensure that the rows are not dropped from the result. To see the effect, you can temporarily use SELECT * in the query as you're developing it. (But don't use SELECT * in production!)
The next step is to use the COALESCE expression suggested by John Mitchell to use Material from Table1 if it is not null, or the proper column from Table2 if the Material column in table1 is null.
(Note that the above assumes that the data is filled as expected, i.e. there is always exactly one of the two relevant columns in Table1 populated. If that is not always the case, you'll have to define what you want returned in the cases where both are null or b oth are non-null).
January 29, 2016 at 6:35 am
Shabbaranks (1/29/2016)
Sorry (and looking at your signature Im guilty... the you can lead a user to data)Anyway so Im looking to get from the 2 tables see attachment as the formatting went to pot when I typed it
Thanks
sorry...still dont follow your thoughts?......the column names are not complete and dont indicate with table they come from
as Hugo suggested, some readily consumable scripts will be useful
see this article
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 29, 2016 at 8:24 am
That link will certainly help me in the future thank you. I think I've solved the issue now (well Im currently testing the output) the solution was I wasn't joining the tables correctly when I used a left outer join it worked (or at least I think it has).
Would be handy if there was a thanks button on this forum to show appreciation for the help, I cant see one but I would like to say thank you for all your help.
January 29, 2016 at 8:37 am
Shabbaranks (1/29/2016)
That link will certainly help me in the future thank you. I think I've solved the issue now (well Im currently testing the output) the solution was I wasn't joining the tables correctly when I used a left outer join it worked (or at least I think it has).Would be handy if there was a thanks button on this forum to show appreciation for the help, I cant see one but I would like to say thank you for all your help.
ok, hope all goes well.
would love to see your solution....???
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply