Form using multiple tables not allowing new records

Have you tried creating a subform with the small table? This might fix your
issue. Try making the relationship a one to many and also add a primary key
to your main table. If none of these work, I am not sure what else to do.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:

Cyberwolf wrote:
That is possible. I guess I didn't make myself clear. Sorry, You dont;
need to have fully matching data on both sides. By this I mean you can have
records on one side that does not have a match on the other. But to have a
match you need to make sure tha data on both sides is the same.

But if you mean that there will never be an exact match then you do have an
issue.

OK. I think we're getting somewhere, slowly. But, this IS the issue,
because I just tried it with two tables that DO have a one-to-many
relationship, and it allows new records.

So...what's going on with the tables with the Indeterminate
relationship? I can assure you that the fields are the same. It is
possible, however, that not only are there more records in one table,
but there STILL might not be a matching record in the longer table for
every record in the shorter table.

Is THIS probably what is wrong? Does there HAVE to be a match for
everything in the shorter table, for it to see the one-to-one
relationship?

-Aaron

--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


Cyberwolf wrote:

Damn. I will NEVER have fully matching data on both sides of the
relationship. One table is going to have more records than the other,
every time. I can only control the contents of my database and the
Applications table within it. I cannot control the content of the table
from the Web-application that I put into the Access database and join
to my table.

So I'm screwed, then?

-Aaron

I don't think they need to be keyed fields. but if you do not have matching
data on both sides of the relationship then it will not work. Also both
fields need to be the same data type. i.e. you can't have one field a number
and one as text. Or, if there is a space on one side the same has to be on
the other side. If you could post a few records from each table and let me
know what field have the relationship I might be able to help more. Also,
when posting the reocrds let me know the datatype on each field.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


Cyberwolf wrote:

Um, that's how I created the relationships in the first place. This one
comes up as "indeterminate."

I assume it has to do with either the fact that I'm not using a primary
key on either side of this relationship or the fact that there are
entries in the second table that do not match up with entries in the
first table.

-Aaron

Click on Tools the Relationships. Add the tables you want to create
relationships for, then simply click and drag the field in one table to the
field in the other table you want to join on. Then follow the prompts.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


TonyT wrote:
Hi Aaron,

If there is a relationship between the data in these 2 tables, and your can
join them with a relationship of one-to-one or one-to-many, then you can
create a form based on a query that joins the 2 together, and usually this
recordset will be updateable from within your form. (I say usually, because
some Outer Joins create recordsets that cannot be updated, but more often
than not in the case you describe it will be updateable).

TonyT..

Thanks, Tony.

This could be the problem. The relationship is showing up as
"indeterminate," when it really should be one-to-one (with some records
not matching at all). I noticed it before, but didn't know what I could
do about it.

What CAN I do about it? This is one of my few linkages that doesn't
involve a primary key, because these 1800 applications have their own
ID, and it was easier to link the data that way (similar to a vlookup
in Excel). I don't know how to proceed...

-Aaron


"Aaron" wrote:

Hi,
I've been teaching myself access on the job out of
necessity, and I've come across a small stumbling block in the forms I
need to create.

Basically, if I only build a form using one table, I can
create new records. If I am using more than one table, I cannot.
Specifically, if I start the form using one table, I can create new
records, and even if I alter the SQL code, changing the fields in the
form, I can STILL create new records. But, if I even so much as add
another table, and not even any fields, I cannot create new records
anymore. I've even tried adding ALL of the fields, using the * option,
from each of the tables, and it doesn't work. I don't WANT to add all
of the fields anyway, but I was willing to make this sacrifice if I had
to).

The nature of the data in these tables is such that I
should not combine them into one table. Basically, some of the data is
user-entered, and the rest is in a joined table that is pulled from a
web-application. I have no control over this web application, so I must
paste the table it gives me into the access database. So, I think I
HAVE to use 2 separate tables. If I don't include both tables, then
some of the fields a user will want to see will not be on the form,
since they are from the downloaded table from that web application.

