Hello. I am trying to complete something simple in Access, yet I'm finding issues. Let me describe my process in some detail.
I have two tables. One table called Transaction Data is every transaction for 5 years. The other is a simple table that has each day in the last 5 years next to the fiscal month it is in. Of course, our fiscal months don't match with the calendar month. I link Transaction data dates to my date table to tell me which fiscal month each transaction falls into. Makes sense, right?
Following what people say is good database policy, I don't touch these tables except to update with new data. Instead I have two queries from these two tables that I do the calculated columns as needed. One is a mirror of the Transaction Data table, that adds some calculated columns (for currency stuff), and one is a mirror of the date table, which uses some formulas to give me calendar month in the same table as the fiscal month, etc. First, this is the right thing to do, yes?
Now my problem - when I try to use these two queries to third query using dates and transactions, there is no enforcement of the relationship setup in the parent tables. So for example (and my exact issue) is that when I do a query to give me all of the Transaction Data from the Transaction QUERY and try to put a column in for the Calendar Month using the date QUERY (not tables), it doesn't work and just ends up copying everything for each date. Am I doing this correctly? Is there a better way??
Thanks!