Access :: Average Miles Per Gallon Query Require Assistance!

MS Access: Logic For Point/Miles Rewards Program

Jul 9, 2007

Hi Everyone:

I am new to this forum as I recently started working in MS Access. I�m hoping someone can please help me with this query I�m trying to do using MS Access 2007.

This task is somewhat similar to a typical points/miles rewards credit card program.

The problem is as follow:

For every $2,000 of accrued purchases in a credit card, an individual will get a gift card.

So, any remaining balances over $2,000 will rollover and be added to next set of transactions, until it reaches $2,000 again. For instance, if on the first week I spent $2,500, I would qualify for my first gift card. The excess $500 would rollover and be added to the next set of transactions until they reach $2,000 again. Therefore, this would qualify me for another gift card.

So far, I�ve created two tables, one that stores accrued purchases (Promo History) and another table based on an Append Query which takes all qualified transactions from the first table and appends those records to the second table (Promo History Qualified) in order to track those qualified transactions over $2,000.

However, I�m stuck on how I would accommodate the remaining balances to be carried over. Any suggestions would be greatly appreciated!!

Thank you in advance,

marel

View 1 Replies View Related

Need Query Assistance

Aug 22, 2005

I have two tables in my database. The first is a list of email addresses and the second is a much smaller list of email addresses I would like to remove from the first table. Is there a query or SQL statement that I can use to accomplish this task. Thank you from grateful Kenny.

View 2 Replies View Related

Query Assistance

Sep 6, 2006

First time poster, so I hope someone shows me the light and makes your forums shine! :)

I have a POS system that was designed in house which uses Access. I am trying to create some queries outside of the system to retrieve some data that has been being stored. I am basically wanting to know which items have been in stock for over 6 months and have never sold.

I have a query designed to show me this data for one store at a time, but nothing that shows me if an item is so poor it has never sold at any stores. This is my single store query:

SELECT Inventory_ProductTable.Artist, Inventory_ProductTable.Title, Inventory_ProductTable.Quantity, Inventory_ProductTable.Category2
FROM Inventory_ProductTable
WHERE (((Inventory_ProductTable.Quantity)>0) AND ((Inventory_ProductTable.LastBuyDate)<Date()-"180") AND ((Inventory_ProductTable.masterStoreID)="CDT001") AND ((Inventory_ProductTable.DepartmentID)=1) AND ((Inventory_ProductTable.LastSaleDate)=""));

It is pulling the artist, title and category of the item out of the database to display the informtion that I am needing (for the most part). Quantity of 0 and last sale date "" basically means there is no record of it being sold, so this gives me my zero quantity sold criteria for anthing that has been in stock for 6 months or more (the last buy date criteria).

I am not sure what other info I need to provide, but I would be happy to do so. I am a n00b at this and really love to learn, so lay it on me!

thanks in advance.

View 6 Replies View Related

Query Assistance Needed Please.

Mar 9, 2006

I have DB with a Switchboard that has a button for Bin Number (Part Number). It is based on a parameter query that asks you to input your Bin Number. Once you enter the Bin Number it pulls all information for that Bin Number from a table that contains the Bin Number, description, Manufacturer P/N and Manufacturer and shows this in the query.

What I need to do is have this information each time a different Bin Number is entered to be stored in specific table (datasheet style) for printing out just a list of specific Bin Numbers and their information. As the parameter query stands now, each time I input a different Bin Number it clears the previous information. Can I tie in a make table query in some manner that will store the selected Bin Number info?

Can someone make suggestions on the easiest and quickest way to set this up? Keep in mind I am still learning Access so please be detailed in your comments. Thanks in advance for any assistance.

View 5 Replies View Related

Query/Expression Assistance

Feb 22, 2007

I have created a query based on two akwardly designed tables that ultimately relate a member of Congress to a specific Committee and Subcommittee. Unfortunately I cannot alter the tables, as I receive weekly updates from an external source.

This being the case, once I have created the query, a "-" appears in the Subcommittee column, where the Member of Congress serves on the Committee, but no Subcommittee or the Committee does not have a Subcommittee. Although I could exclude the "-", however when I do this certain committees are removed from the final results. So I attempted creating an expression, where "<Not Assigned to a SubCommittee>" appears if the Committee does not have a SubCommittee. Unfortunately there are a few committees that fall under this. I created the following expression:

=IIf([HMC_Name]=("Committee on Budget""Committee on Standards of Official Conduct""Committee on House Administration"),"<No Subcommittee Assignment>",[HSC_Name])

Yet this expression returns #Error. Any suggestions?

View 6 Replies View Related

Crosstab Query Assistance

Mar 28, 2007

Hello, My first attempt at a Crosstab query and I need some assistance with the output. The expression I use is below and it returns the correct infornmation by month, but i need the year to be included as well as the data goes back several years.

I would like it to calculate so that i get Jun-03, July-03 etc but i only get the option to choose one field type ie Year Month etc

Any assistance greatly appreciated

Format([APM Outcome Effect dt],"mmm")

