sql - How to join across tables? -

sql - How to join across tables? -

so have complex database i'm sure have been implemented better, i'm stuck trying create table using sql bring together field , utilize aggregate functions on others homecoming specific values place study form.

there 5 main tables @ play here

all_dates: contains 2 fields, first beingness unique id, , next beingness every month in "mm/1/yyyy" format designated point in time until current month. purpose of table left outer bring together query data/calculations pt_level table months since july 2010.

pt_level: contains many fields on patient specific metrics. our purposes utilize [unit],[inf_site_maj], [specif_site], [inf_month], , [inf_year]

v_group_unit: table has several fields , created allow 1 many relationship group(many) can paired pt_level.unit

unit_data: table has numbers days spent on devices or in room. need these number coming calculations.

nhsn_bm:this table contains benchmarking info specific authorities entity compare against, based on pt_level.[inf_year] , 1 time again aggregated later.

this sql code tells me have syntax error (operator missing) in ms access.

select * from( select all_dates.[all_dates], v_group_unit.[group], pt_level.[unit], pt_level.[inf_year], pt_level.[inf_month], pt_level.[inf site maj], pt_level.[specif site], count(pt_level.[inf site maj]) [tot/inf], sum(unit_data.[pt_days]) sumofpt_days, sum(unit_data.[cvc_days]) sumofcvc_days, avg(nhsn_bm.[clabsi_bm]) avgofclabsi_bm, avg(nhsn_bm.[clabsi_ur_m]) avgofclabsi_ur_m, sum(unit_data.[iuc_days]) sumofiuc_days, avg(nhsn_bm.[cauti_bm]) avgofcauti_bm, avg(nhsn_bm.[cauti_ur_m]) avgofcauti_ur_m, sum(unit_data.[vent_days]) sumofvent_days, avg(nhsn_bm.[vap_bm]) avgofvap_bm, avg(nhsn_bm.[vap_ur_m]) avgofvap_ur_m all_dates left bring together pt_level on all_dates.[all_month] = pt_level.[inf_month] , all_dates.[all_year] = pt_level.[inf_year], pt_level inner bring together unit_data on (pt_level.[unit] = unit_data.[unit]) , (pt_level.[inf_month] = unit_data.[month]) , (pt_level.[inf_year] = unit_data.[year]) inner bring together v_group_unit on (pt_level.[unit] = v_group_unit.[unit]) inner bring together nhsn_bm on (pt_level.[unit] = nhsn_bm.[unit_list]) , (pt_level.[inf_year] = nhsn_bm.[nhsn_bm_year]) grouping v_group_unit.[group], pt_level.[unit], pt_level.[inf_year], pt_level.[inf_month], pt_level.[inf site maj], pt_level.[specif site] having (((pt_level.[inf site maj]) in ("vae","bsi","uti")) , ((pt_level.[specif site]) in ("clabsi","cauti","vac","ivac","possible vap","probable vap"))) order v_group_unit.[group], pt_level.[unit], pt_level.[inf_year], pt_level.[inf_month], pt_level.[inf site maj] ) query1;

i think i'm messing trying utilize 2 parent tables insight helpful.

the comma , "pt_level" looks wrong me:

left bring together pt_level on all_dates.[all_month] = pt_level.[inf_month] , all_dates.[all_year] = pt_level.[inf_year], pt_level inner bring together unit_data on (pt_level.[unit] = unit_data.[unit]) , (pt_level.[inf_month] = unit_data.[month]) , (pt_level.[inf_year] = unit_data.[year])

what if replaced this:

left bring together pt_level on all_dates.[all_month] = pt_level.[inf_month] , all_dates.[all_year] = pt_level.[inf_year] inner bring together unit_data on (pt_level.[unit] = unit_data.[unit]) , (pt_level.[inf_month] = unit_data.[month]) , (pt_level.[inf_year] = unit_data.[year])

sql join ms-access-2010