Limit query to a total summed number (counting parts used).

shrimpfork
shrimpfork used Ask the Experts™
on
I'm not sure how to approach this one.  I am working on a query (soon to be a report) that is will be give me a percent usage of a part we use every day listed by project.  The problem is that I need to be able to limit this query to provide me the percent usage over a given number of parts starting from the current day going back in history.
(For example, this report needs to provide me the usage by project for the part "PRN-2" over the last 30,000 parts used so that I can properly assign billing for this part.)  How can I limit a query to show me the records for a given maximum running sum?  Is this the right way to approach this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
HainKurtSr. System Analyst

Commented:
a sample source & result data can help us to visualize what you are trying to do here...

Author

Commented:
capricorn1,
I understand the running sum portion, but I did not see where it explained how to limit the number of records returned in a query to a running sum total.  Say like a list of the last 30,000 parts used?
HainKurt,
I don't have anything put together yet to post, otherwise I would.
 
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
OK Experts, I have put together some data to play with.  Attached is a sample database with a table (tblPartUsage) that I made from a query in my main DB that shows a quantity of parts (WedgeClips & Gussets) used per panel and when the panel was built.  I have then started a query to sum up the parts used by project and sort them descending from the most recent dates.
This is what I need to eventually do:  Create a report that allows me to split up an invoice and assign the costs to the projects that used the part.  I am going to have the user open a form (frmPartUsage) enter the invoice amount $ and the number of parts ordered (n) and then the report kick out a list of projects with a percentage dollar amount for the last n number of wedge clips.
(Note: Let's just focus on the Wedge Clip information for now.  I can apply the same principal to the gussets when we get this figured out.)
I need help to get a query to show only the last n number of wedge clips used by project, then I think that I can take it from there.

PartAssignment.mdb

Author

Commented:
Thinking about this more, I guess that I can used VB code to run through the records (decending order by date) to get a cutoff date for the count and then create a query with that cutoff date.
 

Author

Commented:
Sorry, I noticed that the data in the table was not correct.  Pelase refer to the revised attached sample DB.
PartAssignment.mdb
Top Expert 2016
Commented:
try this query, to get all projects that  have used more than 30000 parts
SELECT tblPartUsage.ProjectID, Sum(tblPartUsage.WedgeClips) AS SumOfWedgeClips
FROM tblPartUsage
GROUP BY tblPartUsage.ProjectID
HAVING (((Sum(tblPartUsage.WedgeClips))>=30000));

Author

Commented:
capricorn1,
I'm not sure if you followed me.  I'm not looking for a list of projects that have used more than 30000 parts.  I need to divy up an invoice per project usage.  (See my post at 02/01/10 01:15 PM)  Let me know if I misunderstood your comment.
Top Expert 2016

Commented:
i just read this
<(For example, this report needs to provide me the usage by project for the part "PRN-2" over the last 30,000 parts used so that I can properly assign billing for this part.)  How can I limit a query to show me the records for a given maximum running sum? >

Author

Commented:
Yes I was trying to explain that I needed to divy up the last 30,000 parts used by project.  In otherwords, what projects used the last 30,000 parts from our stock and show how much they used.

Author

Commented:
OK Experts,
I have been working at this and have a down and dirty version of what I would like to do.  The attached database is only a sample set of the our main DB and is not very prety yet.  It will allow me to input a dollar amount from an invoice and the number of parts that was ordered for that dollar amount.  The output report will give me a summary of what project used that part and how much of the invoice amount they used.
Question:  Is there a better way to do this?  (It seem like there should be an easier way to do this.)
Note:  This sample database includes a section in the code to that sets the minumum dollar amount allocated to a project.  In otherwords, it will only divy up an invoice to projects that used more than $25 worth of parts.

PartAssignment.mdb
Most Valuable Expert 2012
Top Expert 2008

Commented:
Without downloading the database, are you using a SELECT TOP n query?

Example:
SELECT TOP 100 Table1.Field1, Table1.Field2
    FROM Table1;

Author

Commented:
The problem with this is that I don't know where the "top" is in the query.  You would have to download the database to understand.
 
Most Valuable Expert 2012
Top Expert 2008

Commented:
My friend, I don't have Access installed, so I was hoping that I could help you without having to download...if you have a single table, then I would try something like this:

SELECT TOP 30000 tblPartUsage.ProjectID, Sum(tblPartUsage.WedgeClips) AS SumOfWedgeClips
FROM tblPartUsage
GROUP BY tblPartUsage.ProjectID


Author

Commented:
The "top" record level is not going to be the same for every report.  The top number of records is going to be different every time.
Most Valuable Expert 2012
Top Expert 2008

Commented:
Are you looking for a way to calculate the percentage to use for the TOP n value?

Author

Commented:
Yes sort of, but the Top n number of records is a function of the overall sum of the parts and the n value will vary.  My post at 02/01/10 01:15 PM explains this in more detail.
Top Expert 2012

Commented:
This has nothing to do with MS SQL Server and that zone should be removed.

Author

Commented:
I agree, MS SQL is the wrong zone.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Extract the ProjectIDs with a count of 30000+:

SELECT tblPartUsage.ProjectID
FROM tblPartUsage
GROUP BY tblPartUsage.ProjectID
HAVING (((Sum(tblPartUsage.WedgeClips))>=30000));

Save this as, say, qdy30000.
Create another query where you extract the records for those ProjectIDs:

SELECT
  tblPartUsage.PartUsageID,
  tblPartUsage.ProjectID,
  tblPartUsage.Date,
  tblPartUsage.WedgeClips
FROM
  tblPartUsage
INNER JOIN
  qdy30000
    ON tblPartUsage.ProjectID = qdy30000.ProjectID
ORDER BY
  tblPartUsage.ProjectID,
  tblPartUsage.Date DESC;

Now, open this as recordset in VBA and loop through the records adding up for each of ProjectID the values of WedgeClips. Record the date of the first record of the ProjectID and - when 30000 is reached - the date for this record. Now you have the first and last date for the ProjectID and you can feed these dates to a third query to select the records for this ProjectID.

There are several variations to this method including the use of temporary tables but you sure get the idea.

/gustav

Author

Commented:
cactus data,
If I follow you correctly, I think that what you descriped is what I did as I posted with a sample database on 02/04/10 09:49 AM, (ID: 26486879).  I was asking if there was a better way to do what I did in the sample database.
I have been asking for somebody to look at the sample database in comment ID:26486879 and tell me of there was a better way to do what I did in the example.
 
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I didn't locate that example, sorry.

But there is. Collect the data on a regular basis and count up the values - for each record added, calculate the date for the previous record holding the start record of the records now adding up to 30000. That's a pure VBA task which should be pretty fast.

/gustav

Author

Commented:
No problem.  The example is lost in the comments.  I would like to know if there is a way to better my example and I might post it in another question.
 
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Not really. For queries, you need a running sum as I showed and that has to be build record by record (fast read but slower recording) or for the full recordset (slow) record by record - also when you have many records (extremely slow).

The only very fast method is pure VBA using a DAO recordset as described.

/gustav

Author

Commented:
I actually figured out a solution, but wanted somebody to confirm if it was the best way to tackle this problem.  I will need to post the solution in another question to see if there is a better way to handle this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial