I need help in making a report for Access 2016

[xboxown]

Let me explain to you first what I am looking for. In the report there are Company information which is linked to Work order and for each Work Order there is/are Work Order Items. So let us say we have four work orders in the screen so to speak they have the following information:



Work Order ID Buyer Name (Company Information) Order Date Entered By
137959 Microsoft Inc 25-aug-2017 TC
137949 Apple Inc 25-aug-2017 FD
137999 Microsoft Inc 25-aug-2017 AD
138921 Commodore Inc 25-aug-2017 FD

I want to generate a report like this

Customer # Of Orders BDFT $ Average BDFT #
--------------------------------------------------------------------------------------------
Microsoft Inc 2 23303 22222 $/BDFT <-- easy
Apple Inc 1 3948 99282 $/BDFT <--- easy
Commodore Inc 1 928 3945 $/BDFT <--- easy

Now from above Customer information is obtained from table called tblCompany, # of orders is obtained by the amount of the same company entered in that same day, finally BDFT and $ are both obtained from the table called tblWorkOrderItem. Now the report have to be generated from the current date where DateCreated = Date(); <-- Problem I have with this it doesn't display all the records from this current date. When I look at the Work Order screen it shows that today there have being entered 15 records in the SQL query it only shows 10 records. Why 5 records are missing? It is not working.


This how my report design look like in the screenshot below





It is not coming out correctly. I am not getting the count and I don't know how to do it from the querySummary which have multiple tables joined together. I need help in this please! Can someone help me how to make this work! I will show you how it looks displayed


As you can see compared to what is in the record down below the result is incorrect



In the record there are 16 records of 25-august-2017 because we are right now the 25th of august.....and as you can see (I have highlighted it) and replaced the actual data with this dummy data. Observe Wo No 137953 and 137951 these two are Microsoft Inc (false name for security and privacy reasons) it should say 2 and not those jibberish number above.

Please can someone help me fix this and do it right and get the correct date count? I don't know why it keeps displaying missing count of 4 records when I specify a date condition query.

Thanks in advance.

[aytee111]

After all your hard work, and the great explanation, I have to tell you to forget about the report. It is purely a display of a query and the wizard will create it for you. It is not where the problem lies, which is with your query.

Tell us about your query, post the SQL (maybe even the database with some sample data), tell us what the query does and what you would like it to do.

[xboxown]

SELECT qryWorkOrderItem.WorkOrderItemID, tblWorkOrder.buyerID, tblCompany.CompanyName, qryWorkOrderItem.BoardFeet, qryWorkOrderItem.LineTotal, qryWorkOrderItem.UnitQuantity, qryWorkOrderItem.UnitPrice, qryWorkOrderItem.Tax1, qryWorkOrderItem.Tax2, qryWorkOrderItem.Tax3, qryWorkOrderItem.TotalBoardFeet, tlkpWorkOrderType.WorkOrderTypeID, tblSeller.Image, qryWorkOrderItem.UnitPrice, qryWorkOrderItem.Quantity, tblWorkOrder.PrimaryDiscount, tblWorkOrder.Shipping, qryWorkOrderItem.PrimaryDiscountTax1, qryWorkOrderItem.PrimaryDiscountTax2, qryWorkOrderItem.PrimaryDiscountTax3, qryWorkOrderItem.[BoardFeet]*[Quantity] AS TotalBF
FROM tlkpWorkOrderType INNER JOIN (tblAccount RIGHT JOIN ((tblSeller INNER JOIN (tblCustomer AS BuyerCompany RIGHT JOIN ((qryWorkOrderItem RIGHT JOIN tblWorkOrder ON qryWorkOrderItem.WorkOrderID = tblWorkOrder.WorkOrderID) LEFT JOIN tblCustomer AS ShipToCustomer ON tblWorkOrder.ShipToID = ShipToCustomer.CompanyID) ON BuyerCompany.CompanyID = tblWorkOrder.BuyerID) ON tblSeller.ID = tblWorkOrder.SellerID) LEFT JOIN tblCompany ON tblWorkOrder.BuyerID = tblCompany.CompanyID) ON tblAccount.AccountID = tblCompany.AccountID) ON tlkpWorkOrderType.WorkOrderTypeID = tblWorkOrder.WorkOrderTypeID
WHERE (((tblWorkOrder.DateCreated)=Date()));

Above is my query. What it does now is it does this:

It displays all the workorderitems for the work order which means if I have a company called Microsoft Inc for example and there are 24 items for Microsoft inc it will display CompanyName Microsoft Inc 24 times because of the relationship in the query above. This is great for calculationg total boardfeet and for calculating total cost and so on...but in the work order front end as you see in the previous post I posted in the screenshot Microsoft was entered only 2 times.

So in the report I want it to say Microsoft Inc have total entered 2 times with the the amount of total boardfeet and total cost from the query above. I don't know how to go about doing that!

Thanks in advance. I mean I can skip the total amount entered and have my problem solved because the query above does this already but I can't. I need to also calculate how many different work orders entered per company. Another issue I am facing is when the query checks for the date it does not display all the records for that date.

