How to insert a prompt for a # in a query to calculate against another field in query (1 Viewer)

Lilly420

Registered User.
Local time
Today, 17:23
Joined
Oct 4, 2013
Messages
126
Hello,

I am sure this is simple but I just can't get it and I have tried...I also did look through the threads here but did not seem to find anything on this so what I am trying to do is this...In my query, I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?

Days Available: ([Number of Business Days])-28

Thank you for any help with this...I truly appreciate it.

Lilly:banghead:
 

vbaInet

AWF VIP
Local time
Today, 21:23
Joined
Jan 22, 2010
Messages
26,374
Quite simply:
Code:
Days Available: ([Number of Business Days])-[COLOR="blue"][Enter PTO Days to calculate:][/COLOR]
That will cause a prompt when you run the query.
 

Lilly420

Registered User.
Local time
Today, 17:23
Joined
Oct 4, 2013
Messages
126
Hello,

Thank you so much that did the trick, I so appreciate your help.

I have another problem that maybe you could help me with….

In this same query, I am trying to create new field names based on calculations like below and they work great except for I keep getting prompts for these new fields I have created…is there a way to fix this…?

This is what I have:

On the calculation you just helped me with I am trying to create a new field called “DaysAvailable” and then get that number and I do

DaysAvailable: ([Number of Business Days])-[Enter PTO Days to calculate]

Then from this field I am trying to create another field called “Numberof Weeks” which is taking the "DaysAvailable" number and dividing by 5 to get the number of weeks

NumberofWeeks: ([DaysAvailable]/5)

Then from this field I am trying to create another field called “WeeklyAverage:” which is the number of reviews a person did divided by the “NumberofWeeks” field I created…the “NumberofReviews” is in the query I just gave it a new label…

WeeklyAverage: ([NumberofReviews]/[NumberofWeeks])

Now when I run the query it prompts me for PTO time which is should … then for all three new fields I created...

NumberofWeeks
DaysAvailable
NumberofReviews

I just hit enter and don’t add any data and it brings back the correct data but is there a way to get rid of the prompts--I am doing something wrong just don't know what...:confused:

Thank you.

Lilly
 

vbaInet

AWF VIP
Local time
Today, 21:23
Joined
Jan 22, 2010
Messages
26,374
Most times another problem = another thread but don't worry.

Let me see the SQL statement of the query.
 

Lilly420

Registered User.
Local time
Today, 17:23
Joined
Oct 4, 2013
Messages
126
Hello,

Thank you...is this what you need?

SELECT QryLpReport12ReviewerData.[Review Last Name], Sum(QryLpReport12ReviewerData.[CountOfAudit ID]) AS NumberofReviews, QryLpReport12ReviewerData.[Number of Business Days], ([Number of Business Days])-[Enter PTO Days to calculate] AS DaysAvailable, ([DaysAvailable])/5 AS NumberofWeeks, ([NumberofReviews]/[NumberofWeeks]) AS WeeklyAverage
FROM QryLpReport12ReviewerData
GROUP BY QryLpReport12ReviewerData.[Review Last Name], QryLpReport12ReviewerData.[Number of Business Days], ([Number of Business Days])-[Enter PTO Days to calculate], ([DaysAvailable])/5, ([NumberofReviews]/[NumberofWeeks]);
 

vbaInet

AWF VIP
Local time
Today, 21:23
Joined
Jan 22, 2010
Messages
26,374
The problem here is that NumberOfReviews is still being aggregated. Try creating another query based on the Sum query above and perform your Numberofreviews, DaysAvailable, NumberOfWeeks and WeeklyAverage there in the new query.
 

Lilly420

Registered User.
Local time
Today, 17:23
Joined
Oct 4, 2013
Messages
126
Thank you.

I just put the sum of reviews into its own query and then brought that sum query into the one I have the calculations and joined on reviewers last name (also removing that sum field) and now it does not prompt me for NumberofReviews but it still prompts me for DaysAvailable and NumberofWeek—I am stumped…did I do something wrong?

SELECT QryLpReport12ReviewerData.[Review Last Name], QryLpReport12ReviewerCountExamsSum.NumberofReviews, QryLpReport12ReviewerData.[Number of Business Days], ([Number of Business Days])-[Enter PTO Days to calculate] AS DaysAvailable, ([DaysAvailable])/5 AS NumberofWeeks, ([NumberofReviews]/[NumberofWeeks]) AS WeeklyAverage
FROM QryLpReport12ReviewerData RIGHT JOIN QryLpReport12ReviewerCountExamsSum ON QryLpReport12ReviewerData.[Review Last Name] = QryLpReport12ReviewerCountExamsSum.[Review Last Name]
GROUP BY QryLpReport12ReviewerData.[Review Last Name], QryLpReport12ReviewerCountExamsSum.NumberofReviews, QryLpReport12ReviewerData.[Number of Business Days], ([Number of Business Days])-[Enter PTO Days to calculate], ([DaysAvailable])/5, ([NumberofReviews]/[NumberofWeeks]);


Lilly
 

vbaInet

AWF VIP
Local time
Today, 21:23
Joined
Jan 22, 2010
Messages
26,374
Remove the Group By. Basically, turn off Totals.
 

Lilly420

Registered User.
Local time
Today, 17:23
Joined
Oct 4, 2013
Messages
126
Oh my goodness, I removed the Group by on all the calcuation fields I created and it worked. YEAH...you are so wonderful...thank you for taking the time to help me...it is so appreciated... :D
 

Users who are viewing this thread

Top Bottom