MS query adding more tables

[colval]

Hi

I am doing an MSquery linking into an access databse to link into an excel spradsheet.

An example databse as 37 records in all, all connceted to mulitple tables. when I add a table called Vlistcomponants the link then just shows 5 records. I am assuming this is because only 5 matching records have data in the vlistcomponant and the other 32 have nothing in it.

I need to show all 37 records, I ahve tried to change the Join types but it says I cannot have the other join types as there are to many in the query.

Here is the SQL code that works so far (see below). Any idea's how I can add the table in and see all 37 records. Thanks

SELECT TableSites.SiteName, TableSites.SiteAddr1, TableSites.SiteSurveyorsName, TableSamples.SampleDate, TableSamples.SampleSurveyType, VListComponents.ItemValue, VListAreas.ItemValue, VListFloors.ItemValue, TableSamples.SampleRoom
FROM TableSamples TableSamples, TableSites TableSites, VListAreas VListAreas, VListComponents VListComponents, VListFloors VListFloors
WHERE TableSamples.SampleAreaID = VListAreas.ID AND TableSamples.SampleFloorID = VListFloors.ID AND TableSamples.SampleSiteID = TableSites.SiteID AND TableSamples.SampleComponentID = VListComponents.ID
ORDER BY TableSamples.SampleDate

 

[SkipVought]

hi,

Edit the query.

In the QBE grid, you have JOINS between fields in the 2 tables, that represent these equalities in the WHERE clause...

WHERE TableSamples.SampleAreaID      = VListAreas.ID
  AND TableSamples.SampleFloorID     = VListFloors.ID
  AND TableSamples.SampleSiteID      = TableSites.SiteID
  AND [b]TableSamples.SampleComponentID = VListComponents.ID[/b]

In the QBE Grid, Double Click THIS JOIN and select the OPTION that includes ALL rows from TableSamples and only rows from VListComponents where they match.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[colval]

Hi

I have added in a table called Condition and this is when it happens.

SQl looks like this

TableSamples.SampleRoom, TableSamples.SampleSiteID
FROM ListCondition ListCondition, TableSamples TableSamples, TableSites TableSites, VListAreas VListAreas, VListFloors VListFloors
WHERE TableSamples.SampleAreaID = VListAreas.ID AND TableSamples.SampleFloorID = VListFloors.ID AND TableSamples.SampleSiteID = TableSites.SiteID AND TableSamples.SampleConditionID = ListCondition.ID
ORDER BY TableSamples.SampleDate

If I do as you suggest and try and force the Join to

TableSamples.conditionID = ListCondition.ID then it says

Cant have outer joins if there are more than 2 tables in the query.

If I leave it as the normal inner join it only shows 3 records but there are 37 records, but there may be only 3 with a condition in the field, but I need to see all 37.

Thanks

 

[SkipVought]

Does this work?

WHERE TableSamples.SampleAreaID = VListAreas.ID
  AND TableSamples.SampleFloorID = VListFloors.ID
  AND TableSamples.SampleSiteID = TableSites.SiteID
  AND TableSamples.SampleConditionID = ListCondition.ID[red][b](+)[/b][/red]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

MS Query displays only one outer join. However, you can modify the sql string to have more than one. In this case all will work, but only sql view is available.
The sql:
[tt]SELECT
what
FROM
table_1
LEFT/RIGHT JOIN [table_2]
ON [linked_fields1] AND [linked_fields2][/tt]


combo

 

[colval]

Hi Skip

Do you mean to add the (+) at the end of the sql line so it looks like
TableSamples.SampleConditionID = ListCondition.ID(+)

If so I tried this and it says Extra ) in query expression

Any ideas

Thanks

 

[colval]

Hi Combo

I have tried the following code but it gives me an error message saying
Syntax error in query expression 'Tablesamples.samplesareaid}'

I ahve checked it over and over but cant find the problem, any ideas, thanks

SELECT TableSamples.SampleID, VListAreas.ItemValue, VListFloors.ItemValue, TableSites.SiteClientID, ListCondition.Description
FROM ListCondition , TableSamples, TableSites, VListAreas , VListFloors
Left join vlistareas on {TableSamples.SampleAreaID} AND {VListAreas.ID}
Left join Vlistfloors on {TableSamples.SampleFloorID} AND {VListFloors.ID}
Left Join tablesitesid on {TableSamples.SampleSiteID} AND {TableSites.SiteID}
Right Join listcondition on {TableSamples.SampleConditionID} AND {ListCondition.ID}
WHERE TableSamples.SampleAreaID = VListAreas.ID AND TableSamples.SampleFloorID = VListFloors.ID AND TableSamples.SampleSiteID = TableSites.SiteID AND TableSamples.SampleConditionID = ListCondition.ID

 

[SkipVought]

More like this...

SELECT
  TableSamples.SampleID
, VListAreas.ItemValue
, VListFloors.ItemValue
, TableSites.SiteClientID
, ListCondition.Description

FROM
 ((((
  TableSamples
 Left join vlistareas
   on TableSamples.SampleAreaID = VListAreas.ID)
 Left join Vlistfloors
   on TableSamples.SampleFloorID = VListFloors.ID)
 Left Join tablesitesid
   on TableSamples.SampleSiteID = TableSites.SiteID)
 Right Join listcondition
   on TableSamples.SampleConditionID = ListCondition.ID)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[colval]

Hi Skip