Is there any way to create this form and still be able to
make new records? What is the problem, anyway?

Thanks!

-Aaron

Cyberwolf wrote:

Have you tried creating a subform with the small table? This might fix your
issue. Try making the relationship a one to many and also add a primary key
to your main table. If none of these work, I am not sure what else to do.

Possibly, but help me troubleshoot by clarifying what is and is not a
problem when joining data. I'm going to provide some examples, and
please tell me which ones will cause an "Indeterminate" where I want a
one-to-one. You have answered some of these clearly, but I just want to
be all-inclusive pertaining to my specific situation.

Situation 1: "Shorter" table does not have a match (in "longer" table)
for ALL entries.
Example: Shorter table: 1, 2, 3, 4 Longer table: 2, 3, 4, 5, 6, 7

Will THIS prevent a one-to-one relationship? If so, I'm probably
screwed. What will show up for the fields I'm trying to pull, for
records where the longer table doesn't have a match for the shorter
table? I'm trying to find out if this is the case right now.

Situation 2: One table has more entries than the other
Example: Table 1: 1, 2, 3, 4 Table 2: 1, 2, 3, 4, 5

Will THIS prevent a one-to-one relationship? Again, if so I'm probably
screwed.

Situation 3 (I think you covered this one): One table has data
presented slightly differently than the other, in at least one case.
Example: Table 1: "1","2","3","4" Table 2: " 1","2","3"," 4"

And this would cause an indeterminate relationship, right? Would those
records on the form also have data missing, when the join is not
finding the connection between the two tables? What would I be looking
for in those field entries for records where they do not match like
that?

I'll consider what you posted below after I've eliminated these
potential problems.

Thanks!





--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:

Cyberwolf wrote:
That is possible. I guess I didn't make myself clear. Sorry, You dont;
need to have fully matching data on both sides. By this I mean you can have
records on one side that does not have a match on the other. But to have a
match you need to make sure tha data on both sides is the same.

But if you mean that there will never be an exact match then you do have an
issue.

OK. I think we're getting somewhere, slowly. But, this IS the issue,
because I just tried it with two tables that DO have a one-to-many
relationship, and it allows new records.

So...what's going on with the tables with the Indeterminate
relationship? I can assure you that the fields are the same. It is
possible, however, that not only are there more records in one table,
but there STILL might not be a matching record in the longer table for
every record in the shorter table.

Is THIS probably what is wrong? Does there HAVE to be a match for
everything in the shorter table, for it to see the one-to-one
relationship?

-Aaron

--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


Cyberwolf wrote:

Damn. I will NEVER have fully matching data on both sides of the
relationship. One table is going to have more records than the other,
every time. I can only control the contents of my database and the
Applications table within it. I cannot control the content of the table
from the Web-application that I put into the Access database and join
to my table.

So I'm screwed, then?

-Aaron

I don't think they need to be keyed fields. but if you do not have matching
data on both sides of the relationship then it will not work. Also both
fields need to be the same data type. i.e. you can't have one field a number
and one as text. Or, if there is a space on one side the same has to be on
the other side. If you could post a few records from each table and let me
know what field have the relationship I might be able to help more. Also,
when posting the reocrds let me know the datatype on each field.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


Cyberwolf wrote:

Um, that's how I created the relationships in the first place. This one
comes up as "indeterminate."

I assume it has to do with either the fact that I'm not using a primary
key on either side of this relationship or the fact that there are
entries in the second table that do not match up with entries in the
first table.

-Aaron

Click on Tools the Relationships. Add the tables you want to create
relationships for, then simply click and drag the field in one table to the
field in the other table you want to join on. Then follow the prompts.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


TonyT wrote:
Hi Aaron,

If there is a relationship between the data in these 2 tables, and your can
join them with a relationship of one-to-one or one-to-many, then you can
create a form based on a query that joins the 2 together, and usually this
recordset will be updateable from within your form. (I say usually, because
some Outer Joins create recordsets that cannot be updated, but more often
than not in the case you describe it will be updateable).

TonyT..

