I have an unbound form with a subform which is bound to a query. The subform prioritizes and individually lists all projects my company has on its agenda and the project’s corresponding cost. Is it possible to enter a dollar figure (which would represent our budget) on the main form and have the subform list only the top prioritized projects whose running total costs would fit within the budget figure stated? Any help would be appreciated as I don’t even know if this is possible. Thanks.
I am querying a linked ODBC table with a date column called "db_created_tms" that has dates in formatted like "2/25/2004 8:54:02 PM" , because it is a linked table I can't edit the data type. How can I limit my query results to the last 30 days.
My query without the date limit currently looks like this:
SELECT QBReportAdminGroup_v_txn_po_line.doc_num_h AS po, QBReportAdminGroup_v_txn_po_line.quantity_qnty AS qty, QBReportAdminGroup_v_lst_item.name AS REFERENCE, QBReportAdminGroup_v_txn_po_line.unit_price_amt AS cost, QBReportAdminGroup_v_lst_vendorODBC.name AS vendor, "" AS LOCATION FROM (QBReportAdminGroup_v_txn_po_line INNER JOIN QBReportAdminGroup_v_lst_item ON QBReportAdminGroup_v_txn_po_line.item_id = QBReportAdminGroup_v_lst_item.id) INNER JOIN QBReportAdminGroup_v_lst_vendorODBC ON QBReportAdminGroup_v_txn_po_line.vendor_id = QBReportAdminGroup_v_lst_vendorODBC.id;
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No). So what im trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code: SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU FROM Calls GROUP BY Calls.SKU HAVING ((Not (Calls.SKU) Is Null)) ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
I am having some kind of problem. A query in Acces that is used to find results in the DB is asking 2 time's te input.
And there is no way that the input boxes is asking to do this twice..
here is the Query,
SELECT tblHotlines.Hotlinenummer, tblHotlines.Vestigingsnummer, tblDealers.Postcode, tblHotlines.Datum_hotline, tblBestellingen.Chassisnummer, tblBestellingen.Onderdeelnummer, tblBestellingen.[Uitleverings datum], tblBestellingen.[Vervangende auto ingezet?] FROM (tblHotlines LEFT JOIN tblBestellingen ON tblHotlines.Hotlinenummer = tblBestellingen.Hotlinenummer) LEFT JOIN tblDealers ON tblHotlines.Vestigingsnummer = tblDealers.Vestigingsnummer WHERE (((tblHotlines.Vestigingsnummer) Like [Geef het vestigingsnummer op] & "*") AND ((tblDealers.Postcode) Like [geef evt een gedeelte van de postcode:] & "*") AND ((tblHotlines.Datum_hotline) Like [Geef de datum op:] & "*") AND ((tblBestellingen.Chassisnummer) Like [Geef het chassisnr op:] & "*") AND ((tblBestellingen.Onderdeelnummer) Like [geef evt het onderdeelnr op] & "*"));
Qhat could be the problem that Access is running a Query twice before getting results
Is it possible to have a running total either in a query or using the Running Sum function on a text box on a report that will reset after a specific value. Here is what I would like to have happen:
The RunningTotalCube field to reset when it has reach 2.3 or whatever number comes closest to that number.
I am designing a database for a marketing company. One of my tables has all of the company's contacts. On the contacts form, I have a button that opens up a query that shows the information for the company that contact works for. I have a problem where if I have more than one contact per company, it will return that many results (which is just a repeated list of the same company information). It is an eyesore to look at the same information in two or more rows, so I was wondering, can I make the query return only one result? I've been looking online, and I haven't found this at all. Thanks a lot.
I'm currently trying to create a KPI report (Key Performance Indicator) to log jobs & their response times
I can easily produce a query that gathers all the information needed but I need to find a way to limit the results to 1 record per job as one job can have many records - Primary key being the TaskID field
I need a way to only pick out the oldest record & ignore the rest
For example
A priority 1 job that was logged at 01/05/2008 08:00 that needs to be responded to within 2 hours
Two engineers have carried out the work & logged the following details
1st Engineer responded at 01/05/2008 09:00 and the other returned later to complete the job at 01/05/2008 13:45
I need the query to only pick out the entry for 01/05/2008 09:00 as it's the oldest.
Any suggestions would be most welcome
this is what my query looks like at the moment.
SELECT tblTasks.TaskID, tblTasks.DateReported, DateAdd("h",DLookUp("[ResponceTime]","[tblPriorities]","[tblPriorities]![PrID]=" & [PrID]),[DateReported]) AS DueBY, tblTaskTime.TTStarted AS DateStarted, tblPriorities.Priority, tblPriorities.ResponceTime, [DateStarted]-[DateReported] AS Responded FROM tblBuildings INNER JOIN (tblContracts INNER JOIN (tblPriorities INNER JOIN (tblTasks INNER JOIN tblTaskTime ON tblTasks.TaskID = tblTaskTime.TTLINKTask) ON tblPriorities.PrID = tblTasks.TaskPriority) ON tblContracts.ContID = tblTasks.ContractNo) ON tblBuildings.ProID = tblTasks.BuildingID WHERE (((tblPriorities.Priority) Not Like "P8*" And (tblPriorities.Priority) Not Like "P5*" And (tblPriorities.Priority) Not Like "P3*" And (tblPriorities.Priority) Not Like "P6*")) ORDER BY tblTaskTime.TTStarted;
Property (used for look-up values) Service Type (used for look-up values) Rate Table (uses the Property Table and Service Type Table and is used as a rate look-up table for the daily service table noted below) Daily Service
Using the Daily Service table, I need to choose a property, choose a service type that is limited to the property (100's of service types, but a property may only have a limited number), and enter a quantity, and have the Rate Table return the specific cost of the service. I didn't think this was too hard in Access (2013), but I seem to be hitting a wall and spending too much time figuring it out. Now I am behind in my project.
I have been trying to do this in a table format, but have been unsuccessful in limiting the results of the Service Type combo box based on the property chosen.
Hey guys- I have a database of properties for sale. I want to calculate the asking prices of each record (for sale) against the sold prices of all the houses in the same area. Area is defined as a numeric number, and each property has one assigned to it. My question is this- I have a table of all the properties. Do I run the query and store the results in a specific cell in the table- and then use a form to display those results? Or do I run the query from a form and not store the calculated results at all? I will be adding/updating info on a daily basis, so these calculation results will also change daily. Obviously I am fairly new to Access and trying to figure out how to do this stuff. Using Access 2002. Either way, I assume i'll be using a form to display the results one way or another... Thanks!
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
I wrote some code in Excel to open and run a module in Access. The code executes, but when I check the results I get no records, however if I just step back 1 line and rerun the query, I get records. Alternately, if I run the code twice it will get results the 2nd time.
The OpenCounter opens a Session in the terminal screen then performs a macro that writes to a temporary table.
Here is the code:
Dim db As ADODB.Connection, Rst As ADODB.Recordset Dim Acc As Access.Application Set db = New ADODB.Connection db.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath Set Rst = New ADODB.Recordset
[Code] ....
One thing I tried with limited success was to tell the OpenCounter to open and close the table after it was done writing to it, but that seems like a lazy solution and it also doesn't always work.
I have a Top 10 query (it is based on $ amount) and everything works fine except...say I have 1,000 records and 5 are >$0 it shows those fine but since the other 995 records are $0 it shows "all" of them. I would like to limit the record count in the query to only 10. Within that 10 records if there are $0 records then I would like to show them up to the 10 record limit.
I see both sides I think but you would think that if you say "Top 10" it would be "10".
Hello I have an access 2003 db. I have a csv file that regularly downloads from a propietary system. This file contains about 18,000 records. Some customers have more than one record so there are about 7000 customers. I have linked this table to access to benefit from the regular download changes. About 150 of these customers are accounts I want to filter out of my db. I tried a query criteria Cust# Not "(Cust#) or (Cust#) and so on. Access will not let me have this many in my query. Is there another way?
I am working on my DB for work and am almost finished with my DB. I have a query that gives me totals for days. This is the code that I have been using.
The "TotalTMD" and "TotalLOA" days are limited to 180 days for each table. However what I need to do now is limit the combination of both fields to 180 days. Is this possible?
Since "TotalLOA" is more serious, I would like to use what ever days are in this field and then include what ever days are left over to total 180 days from "TotalTMD" days.
Maybe I could use this as an example:
Name TMDDays LOADays Total days John 190 20 210 Jim 80 90 170 Tony 250 190 440
Should display: Name TMDDays LOADays Total days John 160 20 180 Jim 80 90 170 Tony 0 180 180
I have built a query (Loading_query) that brings in the prefix, number and date_loaded (=null) for each rail car.
I now want to display the rail cars numbers in a drop-down but be limited by the prefix selected in another drop-down on the form.
For example:
If the prefix that was selected = GATX then the next drop-down should use "Loading_query" to only show the rail cars that are empty (according to date_loaded) and have a prefix that = GATX..... Still new at this and do not know how to use a value from the form to limit the query.
I have a table that I need to identify the records in that have specific text in one of the fields, the field also contains other data. i.e. the field (accessdescription) can contain any combination of the following text (Bridge, Report, Email). and I want to list only the records that have email in this field, noting that the field usually contains at least two of the possible entries.
Any pointers in the right direction would be greatly appreciated.
I've attached a zip version of my database (which is still in the early stages of development) as I think it will be tricky to explain what I need. I've studied lots of other threads but can't find the answer I'm looking for.
Basically I have a form "Goods-in" which has a subform in continuous view that lets you pick from a product list. I'm trying to limit this combo to products that relate to this supplier only, but (it seems) because the subform is based on a query I can't update it.
I have tried several approaches but got nowehere. At this stage I don't know whether the problem is with - how I'm linking the parent and child forms - the SQL of the combo recordsource - my relationships - my overall design - the weather! - etc.
I would be eternally grateful if someone could take a look and advise me. The items shaded grey on the forms are just my shorthand for what will be invisible in the final version.
I'm going home now and even though I may read replies tonight, I wont be back in the office until 9am.
I tried to word the title as accurately as possible. What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.
I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.
There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?
I have a query that is pulling information from 2 tables: airport codes and orders. I was able to select the 7 states I wanted without any problem. I am trying to limit the "order date" by using the criteria ">=#1/1/2011#". I want the query to only choose those orders that were dated 1/1/2011 to the present.
No matter what I place in the criteria for the date, the data never changes. I even tried "=date()" just to see if it would change. I tried "Between #1/1/2011# And #12/31/2013#" and the results were the same. This should be a simple task (as were the states) but I don't know why it is not yielding the desired results.
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items. How can i make 3rd query which will give me all but taken items from query1? (of course items from query 1 are in query2) thx in advance
