Using cartesian product query in MS Access to create checklist?

Question

I have a table with a list of accounts. I want to create a checklist for the monthly statements that have been entered and reconciled.

Can I create a Cartesian product query that generates a list of month/years as one field and somehow store a boolean value in a table for each month per account?

Example: I check "reconciled" and a record would be stored in a table with a one-to-one relation to the query, so that I don't have to manually create blank rows for each account/month combo that hasn't yet been reconciled yet I would still be able to generate a list of every account/month regardless of true or false state.

...if that makes any sense...

other words: store a true boolean value only for the months that have been reconciled for each account, then join it with a query that would essentially generate rows for the months that have not been done yet, while being able to update the table to mark more months as done.

I have been able to do it but the recordset is not updateable because of the CPQ.

Is this going to require a form and vba? I am hoping for a more graceful solution if there is one.
Thanks for any insight. It's ok if you want to tell me I am off my rocker :) I am self-taught and won't be offended. lol

Answer 1

That's a great idea and one of the few non-accidental applications of a cross/cartesian join. Unfortunately, as you have already discovered what works well for a query does not necessarily apply as a persistence mechanism.



Unfortunately I can't think of any elegant way to do this in Access, and the ways I can imagine are major hacks, which I think is a big mistake in Access. Access + hacks lead to some of the most horrible, unmaintainable things I have ever had the misfortune of inheriting.



I think you will find most mainstream programming languages and their supporting frameworks will handle this task quite well. In essence, an ORM will enable you to create objects on the fly, based on the results of a query and then save any or all of those objects based on whatever critera you decide.



So, you would build a query that produces the combinations of month/year (date) and account numbers and joins that to your existing data store (which also has the boolean). When the boolean is enabled, the ORM would know to save that record. When one was unchecked, it would know to save that change.



Hopefully you get the idea. It's more than I can go into detail on this response. Since you already know Access, you are half-way to using a more full-featured technology like Java or .NET, either of which could handle this task swimmingly.

Answer 2

Cartesian Query

Answer 3

If the transaction in the second database is approved, and it is being approved again, then you need to have a reference link between the 2 databases so that the original list of transactions can exclude records that already exist in the second database. By not doing this, then you are allowing multiple users to approve the same transaction. You need to prevent that from happening. Even if 2 users are approving the same transaction at the same time, one of them must execute first, and the second one must reject. To do that, you need to change the Select clause of your INSERT statement to LEFT JOIN to the destination table WHERE the transactionID is null. INSERT (.......) SELECT ........ FROM Array a LEFT JOIN Target t ON t.ID=a.ID WHERE t.ID IS NULL That will exclude any transaction that already exists in the target table. It sounds like you have an awkward system design there. You should be able to use replication or log shipping to keep the second database current. Good luck.