Thanks, Tony.

This could be the problem. The relationship is showing up as
"indeterminate," when it really should be one-to-one (with some records
not matching at all). I noticed it before, but didn't know what I could
do about it.

What CAN I do about it? This is one of my few linkages that doesn't
involve a primary key, because these 1800 applications have their own
ID, and it was easier to link the data that way (similar to a vlookup
in Excel). I don't know how to proceed...

-Aaron


"Aaron" wrote:

Hi,
I've been teaching myself access on the job out of
necessity, and I've come across a small stumbling block in the forms I
need to create.

Basically, if I only build a form using one table, I can
create new records. If I am using more than one table, I cannot.
Specifically, if I start the form using one table, I can create new
records, and even if I alter the SQL code, changing the fields in the
form, I can STILL create new records. But, if I even so much as add
another table, and not even any fields, I cannot create new records
anymore. I've even tried adding ALL of the fields, using the * option,
from each of the tables, and it doesn't work. I don't WANT to add all
of the fields anyway, but I was willing to make this sacrifice if I had
to).

The nature of the data in these tables is such that I
should not combine them into one table. Basically, some of the data is
user-entered, and the rest is in a joined table that is pulled from a
web-application. I have no control over this web application, so I must
paste the table it gives me into the access database. So, I think I
HAVE to use 2 separate tables. If I don't include both tables, then
some of the fields a user will want to see will not be on the form,
since they are from the downloaded table from that web application.

Is there any way to create this form and still be able to
make new records? What is the problem, anyway?

Thanks!

-Aaron

This will definitely create issues. A one-to-one has to have the same number
of records on both sides. You might be better off with a one-to-many. One
more thing that Klatuu said. Make sure your fields that are linked are
indexed.

Sorry, I misunderstood your original post. See below pulled form the Help
file

Note If you drag a field that isn't a primary key and doesn't have a
unique index to another field that isn't a primary key and doesn't have a
unique index, an indeterminate relationship is created. In queries containing
tables with an indeterminate relationship, Microsoft Access displays a
default join line between the tables, but referential integrity won't be
enforced, and there's no guarantee that records are unique in either table.


--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


Cyberwolf wrote:

Have you tried creating a subform with the small table? This might fix your
issue. Try making the relationship a one to many and also add a primary key
to your main table. If none of these work, I am not sure what else to do.

Possibly, but help me troubleshoot by clarifying what is and is not a
problem when joining data. I'm going to provide some examples, and
please tell me which ones will cause an "Indeterminate" where I want a
one-to-one. You have answered some of these clearly, but I just want to
be all-inclusive pertaining to my specific situation.

Situation 1: "Shorter" table does not have a match (in "longer" table)
for ALL entries.
Example: Shorter table: 1, 2, 3, 4 Longer table: 2, 3, 4, 5, 6, 7

Will THIS prevent a one-to-one relationship? If so, I'm probably
screwed. What will show up for the fields I'm trying to pull, for
records where the longer table doesn't have a match for the shorter
table? I'm trying to find out if this is the case right now.

Situation 2: One table has more entries than the other
Example: Table 1: 1, 2, 3, 4 Table 2: 1, 2, 3, 4, 5

Will THIS prevent a one-to-one relationship? Again, if so I'm probably
screwed.

Situation 3 (I think you covered this one): One table has data
presented slightly differently than the other, in at least one case.
Example: Table 1: "1","2","3","4" Table 2: " 1","2","3"," 4"

And this would cause an indeterminate relationship, right? Would those
records on the form also have data missing, when the join is not
finding the connection between the two tables? What would I be looking
for in those field entries for records where they do not match like
that?

I'll consider what you posted below after I've eliminated these
potential problems.

Thanks!





--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:

Cyberwolf wrote:
That is possible. I guess I didn't make myself clear. Sorry, You dont;
need to have fully matching data on both sides. By this I mean you can have
records on one side that does not have a match on the other. But to have a
match you need to make sure tha data on both sides is the same.

But if you mean that there will never be an exact match then you do have an
issue.

