Create View

[D. Morse]

I'm creating an inspection report database and mgmt wants me to design all
the forms using table views and not the tables directly because they plan to
move the tables to SQL Server once everything is up and running and only use
access as the front-end application.

The problem is I can't find a thing on creating a view in Access 2007 and I
come from an Oracle/SQL Server background and Access was kind of shunned by
my college professors, so I'm not sure of any other options in Access 2007.

Any ideas or suggestions are appreciated!

- D

 

[KARL DEWEY]

Use a query to feed a form in datasheet view.

 

[D. Morse]

Thanks.. didn't occur to me to use a saved query. We'll see how this works out!

Appreciate it!!

SELECT obs.obs_id,
obs.obs_date,
grp.group_desc,
bldg.bldg_desc,
loc.room_desc,
def.def_lvl_desc,
obs.desc,
cat.category_desc,
resp.resp_party_desc,
stat.status_desc,
obs.comments,
sal_feat.salient_feature_desc
FROM ((((((((((t_observation AS obs
INNER JOIN t_group AS grp
ON grp.group_id = obs.group_id)
INNER JOIN t_def_lvl AS def
ON def.def_lvl_id = obs.def_lvl_id)
INNER JOIN t_status AS stat
ON stat.status_id = obs.status_id)
INNER JOIN t_obs_resp_party AS obs_resp
ON obs_resp.obs_id = obs.obs_id)
INNER JOIN t_resp_party AS resp
ON resp.resp_party_id = obs_resp.resp_party_id)
INNER JOIN t_obs_category AS obs_cat
ON obs_cat.obs_id = obs.obs_id)
INNER JOIN t_category AS cat
ON cat.category_id = obs_cat.category_id)
INNER JOIN t_location AS loc
ON loc.location_id = obs.location_id)
INNER JOIN t_bldg AS bldg
ON bldg.bldg_id = loc.bldg_id)
LEFT OUTER JOIN t_loc_salient_feature AS loc_sal_feat
ON loc_sal_feat.location_id = loc.location_id)
LEFT OUTER JOIN t_salient_feature AS sal_feat

 

[D. Morse]

Thanks! It didn't occur to me to use a saved query. We'll see how this works
out.

I appreciate it!!!

SELECT obs.obs_id,
obs.obs_date,
grp.group_desc,
bldg.bldg_desc,
loc.room_desc,
def.def_lvl_desc,
obs.desc,
cat.category_desc,
resp.resp_party_desc,
stat.status_desc,
obs.comments,
sal_feat.salient_feature_desc
FROM ((((((((((t_observation AS obs
INNER JOIN t_group AS grp
ON grp.group_id = obs.group_id)
INNER JOIN t_def_lvl AS def
ON def.def_lvl_id = obs.def_lvl_id)
INNER JOIN t_status AS stat
ON stat.status_id = obs.status_id)
INNER JOIN t_obs_resp_party AS obs_resp
ON obs_resp.obs_id = obs.obs_id)
INNER JOIN t_resp_party AS resp
ON resp.resp_party_id = obs_resp.resp_party_id)
INNER JOIN t_obs_category AS obs_cat
ON obs_cat.obs_id = obs.obs_id)
INNER JOIN t_category AS cat
ON cat.category_id = obs_cat.category_id)
INNER JOIN t_location AS loc
ON loc.location_id = obs.location_id)
INNER JOIN t_bldg AS bldg
ON bldg.bldg_id = loc.bldg_id)
LEFT OUTER JOIN t_loc_salient_feature AS loc_sal_feat
ON loc_sal_feat.location_id = loc.location_id)
LEFT OUTER JOIN t_salient_feature AS sal_feat

 

[Michel Walsh]

An SQL view -> an Access query without parameter
An SQL stored procedure -> an Access query with parameters

(sure, in the later case, the stored procedure is assumed to be limited to
just one 'single' statement, though).

Note that you can UPDATE over an Access query even when you can't with, say,
MS SQL Sever, which greatly simplify the development.


Vanderghast, Access MVP