Query not working as expected - No relationship is being found

[Vaslo]

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!

[June7]

Not sure I understand. January is still January, even if it is the first month of calendar year but fourth month of fiscal year. Don't see need for table. A calculated field in query can assign the 'fiscal month' number to a record.

One such expression, assuming fiscal year is Oct-Sep:
FiscalMo: Choose(Month([datefield]), 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3)

Also consider:
FiscalQtr: Choose(Month([datefield]), 2, 2, 2, 3, 3, 3, 4, 4, 4, 1, 1, 1)

Calculating fiscal year is a little more complicated. Assume the 'fiscal year' is the second year:
FiscalYr: Year([datefield]) + IIf(Month([datefield])>9, 1, 0)

[Vaslo]

Ok I lost some of my clarity. First off, sorry - I forget that some folks don't live in my confusing world. Fiscal months MAY be called Jan, Feb, etc, but may have different dates in them to make it simpler to compare same months year on year. So for example, Jan Fiscal is our 4th month (not April.) In Addition, Jan Fiscal has 3 days from Dec in it. It's all over the place so it's just easier to pull a table from the system that matches a date to the fiscal month it's in.

Let's forget about that for a moment. What about keeping tables unadulterated while doing most of your work from queries that have calculated fields? Can I use those queries to do more queries? or is there a better way? Again, my main issue is that my query built from two queries doesn't use my one to many links in my date table.

Thanks.

Not sure I understand. January is still January, even if it is the first month of calendar year but fourth month of fiscal year. Don't see need for table. A calculated field in query can assign the 'fiscal month' number to a record.

One such expression, assuming fiscal year is Oct-Sep:
FiscalMo: Choose(Month([datefield]), 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3)

Also consider:
FiscalQtr: Choose(Month([datefield]), 2, 2, 2, 3, 3, 3, 4, 4, 4, 1, 1, 1)

Calculating fiscal year is a little more complicated. Assume the 'fiscal year' is the second year:
FiscalYr: Year([datefield]) + IIf(Month([datefield])>9, 1, 0)

[June7]

Yes, queries can be used like tables in other queries.

Okay, that is different - never seen a 'fiscal month' defined like that. Now I can see benefit of table.

Need something to analyze - provide query sql statements or attach db. Follow instructions at bottom of my post.

[Vaslo]

June,

Before I go through trying to scrub data to remove confidential items (which the transactions pretty much are...) let me ask this question. Let's say I have two tables linked and I am able to enforce referential integrity on the one to many, etc. When I create a query from a query, do I need to redefine the link, or should it carry through.

Sorry, I know you'd want to analyze but maybe I'm missing something fundamental. I assumed that once I've joined the two tables, all the queries, subqueries, etc should follow through and use the relationships. I just don't know why when I link the dates it wouldn't work - seems so basic.

[June7]

I don't think so but I've never really checked it. I often don't bother with setting Relationships. So if you are experiencing that query does not carry over the relationship link, that seems to be confirmation.

[Vaslo]

So that begs the question, how do you relate two tables? Or is that set when you bring it over from another source? Im missing something fundamental I think.

[June7]

Can relate tables in Relationships builder.

Can also manually set links in query design (that can be entirely different from links set in Relationships).

Bring over from what 'other source'?

[Vaslo]

You said that you don't bother setting relationships, so I was wondering how you linked two tables. If you don't bother with setting relationships, how is it done for you automatically then (if that is what you are implying?) When I said from another source, I meant from, say, SQL Server and then the relationships were already there and Access just picked them up. Just curious - I feel that I have to set these relationships everytime I add a new table.

[June7]

If db is split (Access frontend and backend), Relationships set in backend do carry forward into frontend query builder. Then I tested a query used in another query - the link did carry through as long as the required PK/FK field was present in the first query.

I have no idea if this holds true with SQL backend.

If I don't set Relationships then I manually set links in query builder.

[Vaslo]

