need to track inventory and information for financial reports (1 Viewer)

[moradisndat]

Hi, I hope I am posting this in the right place. I have an inventory database with tables for Inventory Transactions, Purchases, Sales, etc. Everything is fine with that. But my boss wants to be able to pull reports at random to see cash flow. I created some tables that are not related to inventory: Payroll, Expenses, Income (for misc income that is not related to inventory such as commission) Then I realized I can't get the information I need because the tables are not related.

I thought maybe the unrelated tables need to be in a different database. But even if I do that, I'm still not sure how to get the information that I need. I looked at linking the tables, appending records from the inventory db to a table in an external db. Now I'm just totally confused.

This is the information I need for prior months or month-to-date: totals for Purchases, Sales, Payroll, Expenses, Misc Income.

Any help with how that should be done would be greatly appreciated. I'm so frustrated I'd like to just give up but I've already put so much time in that would be such a waste to give up now.

 

[burrina]

You already have the data you need in the existing tables. As for getting your criteria, that is what queries are for and then build a report off that.

 

[moradisndat]

I can run queries on the tables that are related, but I can't include the tables that are not related in the query. If I run separate queries on all the tables, how do I use them in the financial report? Is it possible to use multiple unrelated queries in a report?

 

[jdraw]

To better understand "linking tables" -- that is the underlying business rules/facts of the business -- work through this tutorial. Then apply what you have learned to your own situation. From experience I can assure you that getting your tables and relationships set up/designed to meet your business needs is absolutely fundamental to a useful database application.

Don't look at your current situation like this

I'm so frustrated I'd like to just give up but I've already put so much time in that would be such a waste to give up now.

. Instead look at it as an opportunity to learn better ways -- knowing that what you have isn't working to give you what you need. It hasn't been a waste of time; it's a recognition that something has been missing.

Good luck.

 

[moradisndat]

Thanks for the link jdraw. I remember having to do ERD models in a Bus.DB Systems class but it was a long time ago so it is a good refresher. It looks like Roger Carlson explains it differently from what I recall so it's easier to understand. Thanks also for the encouragement. I'll finish reading up on that article as well as a few others I found on his site and then take another look at my tables.

 

[jdraw]

Good stuff! Lots of people here to help if needed.