Ad Hoc Query Writer (1 Viewer)

[KeithG]

I am creating a db at work that basically is a user interface to write a pass through query for our AS400. I save the query definitions to various tables so that it is stored an will be able to be modified. I have an elements table, a Criteria table, and a joins table (along with other tables that store the element and table information from AS400). I am having issues with storing the join information for the queries. The table schema is below

tbl_Query_Joins
JoinID
QueryID
ParentEntitityID
ChildEntityID
PE_AttributeID (Parent Entity Attribute ID)
CE_AttributeID (Child Entity Attribute ID)


This is my current structure for the joins table. But I am having problems joining more than two tables. Can anyone suggest a better schema for the joins table. I actually don't think this table is properly normalized because I feel like ParentEntityID and ChildEntityID along with PE_AttributeID and CE_AttributeID are a violation of first normal form. Would you agree?

With my current structure I was thinking I could use my JoinID from the first join as the parentEntityID for the second join. Any ideas.

 

[The_Doc_Man]

Look at the system tables (use Tools >> Options to get to the part where you make hidden and system tables visible.)

DON'T MODIFY THE TABLES. However, insights to your problem should be visible in the MSySRelations table that defines relationships. This information is how Access creates an implied join when you start the Query Grid and name two tables that are related. I might have gotten the MSys name wrong, but you'll know it when you see it.

 

[KeithG]

Good idea, I should have thought of that myself. I just reviewed the table structure and it is very similar to mine. I decided that I will just use the JoinID as the PatentEntity.

 

[KeithG]

Actually this table stores the information for relationships between tables, not joins in queries. But the table structure still works well. I just had to program the function to build the second relationship off of the first one. I will post the db when I am done creating it. You can actually use the db to query any ODBC database as long as you enter the connection string and tables schema's into the database.