Showing all values for two value fields in query

[JC27]

Hi all,

I'm pretty new to Access but have recently tried to get into it, because I want to build a small database to track sales data and sales target achievements.

I have two tables with values that I want to compare: one with sales targets (per month, per product, per customer), and one with sales data (products purchased per order and revenue gained from this). I am now trying to make a query to have the targets per month, per product, per customer, and the revenue per month, per product, per customer side by side and calculate the target achievement percentage by dividing the two. To do this I added the columns for 'month', 'customer', 'product' and 'sales target' from the sales target table, and a sum aggregate for 'sales revenue' from the orders column. I ran the query and it seemed to do the trick, but then I noticed a problem:

There are instances where no target is set for a certain customer/product/month, but the customer did buy this product. Conversely, there are instances where there was a target, but no sales. What happens with my query is that it only shows results in which there was both a target and there were sales, and it leaves out any row where either is missing. I would like to have my query also to show these however, preferably with a 0 in the column for which there is no data. I have tried to solve this problem by creating 'dummy' data in my budget table (i.e. add a row for every month, every customer and every product, and giving it a value 0 if there is no budget). However, this only partially solved the problem - now it will indeed show instances where there were sales but the budget is 0, but still not the other way around, and the dummy data makes the file much heavier.



I realise my description of the problem may not be terribly clear, so I've attached a screenshot of the query for reference. If anyone has any ideas, I'd be more than happy to hear about them.

Thanks!

[ItsMe]

You have a very detailed description of what you have tried and why it does not work. Thank you for this. There could be a couple of reasons why you are not getting the results you expect. The first thing that jumps out at me is the fact that your query is using inner joins, exclusively.

Sometimes, inner joins are necessary. But, this type of join will only retrieve records where there is a match on both sides.

Isolate the tables for your targets and sales. You will want to create a query using these tables and, perhaps, one or two others. Start with a new query to isolate these tables. Now, within the Query Designer, double click the line that represents your Join to launch the Join's Property Window. Adjust the properties to "Include all records from table such and such ...". Test your results until you see the desired results.

Now, it is time to start adding the other tables. It is likely some of the other tables will require an inner join. In order to include some of these other tables, you may need to build a separate Query Object and join that query object to the first one.

[JC27]

Thank you very much for your response!

What makes things complicated for me, is that the "Order Contents" table, which contains the sales numbers, and the "Sales Budget" table, which contains the target numbers, cannot directly be related to each other because the product and customer formats in both tables don't match. There are two or three intermediary tables necessary to map the product codes of the sold products onto the sales target categories, and to map the customer IDs associated with each order onto the customers in the sales targets (this is because sometimes two or more real customers are actually categorised as one and the same customer in the sales targets, so they would need to be merged).

Another point is that the orders are placed in Euros or Dollars, but the targets are in a different currency, so the orders need to be converted via the company exchange rate (which I managed to get to work, so this is not a problem).

For these reasons, I think the tables shown in the image are all necessary to do the conversions that are needed to make the sales and target numbers compatible, and I am not sure I can isolate the tables that actually contain the sales numbers and the target numbers any further. I did think about outer joins as a possible solution, but I must say that I am not sure how exactly I would integrate these into this complicated table structure...

[ItsMe]

I must say that I am not sure how exactly I would integrate these into this complicated table structure

Yes, this is the hard part. If there are issues with the table structure and how the Keys are defined, the issue becomes even harder, if not impossible. I am having difficulties conceiving what the Targets are and how one or several tables define the Targets. So, I cannot understand the relationship between Customers and Targets. Perhaps the best approach, right now, is to create a query that retrieves Target data, specific to a Customer.

I tried doing this and it seems the only way is through an Order. However, I do not believe this is the correct approach when I consider the Business logic, as I understand it.

[June7]

The query shows a circular relationship. The will produce undesirable results.

[JC27]

Thank you everybody for your help!

I think I managed to find a solution that works, although it does require having all the dummy entries in the targets table. I first created a query with the month, customer, and category columns of the sales target table, and added to that the sales data, to map the sales data onto the target categories and customers. Then I created a new query based on this one and the original sales targets table again. Now I could create an outer join between the month, customer and category columns these two tables shared, thereby forcing it to show every entry in the sales targets table with the sales numbers.

[ItsMe]

I am glad to hear you were able to overcome the current obstacle. However, this seems to be more of a "workaround" than a long term solution. It may be beneficial to study some tutorials on Entity Relationship Diagrams and Normalization. Then, farther down the road, when you want to add functionality to your application you will be better prepared.