Db-why-not
Registered User.
- Local time
- Yesterday, 20:29
- Joined
- Sep 17, 2019
- Messages
- 159
I have 2 tables (Q1_Workload and Q1_audit) that are related one to one by lpersonID_Q1 field. Q1_workload table has more records in it. Audit table doesnt have all matching records. I made a Left Join query with the 2 tables, called it pending_auditlist. pending_auditlist query shows all the records in workload table and all matching records in q1audit table even if there isnt a matching record, just shows null for matching records that dont exist in q1_audit table. User will use a continuous form to select a record from a list that uses my pending_auditlist query data. I have a form that when the user select the record, click on lpersonid_q1 field and it browseto another form, q1_dataentry form. This form has a main form and subform. The main form shows fields from my q1_workload table. The subform shows fields from my q1_audit table. When the user browseto this q1_dataentry form it should automatically open the correct lpersonid_q1 record in the main form and subform. Its not working for records that don't exist in the q1_audit table though all the time or even records that exist in both tables beyond a certain number. lpersonID = 102-680 doesn't exist in q1_audit table , it only exists in q1_workload table, but it will still open , browseto works for these records. Then when I try to browseto lpersonID =735 it no longer works. 735 exists in q1_workload but not in the q1_audit table. It seems like it only works up to a certain number. Even numbers that exist in both tables past 734 won't open and browseto properly on my q1_dataentry form.
Some of my tables exist on a sql server. Q1_workload exist on sql server. Q1_audit exists in my access file. Eventually I will have q1_audit file on the sql server but for now its in access, its just a dummy table to test the forms with.
The q1_workload table has over 500,000 records in it. my q1_audit table only has 148 records in it.
In my q1_audit table lpersonID_Q1 is number data type long integer
In my q1_workload table lpersonID_Q1 is a Autonumber long integer, this is also the primary key
In my q1_audit table there hasn't been a primary key assigned to it but I have iQaID that could be assigned the primary key, then lpersonID_q1 can be the foreign key.
I'm just wondering what is causing this issue, what can I do to fix it.
I attached some pictures of my forms showing the pending audit list form page that user selects specific lpersonID_q1 to go to . Then I showed q1_dataentry form and how it looks when it works correctly and navigates to correct record and how it looks when it doesnt work. It just shows 0 value for lpersonID_q1
Some of my tables exist on a sql server. Q1_workload exist on sql server. Q1_audit exists in my access file. Eventually I will have q1_audit file on the sql server but for now its in access, its just a dummy table to test the forms with.
The q1_workload table has over 500,000 records in it. my q1_audit table only has 148 records in it.
In my q1_audit table lpersonID_Q1 is number data type long integer
In my q1_workload table lpersonID_Q1 is a Autonumber long integer, this is also the primary key
In my q1_audit table there hasn't been a primary key assigned to it but I have iQaID that could be assigned the primary key, then lpersonID_q1 can be the foreign key.
I'm just wondering what is causing this issue, what can I do to fix it.
I attached some pictures of my forms showing the pending audit list form page that user selects specific lpersonID_q1 to go to . Then I showed q1_dataentry form and how it looks when it works correctly and navigates to correct record and how it looks when it doesnt work. It just shows 0 value for lpersonID_q1