three table database

[ricardozelaya]

Table 32820 Table 32820LP
Table Property Details
PARCELID Field2 Etc. PARCELID Field2 Etc. PARCELID
Field2 Etc.
A Data A Data

B Data B Data
C Data A Data
D Data

I download table 32820 and table 32820LP already filled from a site. They
have field 1 in common. Table Property Details has Property Address in
common with Table 32820, but it's empty . I need to use it to input
information from clients. Table 32820 contains Tax ID, name, address, phone,
etc; table 32820LP contains Tax ID, case number, court date, etc. On table
Property Details, I need to save results of telephone call, such as bedrooms,
baths, square footage, etc. Table Property Details is empty.

How can I relate these tables in a query and still make the query updatable
without setting Dynaset (Inconsistent Updates)? The current join look like
this: I can make it updatable by setting Dynaset (Inconsistent Updates), but
doing so, does not update the Property details table when I edit the fields
in the form.

FROM (32820 LEFT JOIN 32820LP ON [32820].PARCELID = [32820LP].[PARCEL ID])
LEFT JOIN [Property Details] ON [32820].PARCELID = [Property Details].
[Property Address]
ORDER BY StrConv([ADDRESS],3);

 

[Jeff Boyce]

Your "Table Property Details" appears to have a key field of [PARCELID] in
the first part of your description, but you then refer to and have SQL
pointing to a [Property Address] field. Is there a chance this is part of
the issue?

You have data available to you. Given that Access is a relational database,
you'll need to ensure that the data you are keeping in Access (not
necessarily what data comes in looking like) is well-normalized. If not,
you'll not be able to (easily) use the functions/features Access offers.

You have a "Property Details" table -- but I don't know enough about your
particular situation to tell if this table is well-normalized or more like a
spreadsheet.

Can you provide an example of some of your data?

By the way, if, as your description suggests, there is NO data in the
Property Details table for a property in one of the other tables, perhaps
you DON'T need to join the tables to get data into it.

By the, by the way, if your "32820" and "32820LP" tables have identical
structure, you really will want to look into normalizing the structure of
your database. Just because the raw data comes in those two tables doesn't
mean you are limited to using it that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ricardozelaya via AccessMonster.com]

What I have is three tables. Table 1 has PARCEL ID field plus other data
fields, Table 2 has PARCEL ID field with some additional data fields. Table
1 is much bigger than Table 2. I need to bring together the records of table
1 and table 2 in a form, and add some customized information to table 3.
Table 1 and Table 2 have PARCEL ID in common. I created Table 3 with an
empty PARCEL ID field that I would like to automatically fill it whenever I
write something in any of its fields, so that the new created record would
have Table 3.PARCEL ID = to Table 1.PARCEL ID.

I did a one-to-many relationship betweeh Table 1 and Table 2 and a one-to-one
between Table 1 and Table 3. I only put Tables 1 & 2 in the query with a
include all records from table 1 and only matching records from table 2.
Table 3 does not appear in the query because it renders a non-updatable form.
What I did was create a form based on the table 1 & 2 query and inserted
another sub form based on table 3.

Is there an easier way to do this. Table 1 is like the master set, table 2
is a subset of table 1, and table 3 is a placeholder for additional
information related to tables 1 and 2. Table 3 should grow whenever more
information is obtained for the records in tables 1 & 2.

Your "Table Property Details" appears to have a key field of [PARCELID] in
the first part of your description, but you then refer to and have SQL
pointing to a [Property Address] field. Is there a chance this is part of
the issue?

You have data available to you. Given that Access is a relational database,
you'll need to ensure that the data you are keeping in Access (not
necessarily what data comes in looking like) is well-normalized. If not,
you'll not be able to (easily) use the functions/features Access offers.

You have a "Property Details" table -- but I don't know enough about your
particular situation to tell if this table is well-normalized or more like a
spreadsheet.

Can you provide an example of some of your data?

By the way, if, as your description suggests, there is NO data in the
Property Details table for a property in one of the other tables, perhaps
you DON'T need to join the tables to get data into it.

By the, by the way, if your "32820" and "32820LP" tables have identical
structure, you really will want to look into normalizing the structure of
your database. Just because the raw data comes in those two tables doesn't
mean you are limited to using it that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Table 32820 Table 32820LP
Table Property Details

[quoted text clipped - 31 lines]

[Property Address]
ORDER BY StrConv([ADDRESS],3);

 

[Jeff Boyce]

The SQL statement from your original post joined PARCELID to [Property
Address]:

LEFT JOIN [Property Details] ON [32820].PARCELID = [Property Details].
[Property Address]

but your explanation (below) says that you are using [PARCELID] as your key
field in your 3rd table.

It sounds like you're trying to add new (related) data via Table 3. Take a
look in Access HELP for "updatable query" -- it may give you more ideas
about which fields you have to have in the query for it to be updatable.

Regards

Jeff Boyce
Microsoft Office/Access MVP

What I have is three tables. Table 1 has PARCEL ID field plus other data
fields, Table 2 has PARCEL ID field with some additional data fields.
Table
1 is much bigger than Table 2. I need to bring together the records of
table
1 and table 2 in a form, and add some customized information to table 3.
Table 1 and Table 2 have PARCEL ID in common. I created Table 3 with an
empty PARCEL ID field that I would like to automatically fill it whenever
I
write something in any of its fields, so that the new created record would
have Table 3.PARCEL ID = to Table 1.PARCEL ID.

I did a one-to-many relationship betweeh Table 1 and Table 2 and a
one-to-one
between Table 1 and Table 3. I only put Tables 1 & 2 in the query with a
include all records from table 1 and only matching records from table 2.
Table 3 does not appear in the query because it renders a non-updatable
form.
What I did was create a form based on the table 1 & 2 query and inserted
another sub form based on table 3.

Is there an easier way to do this. Table 1 is like the master set, table
2
is a subset of table 1, and table 3 is a placeholder for additional
information related to tables 1 and 2. Table 3 should grow whenever more
information is obtained for the records in tables 1 & 2.

Your "Table Property Details" appears to have a key field of [PARCELID] in
the first part of your description, but you then refer to and have SQL
pointing to a [Property Address] field. Is there a chance this is part of
the issue?

You have data available to you. Given that Access is a relational
database,
you'll need to ensure that the data you are keeping in Access (not
necessarily what data comes in looking like) is well-normalized. If not,
you'll not be able to (easily) use the functions/features Access offers.

You have a "Property Details" table -- but I don't know enough about your
particular situation to tell if this table is well-normalized or more like
a
spreadsheet.

Can you provide an example of some of your data?

By the way, if, as your description suggests, there is NO data in the
Property Details table for a property in one of the other tables, perhaps
you DON'T need to join the tables to get data into it.

By the, by the way, if your "32820" and "32820LP" tables have identical
structure, you really will want to look into normalizing the structure of
your database. Just because the raw data comes in those two tables
doesn't
mean you are limited to using it that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Table 32820 Table 32820LP
Table Property Details

[quoted text clipped - 31 lines]

[Property Address]
ORDER BY StrConv([ADDRESS],3);