Three tables, one form...possible?

[Guest]

As per advice on this forum I have based my forms on my tables now rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and the
remainder (about 15) from the third table. How do I get this to work?

I have tried the Control Source option in the properties and that just won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Guest]

Normally you would prefer to use tables rather than queries as your
recordsource; however, in this instance, you will need to use a query. The
issue is whether there are multiple rows in the child tables. For example if
Table2 has more than one row related to Table1, how do you know which row in
Table2 to use? If both are included in the result of the query, then you
would end up with 2 rows in your query with the data from Table1 duplicated.
If this is the case, then you need to have the other tables in subforms. I
also believe that such a query would not be updatable. If there is a one to
one relationship, then a query will suffice.

 

[Roger Carlson]

I rarely base my forms on tables. I always base them on a saved query or a
SQL statement. However, this query (or SQL statement) will rarely involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless, I'm
guessing the advice to "use tables" should be modified to say "use queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger,
I had the same thought. I can't remember seeing any advice to use tables
instead of queries. When there is a single table involved, I don't know if
there is any technical reason to prefer one over the other.

I rarely base my forms on tables. I always base them on a saved query or a
SQL statement. However, this query (or SQL statement) will rarely involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless, I'm
guessing the advice to "use tables" should be modified to say "use queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

As per advice on this forum I have based my forms on my tables now rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and the
remainder (about 15) from the third table. How do I get this to work?

I have tried the Control Source option in the properties and that just won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Roger Carlson]

The only reason is if you want your data sorted in your form (and most of
the time you do). You cannot rely on a table's sort order, which is why I
always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
I had the same thought. I can't remember seeing any advice to use tables
instead of queries. When there is a single table involved, I don't know if
there is any technical reason to prefer one over the other.

I rarely base my forms on tables. I always base them on a saved query or a
SQL statement. However, this query (or SQL statement) will rarely involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless, I'm
guessing the advice to "use tables" should be modified to say "use queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

As per advice on this forum I have based my forms on my tables now rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and the
remainder (about 15) from the third table. How do I get this to work?

I have tried the Control Source option in the properties and that just won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Guest]

This is a real bugger!! Because I've changed all my forms to be based on
tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for processing and
reporting, not for use in forms for data entry."

on this post...

http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4

The only reason is if you want your data sorted in your form (and most of
the time you do). You cannot rely on a table's sort order, which is why I
always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
I had the same thought. I can't remember seeing any advice to use tables
instead of queries. When there is a single table involved, I don't know if
there is any technical reason to prefer one over the other.

I rarely base my forms on tables. I always base them on a saved query or a
SQL statement. However, this query (or SQL statement) will rarely involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless, I'm
guessing the advice to "use tables" should be modified to say "use queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my tables now rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and the
remainder (about 15) from the third table. How do I get this to work?

I have tried the Control Source option in the properties and that just
won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Roger Carlson]

The key to Rick Brandt's response is "Multi-table queries". You rarely use
these as the basis for forms. As Klatuu already said, in a One-to-Many
(1:M) relationship, you'll use a form/subform set up, where the form is
based on the 1-side table and the subform on the M-side table. However,
while you can base a form directly on the table, you cannot rely on the
sorting. That's why I recommend basing the form on a query. It doesn't
have to be much of a query. In many cases,
SELECT * FROM MyTable ORDER BY LastName, Firstname
is sufficient. In fact, I would probably do this as a SQL statement in the
RecordSource property rather than save the query.

It's not too difficult to change to this if your form is based on a table.
Just go to design view of the table and click the Builder button [...] to
the right, create the query in the Query Builder, then close the QB. It
will ask if you want to save the changes. Say yes and the SQL will be saved
in the Record Source.

As for your original question, it's hard to say without knowing exactly what
the tables are and the relationships between them. Can you be more specific?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

This is a real bugger!! Because I've changed all my forms to be based on
tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for processing and
reporting, not for use in forms for data entry."

on this post...

http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4

The only reason is if you want your data sorted in your form (and most of
the time you do). You cannot rely on a table's sort order, which is why I
always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
I had the same thought. I can't remember seeing any advice to use tables
instead of queries. When there is a single table involved, I don't

know
if

there is any technical reason to prefer one over the other.