Thanks for the quick responses. I think that this problem is going to be too complex to solve, and though the better solution is to have a date table, the query of a query is screwing something up. I just made a more complex query with the calendar date as another column. it will work for this but gets away from the principles that you experts espouse in normalization, as the date table would have been helpful in other queries. Perhaps the right way to do it is to set the table that gets imported to already have this work done.

Oh well - this database business is tough. Sometimes I'd like to leave it to the experts...

[ItsMe]

Are you doing a 4 4 5 calander? Is it for inventory or finance?

I don't think storing calcs for financial reports is that bad. It just depends.

You may be over thinking the relationship thing. If you are creating a query object, it may be best to think in terms of Joins. It is not mandatory to create a relationship in the relationship window.

[Vaslo]

Are you doing a 4 4 5 calander? Is it for inventory or finance?

I don't think storing calcs for financial reports is that bad. It just depends.

You may be over thinking the relationship thing. If you are creating a query object, it may be best to think in terms of Joins. It is not mandatory to create a relationship in the relationship window.

Sounds like you know exactly what I mean-yes it's a 4-4-5 financial calendar. I always thought relationships implicitly handled joins, is that not the case? I'm actually using it to calculate rebates for a customer, which are based on sales. Finance tracks it fiscally but the customer does on a calendar, so I wanted it all in the same place. What's your thoughts here?

Also, what kind of resources have you come across with doing P&L type stuff in Access? We are trying to do a lot of that but fairly novice. Any advice you have is appreciated. Your mention of the 4-4-5 gives your knowledge away

[ItsMe]

... I always thought relationships implicitly handled joins, is that not the case?...

Since I cannot explain, in a concise way, all of the differences between a Join and a Relationship, I could probably stand to learn a little bit more about them. From MS Access' perspective, Relationships are managed from the Relationships Window and help to enforce data and referential integrity within the DB. From a Relational Database perspective, it can be a line penciled onto a piece of paper that connects two entities.

So if you create a Join from within the Query Builder, you are, in essence, creating a Relationship. However, nothing will appear in the Relationship Window within Access. On the other hand, you can create a relationship within the Relationship Window and have it appear as a Join within the Query Builder. June mentioned this before. I believe this is a default setting within Access. If you do not want your Relationships, that were created within the Relationships Window, to appear within the Query Builder, you can adjust the settings within Options.

As for accounting, I know some. I am not a CPA or even close to being one. It is a general rule to depend on Sage (Peachtree) or QuickBooks. Professional developers can create custom apps to use either of these off the shelf solutions as Back Ends to their applications. A custom application will almost always have some sort of database, even if it is one table. However, tables that manage finance are almost always within Sage or QuickBooks.

Having said that, it can be helpful to manage Invoicing from a custom app, passing transaction data to the accounting software in real time.

My understanding is that P&L's are managed by Peachtree or QuickBooks. I believe there are options within Peachtree and QuickBooks for 4-4-5 finance. I have dabbled a little in 4-4-5 for Inventory. I guess there is a need to match Inventory with Finance.

[CJ_London]

Also, what kind of resources have you come across with doing P&L type stuff in Access?

P&L stuff actually covers a very wide area - it needs to manage P&L reporting, Balancesheet, cashflow, asset management (depreciation etc), nominal, sales and purchase ledger, credit control, some element of stock control, invoice, purchase orders, cash management, journals etc. Not to mention sales tax calculation/VAT etc. Believe me (I am an accountant - FCMA/CGMA) it is not worth the effort, buy quickbooks or there are a number of free products out there (no idea if they are any good) which may meet your needs.

I know Sage very well and you can link to their data through an odbc connection, but you will need to learn how their data structure is designed and also be aware you cannot update their data from Access. What you can do is export a .csv file to a Sage specification to upload into Sage. Of course as more stuff moves online, this gets harder to do.

However if you want to talk about management accounts, Access is your boy. You can create P&L's easily enough, balance sheets with a bit of effort, but it is much more important to know your cashflow, actual v expected profit margins per product/service, wastage, efficiency, costs per hour/day/process, etc. Of all of these, the P&L tells you the least and can often be misleading, the balance sheet tells you where the bodies are buried and the rest of it tells you why or how.