Is this possible? (1 Viewer)

[442reign]

Hey guys. I'm new here. I was working on a sales pipeline for a company with about 6 employees. The pipeline has 11 stages. Each employee's sales are updated to the pipeline. I've created queries for each employee adding up their sales in each stage. I would like to somehow display these totals in a single report or form. Is this possible? I tried using the builder and select queries to do it. It worked out for two employees but I got a query too complex when I tried to add the third query to the record source. I feel like there's a much simpler way to do this. Any help is appreciated thanks. I'm not very good at SQL as a disclaimer

 

[Gasman]

I would expect you would group by employee, stage and year perhaps?
Then just show that on a form/subform

 

[442reign]

When an employee makes a sale they fill out a form with the employee that made the sale, the stage that the sale is at, the amount amongst other things. The stage can be continuously updated as long as the deal hasn't been closed yet. I can get a variety of reports but I have been trying to get a form that I can display the names of the employees on one column, the stages of the pipeline on another and the amount each sale made by each employee inside. Displaying these amounts is what I'm having trouble with. That's when I ran into the problem I mentioned above

 

[Uncle Gizmo]

I've created queries for each employee

Assuming you are new to database construction it is very likely that you have constructed it in a similar way to an Excel spreadsheet. This means you will have separate columns for each employee and the like. If that's the case, then that would definitely make it difficult to progress further.

If you think this could be the issue then you might want to read up about it in my blog here:-

Excel in Access - Problem & Solution!​

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

If you discover that you have Excel like Fields, storing information for each employee in a separate field in the table, and if you're not too advanced with your database construction there are ways of turning your "Excel" like table into something more useful in the MS Access world. I have a free tool which allows you to transpose this erroneous data into an MS Access format.

EDIT:- I have attached a copy of the latest version of the "Nifty Transpose Tool" which helps people cure the problem they create for themselves when they build their database like an Excel spreadsheet...

 

[Mike Krailo]

The problem is most certainly table structure. You should have one table that has all sales no matter what stage they are in. One of these columns will be Stage. Now the report query is just grouped on stage first, then totals per employee per stage. This all has to come from your table that has all the sales in it. Does that make sense?

 

[mike60smart]

Hey guys. I'm new here. I was working on a sales pipeline for a company with about 6 employees. The pipeline has 11 stages. Each employee's sales are updated to the pipeline. I've created queries for each employee adding up their sales in each stage. I would like to somehow display these totals in a single report or form. Is this possible? I tried using the builder and select queries to do it. It worked out for two employees but I got a query too complex when I tried to add the third query to the record source. I feel like there's a much simpler way to do this. Any help is appreciated thanks. I'm not very good at SQL as a disclaimer

Hi
Can you upload a screenshot of your Relationship Window?