:

I rarely base my forms on tables. I always base them on a saved

query
or a

SQL statement. However, this query (or SQL statement) will rarely involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless, I'm
guessing the advice to "use tables" should be modified to say "use queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my tables now rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and the
remainder (about 15) from the third table. How do I get this to work?

I have tried the Control Source option in the properties and that just
won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Guest]

The tables I have are (not all fields):

tblContacts
*pkCRDNumber (one to many with other tables CRDNumber)

tblClaims
pkLynxClaimReference
*CRDNumber

tblProducts
~pkClaimID (one to one relationship with Returns pkClaimID)
*CRDNumber

tblReturns
~pkClaimID
*CRDNumber

The key to Rick Brandt's response is "Multi-table queries". You rarely use
these as the basis for forms. As Klatuu already said, in a One-to-Many
(1:M) relationship, you'll use a form/subform set up, where the form is
based on the 1-side table and the subform on the M-side table. However,
while you can base a form directly on the table, you cannot rely on the
sorting. That's why I recommend basing the form on a query. It doesn't
have to be much of a query. In many cases,
SELECT * FROM MyTable ORDER BY LastName, Firstname
is sufficient. In fact, I would probably do this as a SQL statement in the
RecordSource property rather than save the query.

It's not too difficult to change to this if your form is based on a table.
Just go to design view of the table and click the Builder button [...] to
the right, create the query in the Query Builder, then close the QB. It
will ask if you want to save the changes. Say yes and the SQL will be saved
in the Record Source.

As for your original question, it's hard to say without knowing exactly what
the tables are and the relationships between them. Can you be more specific?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

This is a real bugger!! Because I've changed all my forms to be based on
tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for processing and
reporting, not for use in forms for data entry."

on this post...

http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4

The only reason is if you want your data sorted in your form (and most of
the time you do). You cannot rely on a table's sort order, which is why I
always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger,
I had the same thought. I can't remember seeing any advice to use tables
instead of queries. When there is a single table involved, I don't know
if
there is any technical reason to prefer one over the other.

:

I rarely base my forms on tables. I always base them on a saved query
or a
SQL statement. However, this query (or SQL statement) will rarely
involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless, I'm
guessing the advice to "use tables" should be modified to say "use
queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my tables now
rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and the
remainder (about 15) from the third table. How do I get this to work?

I have tried the Control Source option in the properties and that just
won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Guest]

Also - I really want to avoid using the form/subform setup for this form. I
am using it successfully with others but for this one its just isn't that
practical.

The key to Rick Brandt's response is "Multi-table queries". You rarely use
these as the basis for forms. As Klatuu already said, in a One-to-Many
(1:M) relationship, you'll use a form/subform set up, where the form is
based on the 1-side table and the subform on the M-side table. However,
while you can base a form directly on the table, you cannot rely on the
sorting. That's why I recommend basing the form on a query. It doesn't
have to be much of a query. In many cases,
SELECT * FROM MyTable ORDER BY LastName, Firstname
is sufficient. In fact, I would probably do this as a SQL statement in the
RecordSource property rather than save the query.

It's not too difficult to change to this if your form is based on a table.
Just go to design view of the table and click the Builder button [...] to
the right, create the query in the Query Builder, then close the QB. It
will ask if you want to save the changes. Say yes and the SQL will be saved
in the Record Source.

As for your original question, it's hard to say without knowing exactly what
the tables are and the relationships between them. Can you be more specific?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

This is a real bugger!! Because I've changed all my forms to be based on
tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for processing and
reporting, not for use in forms for data entry."

on this post...

http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4

The only reason is if you want your data sorted in your form (and most of
the time you do). You cannot rely on a table's sort order, which is why I
always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger,
I had the same thought. I can't remember seeing any advice to use tables
instead of queries. When there is a single table involved, I don't know
if
there is any technical reason to prefer one over the other.

:

I rarely base my forms on tables. I always base them on a saved query
or a
SQL statement. However, this query (or SQL statement) will rarely
involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless, I'm
guessing the advice to "use tables" should be modified to say "use
queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my tables now
rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and the
remainder (about 15) from the third table. How do I get this to work?

I have tried the Control Source option in the properties and that just
won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Roger Carlson]