Let us say int he front enter I have entered 25 records for 25-august-2017

The query only returns 20 records or 18 records from the 25 records. There are missing records and I don't understand why it is doing that.

[aytee111]

One of the inner joins may account for not all 25 records being displayed. Check the records that are missing and make sure that they have each join set up correctly.

Every record in the query will show the same company name, buyer, etc - all these fields will be repeated on every row. This is how it should be. Once you get to the report you can make groupings and headers so that the data is separated and displayed in a top-down order with no repetitions.

[xboxown]

Anyways, is it possible you can help me to get the correct thing working. Above is how I want it to look like. Can you help me please?

[aytee111]

I need your database, your query contains many tables and many fields so it isn't possible for me to know what table/query contains which fields just by looking.

Separate the summary, create a separate query and make it a sub-report all by itself. Don't try and put it into the same query/report. Your report here looks simple, not sure why your SQL is so complex. Every record in your query will contain company name, buyer name, total number of orders, total BDFT, total $. Those are the only fields which should be in your query. Create a query with those fields, turn it into a TOTALS query, group by company and buyer, sum the other fields.

[xboxown]

i cannot give you my database and record. I am working for the company and I cannot release the data to the public. It holds sensitive data about our customer information, about our items, prices, detail. This will predge trust, sensitive information and my result in me going to prison if I release these data to the public and additional get fired the same day.

If you could make a dummy data that mimics what I want above and I can just download it and learn from it..that would be great!

[aytee111]

On the other hand, if you make a set of dummy data that mimics what you want then I can download it.

[xboxown]

That is ok. I have solved that problem. However I have another problem and I need your help with it. I will post it here.

In the relationship between tblCompany, tblWorkOrder and tblWorkOrderItem I was able to get the calculation of Line total, average, bdft, total bdft, average bdft etc. Only one thing I am unable to get is total work order. Here is why.

If the query links between tblcompany, tblworkorder and tblworkorderitem it displays all the items for workorderitem but doesn't display the amount of workorder. Let me example.

If there are only three work orders and each work order is company so:

Company A is 1
Company B is 1
Company C is 1

Total companies are three.

Company A have 4 workorderitems
Company B have 3 workorderitems
Company C have 2 workorderitems

If I wanted to count how many companies it would calculate 9 in totals because it calculate the amount of workorderitems and not 3 which is the total companies. My problem is getting the amount of companies and still have the other information intact. How do I go about doing that. Please don't ask me to make dummy data or send you private information or something like that. It is really matter of logical SQL query or coding and I need help in this. Thanks in advance.

[aytee111]

Make it a separate query and join it in.

[xboxown]

k I am unable to get it to working joining but this is what I did so far. I think I am close but I really need your help on this.

SELECT (SELECT TOP 1 Count(tblWorkOrder.BuyerID) As CompanyCount FROM tblWorkOrder WHERE DateCreated=Date() GROUP BY tblWorkOrder.BuyerID) AS CompanyCount, tblCompany.CompanyName AS CompanyName, Sum(qryWorkOrderItem.LineTotal) AS LineTotal, Sum(qryWorkOrderItem.TotalBoardFeet) AS TotalBoardFeet
FROM (qryWorkOrderItem RIGHT JOIN tblWorkOrder ON qryWorkOrderItem.WorkOrderID = tblWorkOrder.WorkOrderID) LEFT JOIN tblCompany ON tblWorkOrder.BuyerID = tblCompany.CompanyID
WHERE (((tblWorkOrder.DateCreated)=Date()) AND ((tblWorkOrder.SellerID)=1))
GROUP BY tblCompany.CompanyName;


OK! As you can see that partially worked. But problem with this it is only returning the 1st record so the 1st line is correct while the rest of the line is wrong. But I am getting close. Is it possible in that sql query where instead of saying top 1 in the where condition it checked for the company name for the current date from the other SQL query as it is looping through the record put that in there in the where condition...count how many times company name repeated for that workorder and have that the returning result. I know that sql query where it sound it is only counting workorder..I can easily have workorder join with tblcompany, to get a better result..pretend the joining of the two table have occured. No problem. My issue in the where condition of the second sql query..how do I put a condition in the where..where it takes the company name from the first sql query?

Thanks in advance.

[aytee111]

You are making this more complex than is needed. Create a totals query, group by company, count ID (or any field) - that is all you need.

[xboxown]

No..that will not work. I will explain why.

There are three tables. One table is called company, the other table is called work order and the third table is called work order item. I need to calculate the line total and total bdft right? If I link these three tables what will end up happening is that say there are two companies and each company have one work order and each work order have three work order items..that will total to 6 work order items. If I link all these queries and say count how many companies it will say six companies instead of 2 companies because of the work order items and I need the work order items to calculate the line total and total bdft.

So I need two separate queries one to calculate the line total and total bdft and one to calculate how many companies there are in the work order and in this case 2 work order or 2 companies and 6 work order items.

So this is why I am coming to you for help! Can you help me on this please?!

[aytee111]

So I need two separate queries

That is what I have been saying since post # 10.