Access query
I've attached an Access database. It has a master table, a transactions table and a
form. Probably the most important thing to point out is that in the master table,
there is a Series field that contains a single digit followed by an 'x'. What is
meant by the data in that field is that there are '1' series records,'2' series
records, '3' series records, etc. The 'x' just signifies that any value IN THE TRANSACTIONS
TABLE (actually any 3 digits since it's a 4-character field) may follow the first digit.
Now, in the transactions table, there is also a Series field (yes I realize there
are 2 tables with the same field name that don't contain exactly the same data.
Rename them whatever you want. I just put the database together quickly to try to
get an answer.) The series field in the transactions table contains (and accepts upon
data entry) 4-digit values. In regard to that specific field, I'm trying to check the
first digit of the transactions table series field against the first digit of the master
table series field. Now, on to the complete query and why I'm trying to do this:
On the form, you'll see 3 combo boxes. All I'm trying to do is have the user make three
selections (or manually enter values) from/in the comboboxes and have the result displayed in the textbox after combobox 3 is updated.
What result am I looking for? I want all transactions from the transaction table to be summed on the TransactionAmount field where the AccountNumber field value matches the Combobox1 value, the first digit of the Series field matches the first digit of the Combobox2 value, and the SubSeries value matches the value in Combobox3. I want that sum to be subtracted from the OpeningBalance field of the one Master Table record where, like the transactions table, the AccountNumber field matches the combobox1 value, the first digit of the Series field matches the first digit of the combobox2 value, and the SubSeries value matches the combobox3 value.
So, for example, let's say the master table has a record with an Opening Balance of $1,000
and that there are 3 matching transactions table records at $50 each. I want those three
amounts subtracted from the Opening balance and the result displayed in the textbox. In the above example, the result would be $850. That's all I'm trying to do. YES, I realize that
I have to handle instances of when a user might not populate one or more of the comboboxes or whatever. I'M JUST TRYING TO GET THE BASICS WORKING, and then I'll handle user entry errors by giving error messages or preventing processing unless all 3 boxes are populated with valid values or whatever.
Why this type of query you ask? Transaction records in the transactions table are charged
against the master table's opening balance. I'm just trying to allow the user to view the
remaining balance in a particular Series and SubSeries before (s)he attempts to add a new
record that may fail because the balance is too low. By doing it this way, if the balance is
too low in one Series/Subseries, the user may have the option of checking another
Series/Subseries where the balance ISN'T too low. So, I want the textbox to act as sort of an information box. The user goes about the process of entering a record, sees that there isn't much money in the Series/Subseries (s)he was going to charge the record to, checks a different Series/Subseries balance, sees there is enough money in that one, and charges the record to that Series/Subseries instead. That's all I'm trying to do.
In conclusion of this novel, I'm being told that I shouldn't do this through code. If anyone
knows how to do this through table relationships, please let me know. Right now, as you'll see when you make a selection from combobox3 on the form, I have my attempt at it in code, which is not working.
TEST.mdb
form. Probably the most important thing to point out is that in the master table,
there is a Series field that contains a single digit followed by an 'x'. What is
meant by the data in that field is that there are '1' series records,'2' series
records, '3' series records, etc. The 'x' just signifies that any value IN THE TRANSACTIONS
TABLE (actually any 3 digits since it's a 4-character field) may follow the first digit.
Now, in the transactions table, there is also a Series field (yes I realize there
are 2 tables with the same field name that don't contain exactly the same data.
Rename them whatever you want. I just put the database together quickly to try to
get an answer.) The series field in the transactions table contains (and accepts upon
data entry) 4-digit values. In regard to that specific field, I'm trying to check the
first digit of the transactions table series field against the first digit of the master
table series field. Now, on to the complete query and why I'm trying to do this:
On the form, you'll see 3 combo boxes. All I'm trying to do is have the user make three
selections (or manually enter values) from/in the comboboxes and have the result displayed in the textbox after combobox 3 is updated.
What result am I looking for? I want all transactions from the transaction table to be summed on the TransactionAmount field where the AccountNumber field value matches the Combobox1 value, the first digit of the Series field matches the first digit of the Combobox2 value, and the SubSeries value matches the value in Combobox3. I want that sum to be subtracted from the OpeningBalance field of the one Master Table record where, like the transactions table, the AccountNumber field matches the combobox1 value, the first digit of the Series field matches the first digit of the combobox2 value, and the SubSeries value matches the combobox3 value.
So, for example, let's say the master table has a record with an Opening Balance of $1,000
and that there are 3 matching transactions table records at $50 each. I want those three
amounts subtracted from the Opening balance and the result displayed in the textbox. In the above example, the result would be $850. That's all I'm trying to do. YES, I realize that
I have to handle instances of when a user might not populate one or more of the comboboxes or whatever. I'M JUST TRYING TO GET THE BASICS WORKING, and then I'll handle user entry errors by giving error messages or preventing processing unless all 3 boxes are populated with valid values or whatever.
Why this type of query you ask? Transaction records in the transactions table are charged
against the master table's opening balance. I'm just trying to allow the user to view the
remaining balance in a particular Series and SubSeries before (s)he attempts to add a new
record that may fail because the balance is too low. By doing it this way, if the balance is
too low in one Series/Subseries, the user may have the option of checking another
Series/Subseries where the balance ISN'T too low. So, I want the textbox to act as sort of an information box. The user goes about the process of entering a record, sees that there isn't much money in the Series/Subseries (s)he was going to charge the record to, checks a different Series/Subseries balance, sees there is enough money in that one, and charges the record to that Series/Subseries instead. That's all I'm trying to do.
In conclusion of this novel, I'm being told that I shouldn't do this through code. If anyone
knows how to do this through table relationships, please let me know. Right now, as you'll see when you make a selection from combobox3 on the form, I have my attempt at it in code, which is not working.
TEST.mdb
kmslogic
Is the voice telling you to not do this in code something you can ignore?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.