So let's see if I have this straight:

Each Contact can have one or more Claims
Each Contact can have one or more Products
Each Contact can have one or more Returns
Each Product can have one and only one Return (and vise versa)

Questions:
Is there any relationship between a Claim and a Product or a Return? In
other words, is the Claim about a specific Product? If so, you need a
relationship. As it stands, all you can say is a Contact as a certain
number of Claims, Products, and Returns, but you can't say how the Products
and Returns are related to a specific Claim. If they are not, then your
design is fine. If they are, then you need to do some modification to be
able to store the information you need.

I realize this was not your question, but it MAY be part of your problem
with designing a workable form.

Observation:
In many (most?) cases, One-to-One relationships can be pulled together into
one table. This greatly simplifies form design.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

The tables I have are (not all fields):

tblContacts
*pkCRDNumber (one to many with other tables CRDNumber)

tblClaims
pkLynxClaimReference
*CRDNumber

tblProducts
~pkClaimID (one to one relationship with Returns pkClaimID)
*CRDNumber

tblReturns
~pkClaimID
*CRDNumber

The key to Rick Brandt's response is "Multi-table queries". You rarely use
these as the basis for forms. As Klatuu already said, in a One-to-Many
(1:M) relationship, you'll use a form/subform set up, where the form is
based on the 1-side table and the subform on the M-side table. However,
while you can base a form directly on the table, you cannot rely on the
sorting. That's why I recommend basing the form on a query. It doesn't
have to be much of a query. In many cases,
SELECT * FROM MyTable ORDER BY LastName, Firstname
is sufficient. In fact, I would probably do this as a SQL statement in the
RecordSource property rather than save the query.

It's not too difficult to change to this if your form is based on a table.
Just go to design view of the table and click the Builder button [...] to
the right, create the query in the Query Builder, then close the QB. It
will ask if you want to save the changes. Say yes and the SQL will be saved
in the Record Source.

As for your original question, it's hard to say without knowing exactly what
the tables are and the relationships between them. Can you be more specific?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

This is a real bugger!! Because I've changed all my forms to be based on
tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for processing and
reporting, not for use in forms for data entry."

on this post...

http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4

:

The only reason is if you want your data sorted in your form (and

most
of

the time you do). You cannot rely on a table's sort order, which is

why
I

always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger,
I had the same thought. I can't remember seeing any advice to use tables
instead of queries. When there is a single table involved, I

don't
know

if
there is any technical reason to prefer one over the other.

:

I rarely base my forms on tables. I always base them on a saved query
or a
SQL statement. However, this query (or SQL statement) will rarely
involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread?

Regardless,
I'm

guessing the advice to "use tables" should be modified to say "use
queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my tables now
rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another

and
the

remainder (about 15) from the third table. How do I get this

to
work?

I have tried the Control Source option in the properties and

that
just

won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Roger Carlson]

As for your specific question:

No, I don't see how you can design a single form based on these tables that
will allow data entry. You will have to use a form/subform design of some
sort. Based on your original table design, you'd need a form for Contacts,
a subform for Claims, a subform for Products and the a subsubform for
Returns (within the Products subform).

I have a sample on my website (www.rogersaccesslibrary.com) called
"Subform3Levels.mdb" which might give you some ideas on how to proceed.

Another solution that would use a single form would be to use an Unbound
form. That is a form with no Record Source. The problem is YOU have to
code all the appends, updates, and deletes yourself. Unbound forms can be
complex to use, but they give you all the flexibility you need.

I have two other samples: "Unbound.mdb" and "UnboundSQL.mdb" which
illustrate how to use simple unbound forms. Yours would be much more
complex.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Also - I really want to avoid using the form/subform setup for this form. I
am using it successfully with others but for this one its just isn't that
practical.

The key to Rick Brandt's response is "Multi-table queries". You rarely use
these as the basis for forms. As Klatuu already said, in a One-to-Many
(1:M) relationship, you'll use a form/subform set up, where the form is
based on the 1-side table and the subform on the M-side table. However,
while you can base a form directly on the table, you cannot rely on the
sorting. That's why I recommend basing the form on a query. It doesn't
have to be much of a query. In many cases,
SELECT * FROM MyTable ORDER BY LastName, Firstname
is sufficient. In fact, I would probably do this as a SQL statement in the
RecordSource property rather than save the query.

