Queries :: Overlapping Date Parameters - Dropping Data
Feb 24, 2014
I'm trying to create a report that pulls from two tables [tblTelephony] and [tblSales]. All data in my query is limited to a date range entered through a form.
For every record in [tblSales] (showing the agent made a sale) there is a record in [tblTelephony] (showing all the stats for the agent's day worked). [tblTelephony] has one date for each record. [tblSales] has two dates for each record. The sales dates are the date the services were ordered (matches the date worked in [tblTelephony]) and the date the services were installed.
In order to get an agent's MTD Sales stats I have to query the date range on Install dates. MTD Telephony stats are run on the same date range on telephony date. Where I run into an issue is with the sales that are ordered before the date range in question and installed during it.
I've run a separate query to sum the sales installed during the date range and used that sales value in my Telephony query. In order to get my data to show as accurately as possible, I had to create a relationship between the Order Date and the Telephony date. I'm really hoping to find a way to force the sum of sales in sales query to show in the sales column in the telephony query, regardless of the telephony date range and without adding telephony data for dates outside the range.
Example:
Date Range = 2/1/14 - 2/24/14
Telephony Date = 2/3/14
Order Date = 2/3/14
Install Date = 2/14/14
Appears on report
Date Range = 2/1/14 - 2/24/14
Telephony Date = 1/31/14
Order Date = 1/31/14
Install Date = 2/3/14
Does not appear on report
How to get the sale example on the bottom to show without removing the relationship?
I atrying to make a query that shows the price for a product, based on two parameters.
Parameter one is a product code. Parameter two is a date. This date falls between two dates.
I have one list (table) where is product code and invoice date.
The second list (table) I have, contains product code, and price valid from date, and price valid to date columns. This price valid to date is often not filled, and the price I still valid as we speak. If the date is filled there is often a new entry with an updated price. But sometimes, even if there is a new entry in the table, the date 'valid to date' is sometimes also not filled.
I would like Access to show me the valid price for the specific product. What criteria should I give in the macro, in order that Access shows what I want?
For illustration purposes, a small overview of my table:
Product, invoice date, price according to price list AAAA, 02.05.2012, ???? AAAA, 01.08.2012, ???? BBBB, 10.06.2013, ???? AAAA, 31.10.2013, ???? AAAA, 16.11.2013, ????
If you happen to know how this search can be performed in Excel, I am of course also happy to read that. (But my index, or Vlookup functions, give only the first possible result in the table. As I do not know how to give in the date parameter.)
I have made several extractions from back ups from our ERP system using maketable querys. Each time when I renamed the table after running the maketable query. I now want to add them to create one big table showing the records which I extracted. I now found that each time when I extracted from my back up tapes there was a little overlap in time which gives me double entries when I use an append query to add them al together.
Is there a way to set parameters which filter our double entries and only adds the unique records to this table?
i'm a bit a database novice, and have a fairly simple database in Access2000 that records complaints of different categories with amongst other things 'reported' and 'resolved' date fields.
I have 6 queries/ reports that need to be run periodically, using a 'ReportStartDate' and a 'ReportEndDate'. When i run the reports they all use the same dates.
At present I have these set as parameters in each of the 6 queries, but it is a pain having to type both dates in 6 times, and there is obviously a good chance of me making a mistake in typing the dates 6 times.
What I would like to do is just set the ReportStartDate and ReportEndDate just once, so that it could be used by all queries/ reports.
I was thinking that the best way of doing this would be to enter the 2 dates in a table, but I can't see how this would then be linked to the main data table in the queries. I'm sure there must be a way around it...and i'm probably going about it the wrong way...any pointers would be greatly appreciated before it drives me insane!
So I run cash flow for a business, and we export data from Oracle and insert it into an access database. I have to run about 25 queries, entering in the same parameters for each. We number each week of the year. So for say the first week in January, I would run the first query and it asks: Beginning Week, I enter in 1, then another paramter value asks me the ending week. I have to enter in these parameters for each of the 25 or so queries, and it becomes quite irritating. Each query has a number of columns, but I am only interested in obtaining the sum of one of the columns, titled Distribution amount. So I am looking for something that will run each of my specified queries, then spit out the total of the distribution column for each in a table like.
Query 1: Total Distribution Query 2: Total Distribution etc....
Is there anything that would allow me to do this, with entering in the week parameter once, say week 1 start, week 1 end. and it use those same parameters for each query?
I had a database that wasn't normalised but after some (a lot of) help here it now is. I have quite a few records that need to come accross to the new database and I was wondering:
I can drop the NAME/JOB_TITLE/BASE information in OK but can I place the trainig done for each staff member in? I was thinking that if I dropped the details in the TRAINING_DONE table and manually entered the STAFF_NO which is the primary key, would that work?
I need the following report to open with date parameters. I have the following code, but it doesn't quite work.
When an item is chosen from Modl (a list box) a box pops up asking for LowPop, then another for Start Year and then another for End Year.
Those last two aren't doing what they should. They should restrice the [Date] field to between the years entered as start and end. I would like to put it in the "OpenReport" line, but don't think that's going to work.
Code: Private Sub Command27_Click() Dim varItm As Variant Dim ModelWhere As String Dim strQuery Dim LowPop As String Dim SDate As Date
i'm trying to drop importerror tables with code, but this isn't working
i am not sure if its to do with unusual characters in the import errors tables ( error tables generated by access when importing spreadsheets etc
any ideas why the code fails
error 3295 - syntax error
'drop errors tables first tdfdrop = 0 For Each tdf In CurrentDb.TableDefs If InStr(1, tdf.Name, "ImportErrors") > 0 Then tdfdrop = tdfdrop + 1 sqlstrg = "drop table " & tdf.Name DoCmd.RunSQL sqlstrg End If Next tdf If tdfdrop > 1 Then Call MsgBox("Dropped: " & tdfdrop & " importerror tables")
How can I design a query that will return each products latest currrent location by date, aka, the third and sixth record???? Thank you, I am relativley new to access and am struggling with this.
I working on a Project in which I have a table that is being updated from the values in another table. The problem that the table is deleted and rebuilt in code before the Query is executed. Access is so smart, it drops the links between the tables when the table is deleted even if the query isn't open.
To get around this, I recreate the SQL in Code for the Named Query involved just before I execute it. This works OK but probably creates bloat and is a little slower.
Is there a better way around this problem other than recreating the SQL for the Update Query each time?
I have created a query where I use the DateAdd function to calculate a date. I have named this calculated field NextVisitDue. The problem is that I'd like to be able to put an input parameter on NextVisitDue so that I can retrieve records within a date range. For some reason, when I do this, the resulting NextVisitDue dates include dates that are outside of the date range. For instance, if [Begin date:] = 1/1/2005 and [End date:] = 6/1/2005, I will get resulting dates in NextVisitDue like 1/12/2006, 10/21/2006, and 10/6/2007. These results are clearly outside of the date range. What follows is the SQL statement...
SELECT tblSpecialist.User, tblSpecialist.FirstName & " " & tblSpecialist.LastName AS Specialist, tblProvider.ManagerOwnerFN & " " & tblProvider.ManagerOwnerLN AS Provider, tblProvider.FacilityName, tblProvider.LastSupervisoryVisit, DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]) AS NextVisitDue, tblProvider.VisitFrequencyCode, tblFrequencyCodes.Frequency FROM tblSpecialist INNER JOIN (tblCaseLoad INNER JOIN (tblArea INNER JOIN (tblProvider INNER JOIN tblFrequencyCodes ON tblProvider.VisitFrequencyCode = tblFrequencyCodes.FrequencyCode) ON (tblArea.AreaCounty = tblProvider.AreaCounty) AND (tblArea.AreaFacilityType = tblProvider.AreaFacilityType) AND (tblArea.AreaZipCode = tblProvider.AreaZipCode)) ON tblCaseLoad.CaseLoadID = tblArea.CaseLoadID) ON tblSpecialist.SpecialistID = tblCaseLoad.SpecialistID WHERE (((DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit])) Between [Begin date:] And [End date:])) ORDER BY DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]);
The real interesting thing is that, if I use literal values for the date range criteria, the query results are as expected! For instance, the following works beautifully (but doesn't allow for user input parameters as required)...
SELECT tblSpecialist.User, tblSpecialist.FirstName & " " & tblSpecialist.LastName AS Specialist, tblProvider.ManagerOwnerFN & " " & tblProvider.ManagerOwnerLN AS Provider, tblProvider.FacilityName, tblProvider.LastSupervisoryVisit, DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]) AS NextVisitDue, tblProvider.VisitFrequencyCode, tblFrequencyCodes.Frequency FROM tblSpecialist INNER JOIN (tblCaseLoad INNER JOIN (tblArea INNER JOIN (tblProvider INNER JOIN tblFrequencyCodes ON tblProvider.VisitFrequencyCode = tblFrequencyCodes.FrequencyCode) ON (tblArea.AreaCounty = tblProvider.AreaCounty) AND (tblArea.AreaFacilityType = tblProvider.AreaFacilityType) AND (tblArea.AreaZipCode = tblProvider.AreaZipCode)) ON tblCaseLoad.CaseLoadID = tblArea.CaseLoadID) ON tblSpecialist.SpecialistID = tblCaseLoad.SpecialistID WHERE (((DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit])) Between #1/1/2005# And #6/1/2005#)) ORDER BY DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]);
I have a report that has two sub reports. The sub reports are based on two different queries that have a parameter in each. When I run the report it prompts me to enter the two parameters for each record (there could be 30 or more records), how do I get the parameters to only ask me once and pull all records? My parameter is >[Continued ED Starting After XX/XX/XXXX] and the other parameter is >[Credits Starting After Date: XX/XX/XXXX], so I would like to just enter this information once and return all records with that criteria and not have to enter the information 30 or more times to get all records.
I have a query that sums downtime on equipment. The problem that I came across is that there can be multiple status items open up on the same piece of equipment. When the restored time on one status item occurs after the down time of another, the query still adds the total down time of both status items which is giving me greater downtime than there actually is.
Ok here is my question. I have two column that I am trying to merge. The first column I have set to be two digits. The second column is set to be three digits. The problem is if the first digit in the second column is a 0 it gets dropped once the two columns are merged. How do I stop this from happening. I need the merged columns to be 5 digits.
When i use docmd.sendoject and try to attach my report in rtf it's dropping my checkboxes. I need the checkboxes...does anyone know how to solve this problem.
I have a report and export function which is based on date parameters. The field name called "date". However, I added a new field called "followup-date" because we would like to track customers that are returning back. Now, my problem is when I go to Export by date parameters or generate the report by certain date eg. July1st to July 28th, it only pulls up the record based on DATE field.
Is it possible for the date parameters to look up july1st to july 28th under date field as well as followup-date and pull up those records that are meet the date parameters? The reason I have to do this is because some customers do not come back for followup so I have to look under 'date' field too. While some customers can have more than one followup.
What I am trying to do is create 3 (or more) parameters for a query from a single table. Lets use this for example:
Table Name= "tblExample" Field Name "A" with Perameter "1" Field Name "B" with Perameter "2" Field Name "C" with Perameter "3" (All from Table= "tblExample")
My intentions are that when the query is run, the user is asked to include 3 subjects (1 subject per perameter; 3 perameters total that pop up). But I am having trouble making it so that if a random person using this query doesn't know or can't remember 1 or 2 of the subjects they are looking for, the query will just (in a sense) ignore the two blank parameters the user has left alone, and just clicked the "ok" button without entering anything, and use the 1 parameter that it was given a subject for, to filter/query out a result.
If you beleive you will have trouble explaining this to me, I'll use this as an example:
Table Name: "tblExample" Field Name: "A" with Parameter "1" Field Name: "B" with Parameter "2" Field Name: "C" with Parameter "3" (All from Table: "tblExample")
The user uses the query and is asked by the first parameter for input. The user isn't sure, and clicks ok without entering anything, and parameter 2 pops up. The user then enters a subject of which he/she knows to look for and clicks "ok". Then the final parameter asks the user for input, and the user again doesn't know, or can't remember so he/she just presses the "ok" button. What would be the coding for this kind of parameter that if nothing is entered, the parameter is ignored?
In desperate need of assistance. Thanks in advnace
I have a query which has a parameter called [Enter Date as DD/MM/YYYY]. This filters out records from my table which match those entered by the user. However, when the query is run, Access is asking the user to enter the criteria twice. The first time it is labelled Expr1, the second time it is Enter Date as DD/MM/YYYY. There is obviously something wrong in my query, can anybody point me in the right direction here?
I have a query that pulls information from two tables. Some of the fields that are being queried share the same name in the tables, [Reimbursed_Amount] and [Cancel_Fee] specifically. In Design View I have specified that I only want the query to pull these fields from the Event Information table. An error occurs when I try to run it, saying that I need to define which table the field is from in the SQL code.
But then after I added clarification in the SQL, when I run the query it now prompts for a parameter for each of these fields. Why is this happening? I leave it blank, so a parameter has no impact on the query. How can I stop this?
Here's the SQL, after I added the table clarification:
Code: SELECT (Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee].[Event Information])-nz([Reimbursed_Amount].[Event Information]))) AS Total_Cost, [Shared Billing Information].Paid, [Shared Billing Information].Shared_Billing_ID, [Event Information].Shared_Billing_ID FROM [Shared Billing Information] RIGHT JOIN [Event Information] ON [Shared Billing Information].Shared_Billing_ID = [Event Information].Shared_Billing_ID
I'm able to pull users that are logged into our network but our company uses a name that has their employee number as well. So deponding on when they were hired it could be a 5 or 6 digit number. so an example would be JSmith123456 or it could be JSmith12345 so is there a way to drop the numbers at the end? Meaning if they are entering data into a form that pulls their name can I drop off those last digits in the name field?
I have a number of linked tables that vary depending on use. How to delete them or drop them all from the database? I produced/stole some code that deleted all tables - but it didn't delete the linked ones.
I am having an issue with an Access import. It worked correctly bringing in a sharepoint spreadsheet until a date field was added to the spreadsheet and the structure to the database. Now it drops any additional fields that are added. At first I thought if I added a filler field after the field I needed that it would still drop the last field but it dropped them both. If I insert the field in the middle of the spreadsheet....(where I really want it). It drops the last field of the email address. Which I need to send out notifications. I have tried designing a complete new table and importing it, but it continues to do the same thing.
Not sure if my problem is with the spreadsheet or Access. Somehow it is still looking at the old structure when I import even if I create a new table. Not sure what is happening.