OK. I think we're getting somewhere, slowly. But, this IS the issue,
because I just tried it with two tables that DO have a one-to-many
relationship, and it allows new records.

So...what's going on with the tables with the Indeterminate
relationship? I can assure you that the fields are the same. It is
possible, however, that not only are there more records in one table,
but there STILL might not be a matching record in the longer table for
every record in the shorter table.

Is THIS probably what is wrong? Does there HAVE to be a match for
everything in the shorter table, for it to see the one-to-one
relationship?

-Aaron

--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


Cyberwolf wrote:

Damn. I will NEVER have fully matching data on both sides of the
relationship. One table is going to have more records than the other,
every time. I can only control the contents of my database and the
Applications table within it. I cannot control the content of the table
from the Web-application that I put into the Access database and join
to my table.

So I'm screwed, then?

-Aaron

I don't think they need to be keyed fields. but if you do not have matching
data on both sides of the relationship then it will not work. Also both
fields need to be the same data type. i.e. you can't have one field a number
and one as text. Or, if there is a space on one side the same has to be on
the other side. If you could post a few records from each table and let me
know what field have the relationship I might be able to help more. Also,
when posting the reocrds let me know the datatype on each field.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


Cyberwolf wrote:

Um, that's how I created the relationships in the first place. This one
comes up as "indeterminate."

I assume it has to do with either the fact that I'm not using a primary
key on either side of this relationship or the fact that there are
entries in the second table that do not match up with entries in the
first table.

-Aaron

Click on Tools the Relationships. Add the tables you want to create
relationships for, then simply click and drag the field in one table to the
field in the other table you want to join on. Then follow the prompts.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


TonyT wrote:
Hi Aaron,

If there is a relationship between the data in these 2 tables, and your can
join them with a relationship of one-to-one or one-to-many, then you can
create a form based on a query that joins the 2 together, and usually this
recordset will be updateable from within your form. (I say usually, because
some Outer Joins create recordsets that cannot be updated, but more often
than not in the case you describe it will be updateable).

TonyT..

Thanks, Tony.

This could be the problem. The relationship is showing up as
"indeterminate," when it really should be one-to-one (with some records
not matching at all). I noticed it before, but didn't know what I could
do about it.

What CAN I do about it? This is one of my few linkages that doesn't
involve a primary key, because these 1800 applications have their own
ID, and it was easier to link the data that way (similar to a vlookup
in Excel). I don't know how to proceed...

-Aaron


"Aaron" wrote:

Hi,
I've been teaching myself access on the job out of
necessity, and I've come across a small stumbling block in the forms I
need to create.

Basically, if I only build a form using one table, I can
create new records. If I am using more than one table, I cannot.
Specifically, if I start the form using one table, I can create new
records, and even if I alter the SQL code, changing the fields in the
form, I can STILL create new records. But, if I even so much as add
another table, and not even any fields, I cannot create new records
anymore. I've even tried adding ALL of the fields, using the * option,
from each of the tables, and it doesn't work. I don't WANT to add all
of the fields anyway, but I was willing to make this sacrifice if I had
to).

The nature of the data in these tables is such that I
should not combine them into one table. Basically, some of the data is
user-entered, and the rest is in a joined table that is pulled from a
web-application. I have no control over this web application, so I must
paste the table it gives me into the access database. So, I think I
HAVE to use 2 separate tables. If I don't include both tables, then
some of the fields a user will want to see will not be on the form,
since they are from the downloaded table from that web application.

Is there any way to create this form and still be able to
make new records? What is the problem, anyway?

Thanks!

-Aaron

Cyberwolf wrote:
This will definitely create issues. A one-to-one has to have the same number
of records on both sides. You might be better off with a one-to-many. One
more thing that Klatuu said. Make sure your fields that are linked are
indexed.

How do I do that?

Sorry, I misunderstood your original post. See below pulled form the Help
file

