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
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.