Tried your script but I get a message saying 'could not add table '(((('

I ahve tried chancing the script but cannot seem to figure it out.

Any ideas

Thanks as always

 

[combo]

Try:

FROM
 TableSamples
 Left join vlistareas
   on TableSamples.SampleAreaID = VListAreas.ID
 Left join Vlistfloors
   on TableSamples.SampleFloorID = VListFloors.ID
 Left Join tablesitesid
   on TableSamples.SampleSiteID = TableSites.SiteID
 Right Join listcondition
   on TableSamples.SampleConditionID = ListCondition.ID

If you have ms access, you could build the query visually here and basing on its sql string apply the structure to ms query.

Are you sure that the RIGHT JOIN in your query is ok (the listcondition table becomes the one that has all entries in output)?

combo

 

[colval]

Hi

This is my full code trying bot Skip and combo's suggestion. But I am still gettting syntax errors, the more i try and change it the more confused it gets, any ideas , thanks

SELECT
TableSamples.SampleID
, VListAreas.ItemValue
, VListFloors.ItemValue
, TableSites.SiteClientID
, ListCondition.Description

FROM
TableSamples
Left join vlistareas
on TableSamples.SampleAreaID = VListAreas.ID
Left join Vlistfloors
on TableSamples.SampleFloorID = VListFloors.ID
Left Join tablesitesid
on TableSamples.SampleSiteID = TableSites.SiteID
Right Join listcondition
on TableSamples.SampleConditionID = ListCondition.ID

WHERE TableSamples.SampleAreaID = VListAreas.ID
AND TableSamples.SampleFloorID = VListFloors.ID
AND TableSamples.SampleSiteID = TableSites.SiteID
AND TableSamples.SampleConditionID = ListCondition.ID
ORDER BY TableSamples.SampleDate

 

[combo]

No need to add the 'WHERE' section - the rules are in joining criteria, they are different than inner join..

Ms query uses specific syntax, it duplicates table name (source table name and its alias(es)) without 'AS', uses 'WHERE' instead of 'INNER JOIN'. I'd try to build simple query with two tables and single outer join, see and copy its sql string, adapt it to four joins following ms query syntax rules and paste back to ms query sql string. In case of problems add table by table and test the query.

combo

 

[colval]

Hi

Ok I will try the simple approach, it did not work without the Where clauses either.

Thanks

 

[Asbestos2]

Hi Combo and Skip

I really dont know whats going on with my accounts on here, I was contacting on a thread about MSQuery using a new acocunt but I now get this


Tek-Tips - Registration Problem
There has been a problem with your membership registration...
Please contact Tek-Tips Forums Management to discuss this issue.
Please include your handle in the email.
Thank You

I had the same problem with my COLVAL handle and now my Asbestos1 handle. Any ideas why this keeps happening, I am sure I aa not doing anything wrong. I contact the support but get no replies.

Ok I replied to Combo today but no idea if I got any reply. Could one of you send me the up to date link, I have the link but it is only showing to 11th March.

Many thanks, hope this account stays live or I will run out of email addresses.

 

[SkipVought]

There has been no reply to this thread but yours since 11 Mar 11 11:54.

AGAIN, you do NOT need a WHERE clause for the JOINS that are defined in the FROM clause.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Asbestos2]

Hi

I added some comments last night and you asked me to try some other things

We did a simple select and that worked and also then using
2 tables with a join. this both worked but when I added the next table it did not work. I was using a username ASBESTOS1. The code below is the last copy I have of a thread after the 11th. Thanks

SELECT
TableSamples.SampleID as TS
, VListAreas.ItemValue as VA
, VListFoors.ItemValue as VF
, TableSites.SiteClientID as TI
, ListCondition.Description as LC

FROM
Tablesamples
Left join va
on TableSamples.SampleAreaID = VA.ID
Left join VL
on TableSamples.SampleFloorID = VF.ID
Left Join TI
on TableSamples.SampleSiteID = TI.SiteID
Right Join LC
on TableSamples.SampleConditionID = LC.ID

 

[combo]

As I wrote in my missing post (with your answer) use brackets. Do not mix aliases for fields or tables (you can add them after finalising basic sql string). Check ending ";". Check table and field names:

SELECT
  TableSamples.SampleID as TSID
, VListAreas.ItemValue as VAID
, VListFoors.ItemValue as VFID
, TableSites.SiteClientID as TIID
, ListCondition.Description as LCID

FROM
 (((Tablesamples
 Left join VListAreas AS VA
   on TableSamples.SampleAreaID = VA.ID)
 Left join VListFoors AS VF '?
   on TableSamples.SampleFloorID = VF.ID)
 Left Join TableSites AS TI
   on TableSamples.SampleSiteID = TI.SiteID)
 Right Join ListCondition AS LC 
   on TableSamples.SampleConditionID = LC.ID;

combo

 

[Asbestos2]

Thanks for your reply

I have tried the code as you have given above but it still says

Cannot add the table table (((Tablesamples

Any ideas

Thanks

 

[combo]

As it was said, try to build the sql string step by step (i.e. start from two joined tables in design view). No aliases. Switch to sql string, clean it to the above structure. Test. Now you will work only with sql. Add second table and join, use brackets. Test. If works, add third table, etc. test every step and names. Finally add aliases, for fields and tables if you like.
As for names - shouldn't be VListF[!]l[/!]oors?

combo

 

[SkipVought]

[quote[I have tried the code as you have given above but it still says....[/quote]
YOU, YOU, YOU have to try MANY other things.

What have YOU tried that has not explicitly been suggested to you? It appears that YOU HAVE NOT!



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]