TABLE (1 Viewer)

[The_Doc_Man]

Since you didn't know about relationship diagrams, I'm going to suggest that you do some serious reading on database normalization. The reason you want to do that is two-fold. First, normalizing your DB saves space (usually) by pointing to something that is re-used a lot. Second, if you have a good relationship diagram that is closer to normalized, the query and form wizards can read the relationships and "power assist" you in building something that will work more efficiently.

"Database Normalization" is what you would seek on the general Internet. On this forum, which is oriented to databases, you only need to ask for "Normalization."

Here is one problem. When you look at your data, it is possible that your master data set isn't normalized, but you claim to have not power over the data set. How you deal with that will be a serious issue.

Here is another problem. You mentioned that you have a FoxPro back-end DB but you also had some tables in a native-Access back-end. You can't draw relationships between the two because a limitation of Access is that you can only relate tables that are in the same file. You can still express a relationship implicitly in your queries, but that "power assist" won't be there for things that are in different files.

 

[Pat Hartman]

You can't draw relationships between the two because a limitation of Access is that you can only relate tables that are in the same file.

This is not an "Access" limitation. It is a limitation of all RDBMS. Relationships only exist WITHIN a physical database. They do not exist BETWEEN databases because a single engine must control the updates to both sides of a relationship.

 

[The_Doc_Man]

No dispute, Pat. However, consider that even Access can have more than one DB file open at the same time - a split back-end as a workaround for the 2GB limit in a single BE file. A single DB engine runs the operation there, but you still cannot do a cross-file relationship, can you? (I know you can't and it has to do with where the relationship is stored.)

If I recall correctly, with ORACLE you CAN establish cross-file relationships because ORACLE supports multi-file "containers" that can even be on different disks. I ran an ORACLE personnel DB for the U.S. Navy that spanned 10 disks x 16 GB/disk. Their relationships don't look the same as Access relationships, but they exist. (I went online to verify that point.)

At the other extreme, I think SQL Server has more trouble with cross-file relationships and I wouldn't suggest otherwise.

Therefore my point was only that Access cannot build relationships across files, a narrow statement intended as such. You are free to disagree with your interpretation of what I said.