Relationships

[kon]

I have the table headmaster with 2 primary keys
customerid and date
Another table master with 3 primary keys
customerid, date and work description
The two tables have a relationship one to many connecting the 2 primary keys
of headmaster with the 2 only primary keys of master.
I want to use VBA code and SQL strint insert to in the master keys to fill
the workdescription (3rd primary key) with records from another table.
How can I do this.
I used me.dirty = false in the headmaster and aftewards I tried the SQL
string. How can I enter data in relationships tables.

 

[Vincent Johns]

I have the table headmaster with 2 primary keys
customerid and date
Another table master with 3 primary keys
customerid, date and work description

This seems kind of strange. I assume that the same-named fields contain
the same information (if they don't, then I suggest you rename them to
avoid confusion). Then [headmaster].[customerid] and
[headmaster].[date] are jointly uniquem since they're the primary key of
[headmaster]. Since you know that they're unique, why bother with
[master].[work description] as part of the primary key in [master]? You
already know that [master].[customerid] and [master].[date] suffice as
the primary key there.

The two tables have a relationship one to many connecting the 2 primary keys
of headmaster with the 2 only primary keys of master.
I want to use VBA code and SQL strint insert to in the master keys to fill
the workdescription (3rd primary key) with records from another table.
How can I do this.
I used me.dirty = false in the headmaster and aftewards I tried the SQL
string. How can I enter data in relationships tables.

Not knowing much about your data source, I'm not sure if the following
suggestion will work, but have you considered using an Update Query? It
would need to have some way to determine, from the other Table, what the
proper value of [workdescription] should be, based on [customerid] and
[date], but if there's a way to do that, you won't need any VBA.

Here's another, somewhat unrelated, thought... Depending on how similar
the Tables [headmaster] and [master] are, you might want to consider
combining their contents into just one Table, perhaps adding a
[IsHeadmaster?] field (yes/no data type) to identify the source of each
record. This would simplify maintenance by, for example, allowing
changes to the format of the [address] field to apply to records
originally from both Tables. Another example: you could use a single
Query, with a criterion based on the value of [IsHeadmaster?], to
display a list of [date] and [address] values, instead of having to
write and maintain two such Queries.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Jeff Boyce]

In addition to Vincent's suggestions, consider renaming the field you
mentioned as [date]. If this is literally the name of the field, be aware
that Access treats this as a reserved word. Both you and Access will have
headaches trying to figure out whether you mean the field you named, or the
function(s) Access knows about.

 

[Pat Hartman\(MVP\)]

Records should only be added to the many-side table when you have data for
them. So arbitrarily adding a record to the many-side table for each record
in the one-side table doesn't make sense. Besides, where would the value
for work description come from?

Just use a left join when joining these two tables. That will allow the
query to retrieve records from the one-side table even when there are no
related entries in the many-side table.