Can this be dun? (1 Viewer)
- Thread starter Sm0ke
- Start date
- Local time
- Today, 16:49
- Joined
- Jan 23, 2006
- Messages
- 15,379
I looked at your database. There are several queries with similar names?? There are many tables without relationships??
I still don't know what you are trying to do.
I suggest you give us a specific example in plain English to follow - Telling us what is input, any parameter values, and what you expect a the result.
I still don't know what you are trying to do.
I suggest you give us a specific example in plain English to follow - Telling us what is input, any parameter values, and what you expect a the result.
I cleaned up the queries, I do not know what you mean about the relationships. Here is what I am trying to do, Have a Table “Brown Wire CT” we list all of the teat wire in the Brown Wire CT Query it auto calculate the correction factor and I wont to do the same thing with Table “K Type Fluke” but add the date of test and when it is due, and I have done that. What I need help with is pull the correction factor from “Brown Wire CT” and “K Type Fluke” in to “FreezerSAT1Query” base on test date and what Fluke and brown wire was used.
I would like to thank you again for your help. Another problem can up and I hope you can help again, I made the form but it will not let me update or add to it. I know that I did not do something. To make this simple I zip and attach the Database.
- Local time
- Today, 16:49
- Joined
- Feb 19, 2002
- Messages
- 43,203
The form is not updateable because the query is not updateable. That is caused by the three table join where you are joining on non- PK to FK relationships.
Step away from the form and go back to work on your schema. Using ID as the PK name for every table makes the relationships harder to see. Common practice is to use some part of the table name as the ID field and then use the same name as the FK field whenever possible. The only time you won't be able to do this is if you have more than one relationship to the same table. For example, if you have a Order table with a BillTo and ShipTo address. Both addresses will have state fields. One will be named ShipToState and the other would be named BillToState and they would point to State in the state table.
Once the names are all sorted out so they make sense, it will be easier to see how the tables relate to each other and then to create relationships which enforce RI using the relationship window.. Things like Equip to FRID are simply unnecessarily confusing. Same with TEquip to InstrumentID. Part of your confusion is not understanding how to properly utilize autonumbers. When a table contains an autonumber, it should be the primary key. There is no other reason for adding an autonumber to a table. If you have a business rule such as InstrumentID must be unique, then enforce that with a unique index. But use the autonumber in ALL relationships and joins. In addition to the FK/PK confusion, you have characters in the names that violate programming rules. All objects should use ONLY upper/lower case letters, numbers, and the underscore. NO other characters should be used including spaces and dashes.
In the query itself, you have dLookups() that reference a table you are joining to the query. That makes no sense at all. Removing the FreezerID Query may fix the updateablity problem. But it is better to fix the root problem and fix the schema so you can join the tables correctly.
And finally, you have expressions in the query that make no sense -
CRDP: IIf([CRD]>=-2<=2,"Pass","Fail")
Is an example. This will always return "Pass" because -2 is <= 2. I think what you intended was:
CRDP: IIf([CRD]>=-2 AND [CRD] <=2,"Pass","Fail")
When creating compound expressions, you MUST repeat the object name and include a relational operation such as AND, OR, <>, whatever to indicate how the two expressions should be evaluated..
Once you clean up the schema, start a new thread and we'll help get the query working if you need us.
Step away from the form and go back to work on your schema. Using ID as the PK name for every table makes the relationships harder to see. Common practice is to use some part of the table name as the ID field and then use the same name as the FK field whenever possible. The only time you won't be able to do this is if you have more than one relationship to the same table. For example, if you have a Order table with a BillTo and ShipTo address. Both addresses will have state fields. One will be named ShipToState and the other would be named BillToState and they would point to State in the state table.
Once the names are all sorted out so they make sense, it will be easier to see how the tables relate to each other and then to create relationships which enforce RI using the relationship window.. Things like Equip to FRID are simply unnecessarily confusing. Same with TEquip to InstrumentID. Part of your confusion is not understanding how to properly utilize autonumbers. When a table contains an autonumber, it should be the primary key. There is no other reason for adding an autonumber to a table. If you have a business rule such as InstrumentID must be unique, then enforce that with a unique index. But use the autonumber in ALL relationships and joins. In addition to the FK/PK confusion, you have characters in the names that violate programming rules. All objects should use ONLY upper/lower case letters, numbers, and the underscore. NO other characters should be used including spaces and dashes.
In the query itself, you have dLookups() that reference a table you are joining to the query. That makes no sense at all. Removing the FreezerID Query may fix the updateablity problem. But it is better to fix the root problem and fix the schema so you can join the tables correctly.
And finally, you have expressions in the query that make no sense -
CRDP: IIf([CRD]>=-2<=2,"Pass","Fail")
Is an example. This will always return "Pass" because -2 is <= 2. I think what you intended was:
CRDP: IIf([CRD]>=-2 AND [CRD] <=2,"Pass","Fail")
When creating compound expressions, you MUST repeat the object name and include a relational operation such as AND, OR, <>, whatever to indicate how the two expressions should be evaluated..
Once you clean up the schema, start a new thread and we'll help get the query working if you need us.
Last edited:
Similar threads
Solved
Age Deomgraphics