Can't add new records in query view

[madwoman]

I have inherited some Foxpro tables which I am trying to convert to Access and I hae set the exported tables up and have tried to set the relationships between the tables so that I can run queries and setup forms to review and add new records. However I am struggling to get the relationships in place that will allow me to do this. When I try to drag the common fields from one table to another to setup the link I get a screen which shows Relationship Type as 'indterminate' rather than 'one-to-many'. Whatever I do I can't change the type of link and when I view a query based on the two linked tables the * in the record navigator is greyed out so I can't add new records.
I have a sample of the tables,links,queries and forms I have setup to date (with a limited number of records), if someone could have a look at it and advise where I am going wrong that would be great.
The data and the structure of the tables is pretty much fixed as this is what is coming through from Foxpro and there is a lot of historical data to bring across.
Many thanks

 

[gromit]

Hi and welcome!

You are getting the "indeterminate" relationships because the links are between an arbitrary field in the data (such as membernum) and not between a primary key (like ID) and a well defined foreign key.

I would start by defining clear primary keys for each table (usually the autonumber field is the best) and name them appropriately. E.g. in the grades table, change "ref" to GradeID. In the homeadrs table, change Ref to HomeAddressID, etc.

Then in each of the related tables, use the appropriate name as the field for the foreign key. E.g. table Main would have both a GradeID and HomeAddressID.

Now you can establish the relationships between the tables and should get the appropriate relationship type.

Note: since you are importing, you may be able to change the relevant incoming field to a primary key. E.g. the grade field in the grade table. However, I don't know enough about your data to tell if that would work.

hope that helps,

- g