It's not too difficult to change to this if your form is based on a table.
Just go to design view of the table and click the Builder button [...] to
the right, create the query in the Query Builder, then close the QB. It
will ask if you want to save the changes. Say yes and the SQL will be saved
in the Record Source.

As for your original question, it's hard to say without knowing exactly what
the tables are and the relationships between them. Can you be more specific?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

This is a real bugger!! Because I've changed all my forms to be based on
tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for processing and
reporting, not for use in forms for data entry."

on this post...

http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4

:

The only reason is if you want your data sorted in your form (and

most
of

the time you do). You cannot rely on a table's sort order, which is

why
I

always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger,
I had the same thought. I can't remember seeing any advice to use tables
instead of queries. When there is a single table involved, I

don't
know

if
there is any technical reason to prefer one over the other.

:

I rarely base my forms on tables. I always base them on a saved query
or a
SQL statement. However, this query (or SQL statement) will rarely
involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread?

Regardless,
I'm

guessing the advice to "use tables" should be modified to say "use
queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my tables now
rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another

and
the

remainder (about 15) from the third table. How do I get this

to
work?

I have tried the Control Source option in the properties and

that
just

won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Guest]

The tables are Contacts, Products, Returns (Claim is just the word I'm using
for the ID in the products table - i should really rename that table to
Claims)

If I renamed the table, it would be like this:
Each contact can have one or more claims.
Each contact can have one ormore returns.
Each claim can only have one return (if at all).

At the moment returns is subform of the form used for claims (hoping to make
it optional/conditionally formatted greyed out thing).

I have chosen to keep claims and returns separate because claims is a rather
large table on its own and the returns table may not always be used for each
product.

Thanks heaps for the observation comments - this is just what i need. Do
you suggest I change the relationships?

EXAMPLE
Customer rings with a problem - CONTACT
Customer has problems with two products - TWO CLAIMS
Customer needs to return one of the products and not the other - ONE RETURN

So let's see if I have this straight:

Each Contact can have one or more Claims
Each Contact can have one or more Products
Each Contact can have one or more Returns
Each Product can have one and only one Return (and vise versa)

Questions:
Is there any relationship between a Claim and a Product or a Return? In
other words, is the Claim about a specific Product? If so, you need a
relationship. As it stands, all you can say is a Contact as a certain
number of Claims, Products, and Returns, but you can't say how the Products
and Returns are related to a specific Claim. If they are not, then your
design is fine. If they are, then you need to do some modification to be
able to store the information you need.

I realize this was not your question, but it MAY be part of your problem
with designing a workable form.

Observation:
In many (most?) cases, One-to-One relationships can be pulled together into
one table. This greatly simplifies form design.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

The tables I have are (not all fields):

tblContacts
*pkCRDNumber (one to many with other tables CRDNumber)

tblClaims
pkLynxClaimReference
*CRDNumber

tblProducts
~pkClaimID (one to one relationship with Returns pkClaimID)
*CRDNumber

tblReturns
~pkClaimID
*CRDNumber

The key to Rick Brandt's response is "Multi-table queries". You rarely use
these as the basis for forms. As Klatuu already said, in a One-to-Many
(1:M) relationship, you'll use a form/subform set up, where the form is
based on the 1-side table and the subform on the M-side table. However,
while you can base a form directly on the table, you cannot rely on the
sorting. That's why I recommend basing the form on a query. It doesn't
have to be much of a query. In many cases,
SELECT * FROM MyTable ORDER BY LastName, Firstname
is sufficient. In fact, I would probably do this as a SQL statement in the
RecordSource property rather than save the query.

It's not too difficult to change to this if your form is based on a table.
Just go to design view of the table and click the Builder button [...] to
the right, create the query in the Query Builder, then close the QB. It
will ask if you want to save the changes. Say yes and the SQL will be saved
in the Record Source.

As for your original question, it's hard to say without knowing exactly what
the tables are and the relationships between them. Can you be more specific?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



This is a real bugger!! Because I've changed all my forms to be based on
tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for processing and
reporting, not for use in forms for data entry."

on this post...


http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4




