Firstly, I find the relationship window a real pain. It is awkward to navigate and layout and you have to redo it if you import your tables into a new database. it also does not seem to reflect the relationships of linked tables. Is there a better/easier way to manage relationships visually?

In a number of instances, I have linked a field in a table to a field in another table by using a look up sql in the table design. This seems to work fine. However, this doesn't seem to create the relationship that appears in the relationships window.

In some sql statements (for a form or report for instance) I have had to create a relationship by dragging and dropping between tables in the design view to get the records returned that I want.

In other sql statements I have had to delete a relationship that appears when I add a table to get the records returned that I want.

These 3 actions do not seem to make or delete relationships as shown in the relationship window. Are they somehow "local" as opposed to "global" and can you change them from Local to Global?

Also, do you need to create relationships in the relationships window if you make the links in the various places where you require them?

Any overview of the subtelties of this subject would be appreciated. I realize it could be excruciatingly deep, so please do not spend vast amounts of time writing a paper in reply.

I'm still a newbie so if I am well wide of the mark, forgive me.

Regards,

Keith.

It is awkward to navigate and layoutI agree.
you have to redo it if you import your tables into a new databaseNo you don't. Select the option to import relationships also when you use the import wizard.
it also does not seem to reflect the relationships of linked tablesIf you want to see the relationships of linked tables, add them to the window. As you add related tables, the relationships will automatically be shown. Even though the relationships are not shown by default, they are always enforced since they are enforced at the source database level. This is true of SQL Server, Oracle, DB2, etc. as well as linked Jet tables. So, it isn't necessary to see them for them to be working for you.
I have linked a field in a table to a field in another table by using a look up sql in the table designThis is a crutch implemented solely for users who don't understand how to create queries. Professionals do not use this "feature" due to the problems it causes with SQL and VBA.
In some sql statements (for a form or report for instance) I have had to create a relationship by dragging and dropping between tables in the design view to get the records returned that I want.Access attempts to create relationships based on the default join type set when you defined the relationship. If you are using a query and the names are different in the two queries being joined, Access will not recognize the relationship and you will have to draw the join line yourself.
In other sql statements I have had to delete a relationship that appears when I add a table to get the records returned that I want.This is the other side of "assumptions". When the assumption does what you want, it is good. When it does what you don't want, it is bad.
These 3 actions do not seem to make or delete relationships as shown in the relationship window. Are they somehow "local" as opposed to "global" and can you change them from Local to Global?If you are talking about creating joins in queries, that action has nothing to do with creating relationships. Relationships are created for the SOLE purpose of enforcing referential integrity and providing you with the cascade delete and cascade update options. Joins in queries can join ANY fields of the same data type whether it makes sense or not. Jet doesn't care. Join the employee table to the department table on EmployeeID to DepartmentID if you want. You'll get nonsense results but Access won't complain because the two data types are the same.
Also, do you need to create relationships in the relationships window if you make the links in the various places where you require themsee above response. Relationships and Joins are NOT the same thing. They are not interchangable.

Thanks Pat. Lost of useful feedback there. I will reflect on it and see where I have to polish my database.

Regards,

Keith

I have linked a field in a table to a field in another table by using a look up SQL in the table design
This is a crutch implemented solely for users who don't understand how to create queries. Professionals do not use this "feature" due to the problems it causes with SQL and VBA.
Hi Pat,
Understanding comes slowly.

I have changed all the combo and list boxes in tables to text boxes which gets rid of all the lookups. These lookups were redundant anyway as all data input is by forms. The database still functions correctly so I guess this proves the point and makes things tidier. :)

As a supplementary question, for forms or controls on forms etc, is there any difference between an SQL statement or saving the SQL statement as a query? Both methods seem to work. I ask this as I have tried to restrict queries to things that are used by more than one item, for example, getting employee details for reports. Is this a good practice?

Thanks in advance.

My personal preference is saved querydefs. That allows me to test the query independently. Access creates hidden querydefs when you use SQL in the RecordSource or RowSource so that makes them equivalent. Only SQL built in VBA is dynamic and therefore causes extra processing at runtime.

Only SQL built in VBA is dynamic and therefore causes extra processing at runtime.

Thanks Pat,

I think I will do some tidying up and convert the sql to queries once my db has settled down. I'm currently in the early stages of roll out and am making small changes most days which is keeping me fully occupied.

Regards,

Figured I'd ask away here-

While I like to use saved queries whenever I can, I'm not quite sure how to use queries when their criteria will depend on either form's entry or VBA's commands, and as a result, I just create a sql string when I'm working on a recordset or a dynamic rowsource.

If there is a mean of using stored query and passing the criteria along, I'd like to know; that would make everything a bit easier for me. :)

Figured I'd ask away here-

While I like to use saved queries whenever I can, I'm not quite sure how to use queries when their criteria will depend on either form's entry or VBA's commands, and as a result, I just create a sql string when I'm working on a recordset or a dynamic rowsource.

If there is a mean of using stored query and passing the criteria along, I'd like to know; that would make everything a bit easier for me. :)
If you want to refer to a text box called txtMyText on a form called frmMyForm, then you can use this as your criterion in the querydef:
Forms!frmMyForm.txtMyText