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
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;
Hi,
I'm Italian and I started playing (I don't work on it) with access to create an Airline Simulator for MS Flight Simulator. My "program" read data imported in an Access table by a secondary program, and work on it. :cool:
One or Two of my Beta testers found this problems:
1) function "date()" is not recognized in Access 2003 and 2007. in 2003 is read as "date"; in access 2007 is read "date(". The one with Access 2007 resolved the problem reinstalling the Office Suite 3 times. The third was working. nothing else changed. :mad:
2) Access request the OFFOWC.DLL, in an Access2003 version. But only one user... and that .dll is from Access 2007!! :confused:
I wrote the database in Access 2007 and in Access 2003, the file is obviosly Access 2003 format.
I attach it here, for someone that wanna try if he has the same problem. PS due to size of the file, it's a zip containing a .rar file, that contains the .mdb (zip only was 660 Kb)
Do you have some indications!? Thanks a LOT!! :)
Lorenzo.
I have a query which selects a complete list of companynames and producttypes, and another query which selects a few companynames and producttypes. What would be the query to select the companynames and producttypes from the first query that do not include the companynames and producttypes from the second query?
View 2 Replies View RelatedI 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 RelatedFirst 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.
Hi All,
Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be much appreciated as I promised the Boss this for tomorrow....:confused:
this is my SQL so far, it strips out the left of the postcode:
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.PostCode;
What do I add to do the count?
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.
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?
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")
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
Hi All,
I have two tables; "Section_Failure_Mode" and "Risk_Assessment". They are linked by a common field called section_failure_mode_id. I would like to write a query where you can delete every record in "Risk_Assessment" where the checkbox (called Selected) is not selected (i.e. equal to 0) in the table "Section_Failure_Mode". However, I do not want any records to be deleted from "Section_Failure_Mode", only from "Risk_Assessment".
I have put a link to the screen cap of the query I have tried below. Is this the correct way to do it?
13797
Best Regards,
Aaron
I am trying to discover the code that is asking for a phantom query in Subreports. Each Subreports control source query works fine.
I have attached two zipped files
1. Database stripped to send
2. SnapShots of the problem.
i have a quick question... I am having a slight problem with a query that i am using in my database... i am trying to calculate miles per gallon. The query i have set up looks at the previous records odometer value and subtracts the current odometer value to get the miles driven since the previous fill... Here is my sql code.
SELECT fuel_use_tbl.unit_ID, fuel_use_tbl.fuel_use_ID, fuel_use_tbl.fuel_date, fuel_use_tbl.gallons, fuel_use_tbl.odometer, (SELECT TOP 1 Dupe.odometer FROM fuel_use_tbl AS Dupe
WHERE Dupe.unit_ID = fuel_use_tbl.unit_ID
AND Dupe.fuel_date < fuel_use_tbl.fuel_date
ORDER BY Dupe.fuel_date DESC, Dupe.fuel_use_ID) AS PriorValue, [odometer]-[PriorValue] AS [Miles Driven], [Miles Driven]/[gallons] AS Expr1
FROM fuel_use_tbl;
The problem with this code is this... lets say i drive a brand new vehicle (its first record and it has no prior odometer value... the PriorValue reading shows up as nothing (NULL) when i then try to take [odometer]-[PriorValue] it shows up as nothing (NULL) while it should just treat it as [odometer]-0... i need help so that it shows an average mpg for every fill... Thanks for your help.
For a better example this is exactly what it looks like
unit_IDfuel_use_IDfuel_dategallonsodometerPriorValueMiles DrivenMPG
11328/11/200700.00
11269/11/200726400.00040015.3846153846154
11279/17/200722700.0040030013.6363636363636
113110/16/2007231,131.0070043118.7391304347826
101289/11/2007220.00
101299/17/200721500.00050023.8095238095238
101309/18/200728800.0050030010.7142857142857
Fuel Use ID is my primary Key and it is autoindexed... ive just been messing around with fake data and have changed the dates... thats why it looks a little strange
Greeny
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
View 11 Replies View RelatedHi:
I'm working with MS access only in very basic things.
Rigth now I need to look some information in a Table that was created in MS Access 2000.I don't know if this is a problem with MS Access 2003, because i still have some ones.
1) The Zoom tool is not accessible.
2) The order of the items, change all the time and I need to sort them every time.
If someone could help me in this matter, I'll appreciate so much
Thanks
I have to run a query which has to select data between two date fields. But the problem here is that my table have start date and end date fields. And I have to give a criteria( like the between and ) one from start date and another from end date, but since my start date and end date are on different fields, i cannot do this by using between and . It will be appreciated if you can give help on this
View 1 Replies View RelatedHello all. This is my first post on here after reading many of the other threads as I try - often in vain - to pick up access.
I am in the process of building a couple of databases, the main one being one that is an amalgamation of 4 other/older mdb's. All tables/queries etc were simply imported from these older defunct mdb's and then are archived off every month so they have 3months data and no more in them. The only thing is when it comes to problem solving and/or new queries, it is impossible to tell which tables/queries relate to which sections of the Db.
So my question to you is can I rename tables and queries in any way so that any queries that are using these tables are updated at the sametime, and if so how is it done? I have asked my colleague who is working with me to develope my understanding of access and he is at a loss. We are in the process of trying the old fashioned way ..... getting a book from the libarary ... but thought I'd try this new fangled internet thingy first :D .
The thought of having to trawl through around 100 tables and queries to rename or even re write the entire query/formulae is so daunting, I simply dont have that amount of time. Im using Access 97 and would greatly appreciate any help on this.
Thanx
Hi all,
I am new here, I am never one to ask for help, but I am desperate to get some assistance and hoping someone may be able to help me. I have just basic knowledge of MS Access, but no where near the skills needed to acheive what I am needing.
To explain, I am involved with a national memorial of all the Truck drivers who have been killed on our roads. I have a database in access, (near 1000 individuals and sadly growing) but wanting to create it into a form style. I also need to add a photograph of each deceased person and a second photograph of the plaque which they are on. Years ago I saw a form which I was impressed with, it looked so professional but never had the oportunity to investigate or disect it :)
I am hoping sks who will enjoy the challenge may be able to assist me.
more about the memorial can be seen at www.tarcuttamemorial.com
Thanks Dianne
Hi all,
please note that I am trying to create a database regarding the inbound shipment management of a warehouse.I have the following tables that I need to link:
SUPPLIERS
ORDERS
PRODUCTS
INVOICES
CASES
Every supplier has many orders.One order has one or many Products.So far is easy beacause I link them with one to many relationship.The problem is that one case has one or many invoices and each invoice has one or many orders.
So there is a conflict because the primary key of orders should be linked twice.
Any suggestions?
please help.
thanx
I need some assistance with how to use the Nz function. If you could please help me rewrite part of my SQL (shown below), I would appreciate it. I am a newbie and don't understand some of the programming syntax.
Background
-----------
I have two columns, RevisedRequirement and TotalFCbyS in which I need to do some calculations and insert two new columns named Variance and VariancePct.
Issue
-----
Both the RevisedRequirement and the TotalFCbyS fields can have nulls. I need to convert any instance of a null to a 0 so that I can perform the calculations and not get any error messages.
Current SQL Excerpt
-------------------
This creates the Variance column:
IIf(IsNull([qryProgramFundingAllocation].[RevisedRequirement])=True Or [qryProgramFundingAllocation].[RevisedRequirement]=0,0,([qryOpPlanForecast].[TotalFCbyS]-[qryProgramFundingAllocation].[RevisedRequirement])) AS Variance
This creates the VariancePct column:
IIf(IsNull([qryProgramFundingAllocation].[RevisedRequirement])=True Or [qryProgramFundingAllocation].[RevisedRequirement]=0,0,([Variance]/[qryProgramFundingAllocation].[RevisedRequirement])) AS VariancePct
The SQL shown, if I understand correctly, treats the Null as if it were a zero, but does not replace the null with a zero. Again, please help me with how to re-write the SQL so that it converts the null to zero.
I created a form using a text boxes by using the field list icon and dragging the field I wanted to make into the text box onto the form. In the properties of that text box, under the format tab I set the format for "short date". Under the data tab in the properties of the text box I put in the control source this forumla =DateSerial(Year([Date of Service]),Month([Date of Service])+10,Day([Date of Service])). "Date of Service" on my form is the name of the source field it calculates from. What the forumla does is calculates from the date entered into my Service Date field and whatever date is in that field it adds 10 months to it. This field with this formula in it caluclates the date of my contacting the customer to setup an annual service for them. I also have another field that calculates the annual service date using the same formula only changing the +10 to +12.
When the Date of Service it entered the Contact Date field automatically adds 10 months to the date entered into the Date of Service field. Same of the Date of Next Service field only instead of 10 months it adds 12. So my formulas are correct. However when I go to my table I can not see any of the caluculated dates entered into the table. I can see it in the datasheet view of my form. My question is how do I create a query to pull only contacts dates from this form for a specific time frame? When I attempt to create a query I can only see table fields which do not contain the formulas for calculating the dates. Or can the fields that calculate the dates on my form be linked so the calculated dates are linked into the table and updated on the table when I update the form?
Keep in mind I am new to access so please give very detailed and specific instructions on a solution for this issue.
I have 1 table with three columns of data (town, county, and zip). After entering the data on my form from a dropdown down box (town), which is represented by the first column of data in my table, I want Access to automatically populate the second and third boxes in my form that are representive of the second and third columns in the table, county and zip respectively.
View 2 Replies View RelatedOK .. bear with me. I am a self-taught Access user (sometimes clueless!) I have a table with member info. Children's birthdate is a field. Children are only eligible for the program until their 3rd birthday. Isn't there a way for the table/query function to figure out 3 years from the birthdate and put the data into a field?
The database already contains all the children birthdates ... can I enter a function that shows i.e., John's birthdate is 7/15/02 ... 7/15/05 is the date he becomes ineligible. If that date is in another column, I can sort all dates out and only view the eligible members.
I know this is probably SIMPLISTIC -- I am having a very hard time. THANX
I have three tables. Each table has the following
TblAA - aaDate, AAFS, AAFT, AAFU
TblBB - bbDate, BBFS, BBFT, BBFU
TblCC - ccDate, CCFS, CCFT, CCFU
The "xxDate" fields are (mm/dd/yyyy)
all other fields contain numerical data. (1,5,10 etc.)
I have linked the tables and it returns all of the results. However, My issue is that I want the user to be able to enter one date range to search all three tables at once.
Let's say tables AA and BB have data entered on 12/01/2006 and 12/15/2006, but CC has data from 11/07/2006. The user would enter Beginning Date: 12/01/2006 Ending Date: 12/31/2006 and they would receive results from the first two tables only.
I hope this makes sense. TBN (Tough Being a Newbie)