Context is I have a TABLE 1 that has a record for every package shipped with various details about it, include how it shipped, and the date. Table 2 is a key to go from the ship method to a better grouping we deem service level. Table 3 is a Fuel Surcharge (FSC) Table. It includes various FSC % that vary by different service levels and different date ranges. In the end I need to know the amount of pkgs shipped (by month and servicelevel) and the relevant FSC.I have several tables in my database but we should be able to focus on 3 to explain my situation and only the relevant fields:
Table 1 is Left join on Table 2 based on ShipVia
Table 2 is Left join on Table 3 based on FSCType
The issue is Table 3 is dependent on Table1.ShipDate being between the date ranges as well.
There are overlapping date ranges, however they are differentiated by the FSCType field.The only semi-feasible solution I currently have is to expand Table 3 to include every date, and not date ranges. This will create an issue due to size of date ranges etc.
I have a query that I want to "filter" based on a date derived from a Build statement (Expression). The Build statement works I can even sort by "Ascending".. But when I try to add a "Between [Startdate] And [Enddate] statement in the Criteria box, returns nothing".
I have multiple buildings that I own. Each building earns a monetary amount each day. Some days they earn $0, some days they earn $1,000. This is all kept track in a data base in which someone manually enters the information each day.The three fields are:
Building Date Profit
I have narrowed a query down to one building, and I am interested in two things. How do I write a query that shows a 30 day peak (the most profitable 30 days). In other words. I want to see the following Building: GNB Tower Date: 02/14/13 - 03/15/13 Profit: $25,162
The next thing I am interested in is a 7 day peak from within these 30 days. This shouldn't be too difficult if I can figure out how to query these 30 days, I can query 7 days out of those 30 days. So this bigger mind stumper is the first one.
out of a date range of months and months (Let's say 10/12/13 - 6-14-13).Once I figure this part out, I am sure I can figure out how to compile all buildings into one large report with their 7 day peak and 30 day peak.
I have recently set up a button that exports multiple queries to one Excel file, using TransferSpreadsheet code. This works great and saves a lot of time, the only thing is on many of the queries I have a date range set, so you have to enter a date range to get the results.
Is there away that once I press the button I enter the date range once and than it exports the rest of the data based on this range, rather than entering the same dates multiple times.
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.
ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2. [ABCTABLE] ABCORDER ABCPART
Is there a way to show the earliest and latest dates of a report generated by a non-date field?
E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)
I am trying to filter out information for an ODBC query I have written, to obviously make it more concise. Because our data is not set up very logically, a lot of my queries are based on text...but that is another story. My current situation starts with a simple make-table query finding certain data within a date range. This is followed up by another simple make-table query to find other specific data within another specified date range. Then I have a cross-tab query between the two tables to locate all the data that shares the same unique identifiers. My problem lies with trying to only pull information from a certain date range. For example, I want information to be included if the data has the same unique identifier as well as falls within +/- 7 days of the received date (dd/mm/yyyy) also listed in one of the tables. Each record will likely have different received dates so I cannot set it as a constant.
Make sense to anyone? I am not confident enough to play around with some expressions...well it is more I haven't been successful at using them correctly.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Hi, I was wondering if someone can give me a possible solution to my join below:
select tableA.name From tableA, tableB where tableA.id IN (Select id from table c where .......) <--I would like tableB.prodId to make a join with the tableA.id's that are in this select sub query
Hi everyone, I am hoping to learn something new about access today. What I need is to be able to run a query that gives me rows of data based on paramenters that I specify. For example, I will query this all just on one table and I want to come up with an output that is in a nice format as follows:
Basically each member has an effective date, like member A might have 01/15/04 effective date and member B might have 01/02/04 effective date. I need to capture the count of members by month based on their effective date range. I would like to do it all in one query like above instead of just running a query for January, then February, then March, so on.
Is there a way to do this? Right now I am just running a query and getting one member count at a time and copying that number to an excel spreadsheet. it works okay, but is time consuming. I would rather semi-automate things for me. Any ideas?
Oh and I am new to access, by no means an expert, so be gentle with me.
I have a database with several clients who have a series of appointments. I want to be able to create a report of all the clients to be seen during a range of dates to be inputted by the user. All help would be greatly appreciated.
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.
Any suggestions or assistance would be appreciated.
I have form with two date text boxes. One is start date and the other is end date. I want to query a table and only pull records that the date field is between the start date and end date.
my code I am working on is:
Dim startdate2 As String Dim enddate2 As String Me.start_date = startdate2 Me.end_date = enddate2 Dim SQL As String
SQL = SELECT FROM 'project' WHERE Date between 'startdate2' and 'enddate2'
DoCmd.RunSQL SQL
I am getting a error on the sql = statement, I can't seem to get the statement right.
Is their a way to update the TotalBatches in TableA with the sum of NumBatches from tableB that have the same BatchType and falls between the FromDate and ToDate of TableA?
First of all, I didn't know how to search for the precise info regarding my question, so, If it is repeated, feel free to close this thread or delete it.
So, here it is my question:
I have a table with values taken once monthly, that is, for each person on that table, I would have a maximun 12 records per year. In that table I have the Id of the person, the date field where I store a date when I get the record, a field where I store a value and a control number field. The values on the control number field go from 1 to 20 (or less).
What I want to achieve is, given a range of dates, show the last two controls of every person, something like: id, last control date, last control number, value of last control, previous control date, previous control number, value of previous control. As you could see, I want to show in the same row the last two controls for each person, only if the happend to have two controls, if they have only one, it wouldn't be necessary to show them, so, how can I do this?.
I am trying to calculate the working days Based on all web searches I am unable to find the specific scenario I am working in Auto industry, which means auto industry usually close twice a year for any reasons, let say in July for one or sometime two weeks and in December depends upon the Christmas date usually from December 20 till Jan 01
Now my question is when i am enter the holiday details in table do I have to enter line by line date e.g. july 01, 02, 03 (I am able to understand, how this works but still not yet tried) OR july 01 to July 07 (which make sense, but unable to find how to use date range to calculate working days)...
So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.
I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.
Code:
Private Sub btnDatumUpis_Click() Dim SQL As String SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _ & "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _ & "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
I am looking to have entries made to the table based on a date range. I have a database of events and most repeat daily, weekly, etc. When I enter them into Access, they go in as a single entry with a start and end date. But I need to have them as individual entries for each day so that rather than one entry of 5 days, I need to create 5 entries of one day each.
I have the following project that works as a timesheet manager:
Users enter in a form the employee name/date/hours worked/role/overtime (yes or no)/rate... on a daily basis. At the end of every week, they must create a report depending on the dates given in a query.
I have only one table (tbl_EmployeeHours) and a query to calculate the number of hours between two dates.
At the moment a report gives me all the information above (employee name/date hours worked etc..) but what I am trying to is a bit different.
I need Access to create a report (based on a date range given by the user) that would give me: per unique name, the number of normal hours worked in total, next column, the number of hours worked as overtime (rate 1.5), then, the number of hours worked as overtime (rate 2).
This means there are 3 different totals. For example, in the date range given, someone might appear twice but in the report he will appear only once with 3 different totals...
My guess:
I would say I need to create a different query for the 3 different totals (normal hours, overtime 1.5 and overtime 2). For each query you would need to ask the user to input the date range (which would not be so user-friendly I agree...). Then, I am not sure how access would create that list of names and combine the 3 different results from the queries...?
Code: ' count records in query Dim rs As DAO.Recordset Dim db As Database Dim strSQL As String Dim beginDatum As String Dim eindDatum As String Set db = CurrentDb
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
I am trying to form a join between two separate databases. Database 1 has a column (Gene_Name2) that consists of common names of human genes. In this database there is only one name for each gene per field. The other Database 2 has a column (Gene_Name2) that has various names for the same gene in each field. Most genes have been given different names over time.
I want to join the databases into a separate query database so that all of the information in Database 1 is retained and only when the name in Database 1 matches one of the names in Database 2 does the row of information get tacked on to the query database. The problem is that in a normal query the text in both Gene_Name columns do not match and the query returns no matches. I have tried Instr and like criteria statements with no luck. The following is an example of what I have and what I need.
Database 1
Chrom Gene_Name1 Data Chr1 ATC XYZ Chr2 IDH2 ZYZ
Database 2
Gene_Name2 Data ATC;ORF34;NM_123 ABC ORF65;IDH2 IGH
Joined Query Chrom Gene_Name1 Data Gene_Name2 Data Chr1 ATC XYZ ATC;ORF34;NM_123 ABC Chr2 IDH2 ZYZ ORF65;IDH2 IGH
I'm creating a query for a someone who wants quantities summed by a weekly range and correspond to a week number. This person wants Access to do something that is a simple function on Excel.
I have no clue how to do this or if Access is even made to do something like this.
