I am trying to use the Left join command and having all sorts of problem.
I have 16 queries and each query has two columns in it the first column in each query is the "Index" which has similar vlaues but they are not in the same order and are not present in each query like "Ind-1" might be in query 1 but not in query 5 and so on and so forth
I wanna join the second column of all queries based on the index and where the particualt index value is not present I want it to display blank. So the output would look ilike
Ind-1 -- 6 -- 5 -- 1 -- 2 -- 3 .................
Ind-2 -- blk-- 3 --blk--5 -- 6 ..............
blk=blanks for values not found for the particular index in the Query
Yes i have the table with all the index values in it.
I s there a limit to how many columns you can put in horizontal arrangment
this is the code i wrote I am using Access 2003
Concat_Id is the Table with all indexes
CONCAT is the Index column which is named similar in all 16 tables
SRHWND_X are the tables from which columns have to retrieved
the join criteria is CONCAT=CONCAT
[0_3] and [2_3] are the columns that are to be pulled out and put alongside the Index (look at my orig question fo Description)
this is the code I have form some reason it does not go over two tables is there a Synatax error somewhere.
SELECT Concat_ID.CONCAT, SRHWND_0.[0_2], SRHWND_1.[2_3]
FROM (Concat_ID LEFT JOIN SRHWND_0 ON Concat_ID.CONCAT = SRHWND_0.CONCAT) LEFT JOIN SRHWND_1 ON Concat_ID.CONCAT = SRHWND_1.CONCAT;
I had the code i wrote working but it works only for two columns
as sson as i add a third column to the code
SRHWND_2 and try to use the same code it messe up
missing operator, FROM cluase error alll sorts of errors come up.
SELECT Concat_ID.CONCAT, SRHWND_0.[0_2], SRHWND_1.[2_3],
FROM (Concat_ID LEFT JOIN SRHWND_0 ON Concat_ID.CONCAT = SRHWND_0.CONCAT) LEFT JOIN SRHWND_1 ON Concat_ID.CONCAT = SRHWND_1.CONCAT LEFT JOIN SRHWND_2 ON Concat_ID.CONCAT = SRHWND_2.CONCAT;
FROM (Concat_ID LEFT JOIN SRHWND_0 ON Concat_ID.CONCAT = SRHWND_0.CONCAT) LEFT JOIN SRHWND_1 ON Concat_ID.CONCAT = SRHWND_1.CONCAT, Concat_ID LEFT JOIN SRHWND_2 ON Concat_ID.CONCAT = SRHWND_2.CONCAT;
May be tis the way iam adding the third column to it I dont if i need to put in parenthesis or comma or " ' just woldnt work
i asked if you had a table that had all the values
what does the SQL for Concat_ID actually look like?
also, the reason your second try didn't work is because you forgot another level of nested parentheses
watch carefully --
ON SRHWND_0.CONCAT = CONCAT.Concat_ID
ON SRHWND_1.CONCAT = CONCAT.Concat_ID
ON SRHWND_2.CONCAT = CONCAT.Concat_ID
Now Access is sayin that "System Resource Exceeded"
I do have a table but the coding I have doe up till now was very complex the data had to be broken down using differernt queries and firleds had to be deleted. This is what the SQl for "Concat_ID" looks like
SELECT DISTINCT PSMM_NPhase.CONCAT
ORDER BY PSMM_NPhase.CONCAT;
It looks at a different table all lists all possible Indexes(distinct Indexes nothing id repeated)