:

The only reason is if you want your data sorted in your form (and most
of
the time you do). You cannot rely on a table's sort order, which is why
I
always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger,
I had the same thought. I can't remember seeing any advice to use
tables
instead of queries. When there is a single table involved, I don't
know
if
there is any technical reason to prefer one over the other.

:

I rarely base my forms on tables. I always base them on a saved
query
or a
SQL statement. However, this query (or SQL statement) will rarely
involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless,
I'm
guessing the advice to "use tables" should be modified to say "use
queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my tables now
rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and
the
remainder (about 15) from the third table. How do I get this to
work?

I have tried the Control Source option in the properties and that
just
won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Roger Carlson]

Although there is some logic to keeping Claims and Returns in separate
tables (from a theoretical viewpoint) it does complicate the application
design. I would probably combine them into one table called Claims.

One way to implement this change would be to have a main form based on
Contact and two subforms, each based on Claims. One subform would show the
Claims information and the other the Returns information. Perhaps add a
Yes/No field to the table so that when this is checked in the Claims
subform, the Returns subform is not visible (or greyed out).

The trick here is to synchronize the subforms so that when you move from
Claim to claim, the appropriate Returns appear. I have another sample on my
site called "SynchronizedSubforms.mdb" that can help illustrate this.

There is no one "right" answer here. Every option has its pros and cons.
You'll have to decide what's best for you at your current level of
expertise.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

The tables are Contacts, Products, Returns (Claim is just the word I'm using
for the ID in the products table - i should really rename that table to
Claims)

If I renamed the table, it would be like this:
Each contact can have one or more claims.
Each contact can have one ormore returns.
Each claim can only have one return (if at all).

At the moment returns is subform of the form used for claims (hoping to make
it optional/conditionally formatted greyed out thing).

I have chosen to keep claims and returns separate because claims is a rather
large table on its own and the returns table may not always be used for each
product.

Thanks heaps for the observation comments - this is just what i need. Do
you suggest I change the relationships?

EXAMPLE
Customer rings with a problem - CONTACT
Customer has problems with two products - TWO CLAIMS
Customer needs to return one of the products and not the other - ONE RETURN

So let's see if I have this straight:

Each Contact can have one or more Claims
Each Contact can have one or more Products
Each Contact can have one or more Returns
Each Product can have one and only one Return (and vise versa)

Questions:
Is there any relationship between a Claim and a Product or a Return? In
other words, is the Claim about a specific Product? If so, you need a
relationship. As it stands, all you can say is a Contact as a certain
number of Claims, Products, and Returns, but you can't say how the Products
and Returns are related to a specific Claim. If they are not, then your
design is fine. If they are, then you need to do some modification to be
able to store the information you need.

I realize this was not your question, but it MAY be part of your problem
with designing a workable form.

Observation:
In many (most?) cases, One-to-One relationships can be pulled together into
one table. This greatly simplifies form design.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

The tables I have are (not all fields):

tblContacts
*pkCRDNumber (one to many with other tables CRDNumber)

tblClaims
pkLynxClaimReference
*CRDNumber

tblProducts
~pkClaimID (one to one relationship with Returns pkClaimID)
*CRDNumber

tblReturns
~pkClaimID
*CRDNumber



:

The key to Rick Brandt's response is "Multi-table queries". You

rarely
use

these as the basis for forms. As Klatuu already said, in a One-to-Many
(1:M) relationship, you'll use a form/subform set up, where the form is
based on the 1-side table and the subform on the M-side table. However,
while you can base a form directly on the table, you cannot rely on the
sorting. That's why I recommend basing the form on a query. It doesn't
have to be much of a query. In many cases,
SELECT * FROM MyTable ORDER BY LastName, Firstname
is sufficient. In fact, I would probably do this as a SQL statement

in
the

RecordSource property rather than save the query.

It's not too difficult to change to this if your form is based on a table.
Just go to design view of the table and click the Builder button

[...]
to

the right, create the query in the Query Builder, then close the QB. It
will ask if you want to save the changes. Say yes and the SQL will

be
saved

in the Record Source.

As for your original question, it's hard to say without knowing

exactly
what

the tables are and the relationships between them. Can you be more specific?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