View 1 Replies View Related

Total Query Assistance

May 20, 2006

I am doing an assignment where i am required to

Quote: "Find the total price (SUM) of all stock items in the database (use total query and find the SUM of the [price of stock]*[quantity on hand]"

Ive tried several times to do this, each time unsucessfully because im unsure how to go about it. i can get the sum of those two things, but i cant multiply the two.

Thanks in advance for the help

View 2 Replies View Related

Need Assistance Modifying Date Query

Nov 19, 2006

I have a query that is based off of a union query

SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs
FROM clvpertech
GROUP BY clvpertech.clvtech1;

This is returning a sum of all the procedures a technician does for the entire database.
This is working.

I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.

I made this query to get the daily results and it works
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$()))
GROUP BY clvpertech2.clvtech1;

How can I add different queries to get the results by breaking down the date field for year, week, and month?

I tried this for the month but I get Invalid Procedure Call when I try to run it???

SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4])))
GROUP BY clvpertech2.clvtech1;

View 6 Replies View Related

Request Assistance With A Query On A Date Field

Feb 22, 2006

I am looking for assistance creating a query on a date field that will go
back three business days from the current date. It must not show inclusive
days, as in yesterday and the day before. Just the 3rd day past and not
counting weekends. Also holidays would need to be considered.
Thanks for any assistance
RW

View 3 Replies View Related

10-min Average Query

May 3, 2006

Hi,

I'm trying to create a query that returns 10-min average wind speed.

I have the logging date,time and the wind speed per second in the wind log table.
Date and Time Wind Speed(mph)
28/04/2006 2:17:01 PM 10.5
28/04/2006 2:17:02 PM 10.6
28/04/2006 2:17:03 PM 10
...

And I would like something like this from the query:
Date and Time Wind Speed Ave
28/04/2006 2:17:00 PM 10
28/04/2006 2:18:00 PM 7
28/04/2006 2:19:00 PM 5
......

Thx,
1.8T

View 5 Replies View Related

Fields Require

Sep 28, 2006

Attached program needs little code correction; but, I do not know how, Any help will be highly appreciated
The code attached works fine with fields Dentist , patient number and name, but when I try to apply the same code at fields productID and Quantity does not work because these fields at sub form , Can any one help me to handle this problem please ?

All what I need to in force user to fill all fields ( all fields require ) dentist, patient number, patient name, product Id and Quantity,

Regards
vbnt

View 7 Replies View Related

Simple Average Query

Jun 3, 2005

I apologize, I know this has been covered. But I just spent half an hour reading old posts and still can't quite decide how to apply it to what I'm doing.

I have a db that logs surgeries and all their details. One of the new things they want to do is be able to run a list of average cost for a certain surgery, since patients are always asking ahead of time how much it will cost. I have a query (and report that runs from it) that will list all the surgeries and total charges for individual ones for a date range the user specifies. But I can't figure out how to make it calculate an average charge for each surgery. I could if there were always a certain number to divide by, but of course there could be 2 of this type of surgery and 57 of that type.

The query I currently have set up is:

Field: MR#
Table: SurgeryLog

Field: Wname: [LastName] & ", " & [FirstName] & " " & [Initial]
Table:
Sort: Ascending

Field: OperationDate
Table: Surgery Log
Criteria: >=[Enter Start Date]

Field: PatientType
Table: SurgeryLog
Criteria: "SDS"

Field: TotalCharges
Table: SurgeryLog
Criteria: >0

Field: Operations: [Operation1Performed] & Chr(13) & Chr(10) & [Operation2Performed] & Chr(13) & Chr(10) & [Operation3Performed]

Thanks much!

View 1 Replies View Related

Average Time In Query

Dec 6, 2005

I have a query with the fields dtDate (Date/Time in Short Date format), NC "Number of Chats" (Long Integer), and TCT "Total Chat Time"(Date/Time in hh:nn:ss format) from tblChats. Each date will have multiple NC and TCT values. This query is totaling them by date with the following SQL.

SELECT DISTINCTROW tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') AS ChatDate, Sum(tblChat.TCT) AS SumOfTCT, Sum(tblChat.NC) AS SumOfNC
FROM tblChat
GROUP BY tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date')
HAVING (((tblChat.dtDate)>=Date()-7))
ORDER BY tblChat.dtDate;

The query works fine. Now my question.

I would like the query to figure the average time per chat for each day.

Could this be done without VBA? Any ideas??

Thanks.

View 3 Replies View Related

Average Rank Query

Sep 22, 2015

Right now, I have an output that looks like this:

Code:
AvgCost CompanyID Policy#
25 22 12
28 23 12
35 24 12
21 25 12
20 22 20
15 24 20
13 23 21
43 24 21
Etc.

I want to know if it is possible to get an output that adds a ranks the CompanyIds by average cost per each policy #.how companyID24 ranks, and I have the query set up where it only outputs Policy#'s that company 24 is ranked in (has sold product in).

Code:
AvgCost CompanyID Policy# Rank
25 22 12 2
28 23 12 3
35 24 12 4
21 25 12 1