Note If you drag a field that isn't a primary key and doesn't have a
unique index to another field that isn't a primary key and doesn't have a
unique index, an indeterminate relationship is created. In queries containing
tables with an indeterminate relationship, Microsoft Access displays a
default join line between the tables, but referential integrity won't be
enforced, and there's no guarantee that records are unique in either table.

I can't use the primary key, but perhaps I can index them. How do I do
that?

-Aaron

You can use the Indexed property to set a single-field index. An index speeds
up queries on the indexed fields as well as sorting and grouping operations.
For example, if you search for specific employee names in a LastName field,
you can create an index for this field to speed up the search for a specific
name.

Setting

The Indexed property uses the following settings.

Setting Description
No (Default) No index.
Yes (Duplicates OK) The index allows duplicates.
Yes (No Duplicates) The index doesn't allow duplicates.


You can set this property only in the Field Properties section in table
Design view. You can set a single-field index by setting the Indexed property
in the Field Properties section in table Design view.



Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:


Cyberwolf wrote:
This will definitely create issues. A one-to-one has to have the same number
of records on both sides. You might be better off with a one-to-many. One
more thing that Klatuu said. Make sure your fields that are linked are
indexed.

How do I do that?

Sorry, I misunderstood your original post. See below pulled form the Help
file

Note If you drag a field that isn't a primary key and doesn't have a
unique index to another field that isn't a primary key and doesn't have a
unique index, an indeterminate relationship is created. In queries containing
tables with an indeterminate relationship, Microsoft Access displays a
default join line between the tables, but referential integrity won't be
enforced, and there's no guarantee that records are unique in either table.

I can't use the primary key, but perhaps I can index them. How do I do
that?

-Aaron

Aaron wrote:
Cyberwolf wrote:
This will definitely create issues. A one-to-one has to have the same number
of records on both sides. You might be better off with a one-to-many. One
more thing that Klatuu said. Make sure your fields that are linked are
indexed.

How do I do that?

Scratch that, I figured it out.

I got the one join to be one-to-one, even though there are more entries
in one of the joined tables.

However, there is one more that SHOULD be one-to-many, I've got it
indexed (with duplicates OK on the many side, of course), and it's
still indeterminate. Working on it now.

Thanks for the help!

-Aaron

Got the other join to be one-to-many, and now my form works as it
should. For some reason, even after I indexed as well as I could
(duplicates OK in the main table, not in the table being referenced),
it was still indeterminate. But, I deleted the join, and redid it, and
it was one-to-many. Hmm.

So, when it doubt, rejoin, eh? I didn't have to for the one-to-one,
though. That fixed itself.

Thanks for all the help! Indexing was the key! And, there WERE two
duplicate entries in my main table. Crappy initial data (created by
someone else)...

-Aaron

Help!

I broke my form again! I know what did it, but I don't know how to fix
it.

Basically, I needed to use queries to calculate a couple of the values
for the forms. I can see that the join type for the queries doesn't
exist, if I join them to the tables. I can only assume this is just as
bad as having an "indeterminate" join type, as far as disallowing new
records.

So, what can I do about it? Can I create tables from these queries, so
that I can fix the relationships? How?

-Aaron

Aaron wrote:
Help!

I broke my form again! I know what did it, but I don't know how to fix
it.

Basically, I needed to use queries to calculate a couple of the values
for the forms. I can see that the join type for the queries doesn't
exist, if I join them to the tables. I can only assume this is just as
bad as having an "indeterminate" join type, as far as disallowing new
records.

So, what can I do about it? Can I create tables from these queries, so
that I can fix the relationships? How?

-Aaron

Give me a little more info. What exactly are you calculating in these
queries? YOu may be better off creating a calculated field.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


"Aaron" wrote:

Help!

I broke my form again! I know what did it, but I don't know how to fix
it.

Basically, I needed to use queries to calculate a couple of the values
for the forms. I can see that the join type for the queries doesn't
exist, if I join them to the tables. I can only assume this is just as
bad as having an "indeterminate" join type, as far as disallowing new
records.

So, what can I do about it? Can I create tables from these queries, so
that I can fix the relationships? How?

-Aaron