This is a real bugger!! Because I've changed all my forms to be

based
on

tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for

processing
and

reporting, not for use in forms for data entry."

on this post...

http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4

:

The only reason is if you want your data sorted in your form

(and
most

of
the time you do). You cannot rely on a table's sort order,

which is
why

I
always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger,
I had the same thought. I can't remember seeing any advice to use
tables
instead of queries. When there is a single table involved, I don't
know
if
there is any technical reason to prefer one over the other.

:

I rarely base my forms on tables. I always base them on a saved
query
or a
SQL statement. However, this query (or SQL statement) will rarely
involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless,
I'm
guessing the advice to "use tables" should be modified to

say
"use

queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my

tables
now

rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from

another
and

the
remainder (about 15) from the third table. How do I get

this
to

work?

I have tried the Control Source option in the properties

and
that

just
won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+

 

[Guest]

Thanks very much for your detailed answer Roger. I will do just that....after
I check the links that you have given me, tomorrow. (M, W, F - Database days).

Cheers for your help.

Cindy

As for your specific question:

No, I don't see how you can design a single form based on these tables that
will allow data entry. You will have to use a form/subform design of some
sort. Based on your original table design, you'd need a form for Contacts,
a subform for Claims, a subform for Products and the a subsubform for
Returns (within the Products subform).

I have a sample on my website (www.rogersaccesslibrary.com) called
"Subform3Levels.mdb" which might give you some ideas on how to proceed.

Another solution that would use a single form would be to use an Unbound
form. That is a form with no Record Source. The problem is YOU have to
code all the appends, updates, and deletes yourself. Unbound forms can be
complex to use, but they give you all the flexibility you need.

I have two other samples: "Unbound.mdb" and "UnboundSQL.mdb" which
illustrate how to use simple unbound forms. Yours would be much more
complex.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Also - I really want to avoid using the form/subform setup for this form. I
am using it successfully with others but for this one its just isn't that
practical.

The key to Rick Brandt's response is "Multi-table queries". You rarely use
these as the basis for forms. As Klatuu already said, in a One-to-Many
(1:M) relationship, you'll use a form/subform set up, where the form is
based on the 1-side table and the subform on the M-side table. However,
while you can base a form directly on the table, you cannot rely on the
sorting. That's why I recommend basing the form on a query. It doesn't
have to be much of a query. In many cases,
SELECT * FROM MyTable ORDER BY LastName, Firstname
is sufficient. In fact, I would probably do this as a SQL statement in the
RecordSource property rather than save the query.

It's not too difficult to change to this if your form is based on a table.
Just go to design view of the table and click the Builder button [...] to
the right, create the query in the Query Builder, then close the QB. It
will ask if you want to save the changes. Say yes and the SQL will be saved
in the Record Source.

As for your original question, it's hard to say without knowing exactly what
the tables are and the relationships between them. Can you be more specific?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



This is a real bugger!! Because I've changed all my forms to be based on
tables and deleted all the queries!! Hang on, let me find the post I
received....

This is the advice..."Multi-table queries are mostly for processing and
reporting, not for use in forms for data entry."

on this post...


http://www.microsoft.com/office/com...sign&mid=9a0a74be-188c-43ea-99a8-64fad5f886f4




:

The only reason is if you want your data sorted in your form (and most
of
the time you do). You cannot rely on a table's sort order, which is why
I
always use a query or SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Roger,
I had the same thought. I can't remember seeing any advice to use
tables
instead of queries. When there is a single table involved, I don't
know
if
there is any technical reason to prefer one over the other.

:

I rarely base my forms on tables. I always base them on a saved
query
or a
SQL statement. However, this query (or SQL statement) will rarely
involve a
JOIN of two or more tables for the reasons Klatuu mentioned.

I missed this advice. Can you point me to the thread? Regardless,
I'm
guessing the advice to "use tables" should be modified to say "use
queries
based on single tables as the record source for forms".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




As per advice on this forum I have based my forms on my tables now
rather
than queries. This fixed a problem but created another one.

I have one control from one table, three controls from another and
the
remainder (about 15) from the third table. How do I get this to
work?

I have tried the Control Source option in the properties and that
just
won't
work...keep returning [#Name?].

Does anyone have any idea?

Cheers+