[code]....

View 11 Replies View Related

Does Access Require Re-installation??

Nov 21, 2007

Hi,
a colleague of mine has Office 2000 installed on her work PC in order to carry out Access datadase responsibilities (amongst other things) as part of her job.

I think (though I cannot be sure) that someone has 'locked down' the copy of Access to such a point that shift bypass no longer works, F11 does not display the database window etc. This is strange because our company generally does a default install of Office and leaves most everything configured as standard.

In short, I need to access the Tools menu to set startup screen options and customize some of other options for her. Obviously I can't.

Will this problem require a complete Office re-install or is there a way to undo someone elses changes and set Access back to 'wide-open' ??

Thanks

Sausagefingers

View 5 Replies View Related

Require Entry In Either Of Two Fields But Not Both

Jun 6, 2014

In a table, I'd like to require an entry in either Field A or Field B. But an entry should be in only one of the two fields.

View 9 Replies View Related

Display Average Value From Query In Label

Aug 12, 2005

I have a form that I want to use in order to work out a global figure for average travel time for my engineers (data coming from another database).

I have a form with two text boxes, which allow the user to define a date range. This links into my query which works fine and returns all records from that date range.

Now comes the troubling part!

On the click of a command button I want the label to change and display the average for my work time column form my query (which by the way is in decimal format).

Can anyone help me? I have tried searcing but to no avail

View 14 Replies View Related

Queries :: Average Function In Query

Jul 5, 2013

I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard

Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status

The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon..I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013. My Dashboard needs to have the below fields

Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days

I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero.

SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days]
FROM TblWO
WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected"))
GROUP BY TblWO.[Operational* Categorization Tier 2]
HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));

View 3 Replies View Related

Queries :: Exclude Zero From Average Query

May 21, 2013

I made a query to calculate the average of a column, suppose that I have 5 records in that column (46,35,0,19.3,12), when the query calculate the average it sums the total of the column and divide by 5 (that's 112/5 = 22.4), what I need the query to do is to divide by 4 because one record is zero (that's 112/4=28). I put in the criteria the following (Not Is Null And <>0), yes this will not show the column that has zero but it still divide by 5.

View 6 Replies View Related

How To Require Entry In A Field On A Subform?

Aug 8, 2005

I haven't been able to figure out the code to require entry (for a new record) in a field on a subform - anyone can help with this?

The situation is a contacts database, and each new contact should have a (or multiple) Person Roles entered as well, which is their relationship to the organization.

View 2 Replies View Related

VBA Code To Require User To Enter A Value

Mar 14, 2012

I am trying to edit the following code to be able to require the user to enter a Loan number and keep prompting the user to enter a value as long as the field is blank. Once the field is filled in, then the code should go on to check if the sql condition is met and make the necessary change if met, then finally, I have a save command code that will require the user to save the record. Right now I am getting errors when I added the code to require the user to enter a field. I am new to loop and if statements in vba so I am not exactly sure how to structure it.

Code:
Private Sub Save_Record_Click()
Dim SQL As String
Do
If IsNull(Me![Loan Number]) Then MsgBox "Please Enter Loan Number. This is a required field."

[Code] ....

View 1 Replies View Related

Can't Query For Average Balance Sytax Errors

Feb 17, 2008

Can someone help me please???:rolleyes: I have to query for the avg balance from customer table, custbal field from a certain state... I've tried everything and cannot seem to get the expression correct. How do I do this???? any help is appricated :D

View 2 Replies View Related

Require Answer For Option Group On Form

Apr 13, 2006

I'm creating an employee survey database and I'm using option groups for collecting general information such as office, department, etc. My question is can I make it a requirement for them to answer an option group before moving on to other questions? I'm trying to control and track how many employees have taken the survey. Right now I can skip these general information questions and just go into the survey questions so I'm trying to prevent that. Also in the same train of thought is it possible to not save any incomplete surveys? Say I have 50 questions and someone starts the survey but only answers 10 questions and then exits for whatever reason. I would like that record to be deleted so it does not count. If I allow incomplete surveys then I can get skewed numbers.

As always you help is greatly appreciated.

Thanks,
Chester Campbell

View 7 Replies View Related

Require Specified Numeric Lenght Or Alpha Input

Jul 26, 2007

I'm trying to create a validation rule that requires an input of 5 numeric characters OR if the entry begins with a letter it can be any length. For example if the record begins with a number the length must be 5. If it starts with a letter any length is acceptable.

Any help is appreciated!

Thank you!

View 2 Replies View Related

Require Data Entry; Message On Edit

Apr 27, 2005

I have a subform where records can be added or edited. There are two fields: firstname and lastname. I want a message box to appear when a first or last name is edited.
I do not want that message box to appear when a new name is being added. I use this code:

[CODE]....

Now I want to also prevent the user from leaving a record before both fields have data (first and last name are required). My efforts to do this seem to conflict with my message box issues.

Can anyone see what I'm doing wrong or how to do this?

View 14 Replies View Related