Query To Return Intermediate Values
Dec 10, 2007
Hi All,
I need an Access query to return the rate at which a customer should be charged. A customer's rate can be set for a period of 1 to 6 months and the customer's rate table could look like this:
ACCOUNT_ID........START_DATE........END_DATE...... ..COST_PER_UNIT
31308.................01-Apr-07................30-Jun-07.................6.195
31308.................01-Jul-07.................31-Aug-07.................6.304
31308.................01-Sep-07................31-Mar-08.................5.422
31308.................01-Apr-08.................31-Aug-08.................6.304
I need a query (preferrably SQL) that will return the rate a customer should be charged each month, e.g.
31308 / Aug 07 / $6.304
31308 / Sep 07 / $5.422
31308 / Oct 07 / $5.422
etc, etc
I'm using Access 2003 and the rates table contains details of about 2500 customers.
Any ideas would be welcomed.
PS. Can someone tell me how to paste a table straight from Excel into the forum post body???
View Replies
ADVERTISEMENT
May 25, 2006
I have a query that uses a linked table from an external database and joins it with a table from my database to return records that contain the same values.
Is there a way to return only the records that are present in the external database and not in my db?
Thanks
View 3 Replies
View Related
Feb 22, 2012
I have two tables: tblStudents and tblEnrollments. The query I have designed shows the StudentID and counts the number of Enrollments that each student has. I want the students who don't have any enrollments to still show up and have a 0 by their ID. Right now, only the students with enrollments show up in the query results.
Here is my SQL Statement:
SELECT tblStudents.StudentID, Count(tblEnrollments.EnrollmentID) AS CountOfEnrollmentID
FROM tblStudents INNER JOIN tblEnrollments ON tblStudents.StudentID = tblEnrollments.StudentID
GROUP BY tblStudents.StudentID;
What do I need to do to it to have null values display as zero?
View 4 Replies
View Related
Aug 2, 2014
I have a table that has a list of tasks and checkboxes attached to them to be checked once the task is completed. I need to run a query on the table that will only bring me back the tasks with a completion that is false.
Everything that I read online indicates that this is a difficult task for access. Maybe I can accomplish this in SQL view instead of design? If I put false is all of the yes/no fields, the query brings back nothing.
View 14 Replies
View Related
Sep 22, 2015
I have a set of devices that are assigned to a truck, on occastion they are moved from truck to truck, I need to be able to track where they are AND where they have been. Here is what I have:
ID (autonumber)
GPS ID (number, this is the specific ID to each device)
Truck Number (this is the specific ID for each truck
Installation Date (Date installed / moved)
I have created a query to show the data I need, I have put them in order based on date.
- I must keep each event stored on the table
Goal:
To show a list of the current location of each device, nothing more.
View 5 Replies
View Related
Jan 11, 2014
Trying to run a query where each 4 fields calling a custom function will not just re-run the same custom function over and over again for each field in a single record.
A Function has a huge amount of multiple queries and logic to perform.The Function returns a Integer, Integer, Integer, and optional Integer. Each integer requires a DLookup to lookup a String description value for each individual integer (in each of 4 fields).
The problem is, the DLookup in each column that runs against each of the integers re-run the same function.The result is that a single record, each of the 4 columns returning a single of the 4 values, the complex function is re-run 4 times.
The function is huge, part of a Business Rules Engine. Depending on the Rule-Meta data - it might launch up to a dozen queries and perform logic steps for each record. This is not the ordinary SQL Query.
Imagine if one record (for 1 field) takes 0.1 second to run. By referencing the function in 4 columns, this same function is re-run 4 times (0.4 Seconds) Against 50,000 records - this duplication of re-running the function for each column can really add up.
Possible Solutions: Researched Class Modules - There is a comment that the property Get, Let actually reduce performance. There are huge advantage of code documentation, documentation and centralization.It doesn't claim class modules reduce execution as each propery is returned. It also describes that Class Modules can't be called directly in a Query - unless each property is wrapped in a function.
Function Returns one String with delimiters: e.g 34;54;55;1 This single column goes into a Make Table (runs function one time per record) Then the D-Lookup is run against static local data. This pevented the function from being run over and over across the network linked data.
Final Solution: Eventually, the many hundred lines of VBA code for the Rules Engine will be converted into SQL Server T-SQL Functions on the server.For a Rule Engine development, Access has been great for a rapid protoype development and testing. The TSQL will be a final big step requiring re-coding. It is not currenty my option for the delivery time frame.
View 8 Replies
View Related
Oct 24, 2007
Hi
I have a query that runs off three tables: These are tbl_contacts, tbl_status, tbl_publications. All contacts have a status but not all will have a publication. The problem is that when i run the query on a status type where none of the contacts for that status have a publication it will return no results, where what I actually need is for it to just return the details of those contacts for that status. The whole query runs off a form with combo boxes.
grateful for any advice
Shapman
View 1 Replies
View Related
Apr 23, 2007
Here is my sample DBA
I'd like to solve this issue
when I open my Quotations form I'd like a set of services to be selected by default, ex: Brush and Pen. So the user only has to input the quote amount
is there anyway to have the form open with a script like:
"on open (or on load). in subform SELECT refproduct where product from tblproducts = 1 and also SELECT refproduct where product from tblproducts = 4"
Therefore when my main form opens, in the subform I have already Brush on the 1st line selected and pen on the 2nd. My user only has to type the price.
Thanks for your much appreciated help
View 2 Replies
View Related
May 3, 2007
I am working with Independent School Districts (ISD) in the state of Texas. The State is divided into 20 regions. Over 1000 ISDs are divided among those 20 regions. Each of the 1000+ ISDs has between 3 and several hundred individual school campuses. Each has a name, but as you would imagine, there is some duplication - not within an ISD, but between different ISDs. For instance, both Dallas ISD and San Antonio ISD may have a Lyndon B. Johnson campus.
We are an association that has members on most of the campuses.
I am using an autonumber ID as primary key on the member, campus, and ISD tables.
During entry/editing within the member table using the basic datasheet, I want to use a lookup field (showing campus names) to select the campus. That is not a problem by itself. But with many tens of thousands of potential campuses to choose from that doesn't work very well. What I would like to do is restrict the campus lookup to the names within the ISD, that is, filter the lookup by ISD.
However, I don't want to include the ISD in the member records because that will not be full normalization, but I am willing to do so if necessary. But if I do, I want to include only the ISD number (another autonumber primary key for the ISD table). So during member entry, I want to translate the ISD key into the related ISD name for selecting the correct ISD, before I try to select the correct campus within the ISD.
Essentially what I want to do, is member entry thru a datasheet and first look up the ISD to find it by name, then use that selection to filter the lookup on campus to limit the selections to only campus names within the chosen ISD.
I would be grateful for any assistance with this. I am not married to this approach, so if someone has a better but different idea, please suggest it. However, I am a relative novice, so bear that in mind.
Thanks in advance.
Conor
View 2 Replies
View Related
Sep 16, 2014
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
View 3 Replies
View Related
Jan 29, 2007
Hi.
I have 2 queries, one that returns all the project numbers in the db (say 50 projects), and one that returns some of the project numbers (say 30 projects)
I can make a query that combines the 2 queries, and it returns project numbers that are in both queries (30 projects), but I would like the query to return the 20 projects that are in first query, but NOT in the second query.......does this make sense?
I am sure this is fairly easy.......it has just got me stumped!!
Many Thanks.
Frank.
View 2 Replies
View Related
Sep 12, 2007
I'm working on a freebie for a day care center.
I can not seem to work this out in a single query.
I have:
tblChildren
tblParents
tblGuardians
Because so many familes that use day care are fractured, it is possible for 1 parent to have multiple kids but some of them to have a different second parent.
Therefore each parent and each guardian has thier own line and own unique ID in their respective tables.
each child in tblChildren can have up to two Parent IDs (Parent_ID_1 & Parent_ID_2) and up to two Guardian IDs (Guardian_ID_1 & Guardian_ID_2)
I've set the data entry form to require at least one parent for each child, but there may not be any 2nd parent or any guardians.
So what I now need to do is create a query, or series of queries, that results with each child on it's own line, with the name of each parent, and each guardian in thier respective fields.
Has anybody got any guidance for me please?
View 1 Replies
View Related
Feb 12, 2014
I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.
Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.
E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography
I would expect:
when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned
when TBL_Email!Category = Maths : Students A,B to be returned
when TBL_Email!Category = Physics,Geography : Students A,C to be returned
View 2 Replies
View Related
Feb 22, 2014
I want to return the price from my transactions table for the value '1' in the ID field in this transactions table.
That would be my first field in the query. The next one would be to do a sum of the sellprice field in my 'inventory' table where the buyingID matches a value of '1'.
how to accomplish this? So far, I'm not getting the query to return anything on the relationship.
View 4 Replies
View Related
Nov 7, 2006
I have a table like this
Stuff.
ItemNo Name ImageName
123 Foo 00123.jog
123 Foo 00FOO.jpg
123 FOO FOO123.jpg
456 bar 00456.jog
456 BAR 00BAR.jpg
456 Bar BAR456.jpg
...
I want to do a query to return just one row per unique ItemNo
So the query would return
ItemNo Name ImageName
123 Foo 00123.jog
456 bar 00456.jog
I don't care which one it grabs, the first is as good as the last, as they are essentially different names for the same thing coming from different systems.
I know there's gotta be an easy way to do this, but I've tried things like TOP, DISTINCT, etc and none of them work for me.
View 3 Replies
View Related
Jun 30, 2015
How to write an expression/criteria in a query that will return only ID numbers with alpha numeric values. Example: My Id field contains both numeric 479621, 680530 and alpha numeric 132NAV100, 174NAV26a values. I want to run the query to return only the alpha numeric values.
I do not know SQL nor VBA.
View 11 Replies
View Related
Jun 25, 2014
I have a checkbox that when checked returns all of the yes values for the column but I want it so that when it isn't checked, it doesnt search for this criteria at all, is there a way of doing this? I tried option buttons too but I wasn't sure of how to go about it.
View 13 Replies
View Related
May 15, 2013
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between]
FROM [Main Table]
WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
View 11 Replies
View Related
Apr 5, 2013
I have a subformClientOffers on my frmAllProperties. On the subformClientOffers is a field called Outside Agents. This field allows for multiple values to be selected.
The subfrmClientOffers is in datasheet view.
While the field Outside Agents DOES allow me to select more than one, they post in the field one after another:
Ken Shaw, John Doe, Jane Doe, Harlan Bestlyn
Therefore, the datasheet view of the subformClientOffers causes the field Outside Agents to get very long, much too long to view without scrolling. My client wishes for everything on the frmAllProperties to be viewable without scrolling.
Is there anyway, to cause the field Outside Agents to place a return between each selected name (sort of a wrap text kind of thing)?
One thing I might mention is that the field is a drop down field from tblOutsideAgents. The table itself has First Name and Last Name, but I concatenated that into Fullname on the form, so that when the user opens the drop down they see the entire name. I don't know if that would have any effect on making the 'return' thing work or not.
I can't use this field as a subdatasheet because I already have one subdatasheet attached to the subform to show multiple notes on each Client Offer.
View 4 Replies
View Related
Jun 18, 2013
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
View 5 Replies
View Related
Aug 25, 2005
HI, i have a field in a query called [cost_type], these typically contains the values "principle works" or "additional works" how can i set the query up so that it doesn't return the "works" part of the record? Could i just return the first 10 characters of the result?
thanks
View 1 Replies
View Related
Feb 23, 2006
Each record in my table has six possible sale dates as a result of cancellations. My problem results when a record is sold twice during the queried time frame (SaleDate1 and SaleDate2). Currently my query uses an if statement and if they are in the same time period, it will only return SaleDate1. I would like to see the record returned twice (once for SaleDate1 and once for SaleDate2). Any suggestions?
View 10 Replies
View Related
May 7, 2007
Hi Everyone,
I have a database that I enter time for in the form of military time, I would like to make a query to count the number of entrys in the PM,AM, And NIGHT. For instance if a record has a time of anywhere between 700 or 2299 I would like it to be in the AM catagory, then I would like to total up the number of AM records. Any help would be great. Thanks.
AM=700-1399 PM=1400-2299 NIGHT=2300-699
View 2 Replies
View Related
Jun 23, 2007
Hello all,
Is there a SQL function in Access that will allow it to return (as a field value) the name of the table where a particular record was found?
The reason that I want to do this is because I have a single table (Table A) with building address records counted and broken down by state and by month - but some of the records belong to smaller groups (e.g. Zone 1, Zone 2, etc.) that aren't states, but that I want to count in the crosstab as if they were. There are building addresses on Table A that belong to each of these four groups, so I need to somehow match the records on Table A with the appropriate Zone, and place the name of that Zone in the "State" column of the query results instead of the actual state so that I can then count the results in the same crosstab.
I had the intention of resolving this by creating three tables (with the listings of address information for each Zone #), then matching records in Table A to each Zone table in a query by the address field to get each record's appropriate Zone no.(if available), then using the results of this query in my crosstab - :confused: but I can't figure out how to get Access to create a calculated field that lists from which table a resulting query record came from, so I can't continue.:confused:
Any suggestions, if able, would be appreciated!
View 5 Replies
View Related
Feb 14, 2006
I placed ="G*" in a text field to get all names that begin with G and a blank return was issued. Maybe the manual I have has the wrong info. I have Access 2003 and a Access 2000 manual. Please help.
View 1 Replies
View Related
Mar 20, 2013
I have a query which is a max sum of sales for several sales reps. (If that makes sense?)
Sales Rep, Total Sales
1, 100
2, 200
3, 500
4, 50
5, 150
I need to create a report which delivers the sales rep with the most sales and only that sales rep
e.g.
3,500
View 7 Replies
View Related
