Queries :: Drawing Register Database - Lookup Specific Criteria Based On Date
Aug 11, 2014
I have a drawing register database which notes the revisions for all drawings issued. One drawing can be revised several times so I have a details table which notes the revision letter, date issued and the notes for each revision. Using this table I created a max date query to show the latest issue date and revision for each drawing. This query is used for a transmittal form for issuing drawings. Both the form and report is working perfectly.
The TBLTransmittal consists of the following fields
ContractName
IssueDate
SubSup
DwgNo (multi-value checkbox)
Using the TBLDwgRegisterDtls or the query QRYDtldDwgReg (which includes the drawing titles) I would like to create a query that would look up the latest revision at the date of issue and show the revision letter.
I started a query combining TBLTransmittal and TBLDwgRegisterDtls and in the criteria for DateIssued of the TBLSDwgRegisterDtls (which is when the drawing was issued by the architect to the contractor) I put "Not > [IssueDate]". This is filtering the information to show revisions issued up to the date we (the contractor) issued the drawing to our sub-contractor but I need now to pull the max revision only from this information.
Before I go I will give you an example of what I want from the end result
Drawing BK2-02 rev. D was issued by the architect to the contractor on July 17th. This drawing was in-turn issued to our sub-contractor (Sub "A") this Saturday, August 9th. The same drawing was revised this morning and rev. E was issued to Sub "A" today, August 11th.
I want the end report to show that Sub A received Rev. D on 09-08-14 and Rev. E on 11-08-14.
I'm wondering if another max date query on this new query would work? I'll try and see what happens.
View Replies
ADVERTISEMENT
Jul 26, 2015
I am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:
1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)
2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)
I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")
However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.
I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.
View 8 Replies
View Related
Dec 14, 2013
I don't know what it would be called or even how to start doing it in access other than it requires a criteria here is what I'm trying to make happen with access
name date yes/no criteria would be set to date > 180 days then it would equal value of No < 180 days then equal value Yes
If i set a today's date value on the database and then criteria is based of the value in the date box with the above information how would this be done.
View 4 Replies
View Related
Jun 11, 2013
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies
View Related
Jun 14, 2013
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.
View 1 Replies
View Related
Apr 16, 2015
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.
View 2 Replies
View Related
Mar 30, 2015
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 code, Price, valid from, valid to
AAAA, 12000, 01.01.2012, 31.12.2012
BBBB, 16600, 01.01.2012, 12.06.2013
AAAA, 13500, 01.01.2013, 28.08.2013
AAAA, 11500, 29.08.2013,
BBBB, 17600, 13.06.2013,
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.)
View 11 Replies
View Related
Aug 21, 2013
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.
View 4 Replies
View Related
Nov 17, 2014
I have a task completion database. Each record of model consists of an expiry date. I need to review and update the records before the expiry date. Each model may have more than one record as I have updated the models a couple of times. I would like to select all of records with the expiry date less than 30 days so I can plan to review and update them on time. How to select all these records.
View 2 Replies
View Related
Jun 24, 2014
I have a report database that provides my company with clients that took our training modules and notify us of which clients completed our trainings.The clients can complete training in 3 States and "Passed" means they are good to go.
I download an excel report daily and import it to Access on a daily basis. Problem is the Report is over 8,000 rows long and basically I just need the clients that completed training within past 48 hours. The excel report provides a date of completion.
code that only pulls those clients that "Passed" within the last 48 Hours. Here is my SQL Statement I use on the RecordSource.
SELECT report.SPS, report.FirstName, report.region, report.id, report.AZ_Cert, report.AZStatus, report.CA_CERT, report.CAStatus, report.OR_CERT, report.ORStatus, report.Completed FROM report WHERE (((report.Completed)=False));
The completed checkbox removes the record from the cert queue. How can I do this more efficiently? I think I have it right.
Private Sub Completed_Click()
Const cstrPrompt As String = _
"Are you sure you want to complete this record? Yes/No"
If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
If Me.Dirty Then
Me.Dirty = False ' save the record
Forms!frmRecertView.subfrmRecert.Requery
End If
End If
End Sub
View 6 Replies
View Related
Aug 31, 2013
I want the lookup to be performed with criteria based off of the same row that record being selected is on.
I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.
You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521| KNNRLABR
The lookup displays products available where the indexed products match the same location. Here is what the query returns:
| Location |Products Available|
| KNNRLABR| Product 21 |
| KNNRLABR| Product 9|
| KNNRLABR| Product 7 |
| KNNRLABR| Product 3 |
This is the affect that I want to achieve but, if I add another location like so:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521 | KNNRLABR |
| Store 2 | 3609061624 | VANCWA01|
The lookup returns all products available for all the records for each location entered rather than based off of the selected record.
| Location |Products Available|
| KNNRLABR | Product 21 |
| KNNRLABR | Product 9 |
| KNNRLABR | Product 7 |
| KNNRLABR | Product 3 |
| VANCWA 01 | Product 31 |
| VANCWA 01 | Product 8 |
| VANCWA 01 | Product 4 |
| VANCWA 01 | Product 3 |
| VANCWA 01 | Product 1 |
Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
P.S. Here is a copy of the SQL statement I'm using right now.
SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI
FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet]
WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
View 6 Replies
View Related
Oct 28, 2014
I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.
View 1 Replies
View Related
Dec 19, 2013
I am attempting to develop a DB to track hardware assets, e.g., servers, switches, storage devices, etc... I am at the very beginning of the development/design process. I haven't gone beyond the paper stage yet. I am trying to figure out how I would set up the main Asset table so that when I put in a particular vendor, the model field will list models only for that specific vendor. i.e., when I put in IBM in the Vendor field I will only see IBM models in a drop down for the Vendor_Model field.
I'm pretty sure this will involve a Lookup field. But, how do I restrict the lookup of models. Would that be with a query?
View 2 Replies
View Related
Mar 20, 2013
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies
View Related
Jun 6, 2005
Hi,
Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.
Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.
I hope my explanation is clear.
Thanks upfront for any suggestions!
BJS
View 4 Replies
View Related
Mar 27, 2013
I would like the "DateOfConfirmation" to populate with today's date when "SSurvDiagThisYear" is Confirmed.
The DateRecordCreated is a simple =Date() that populates when the record is entered into the table.
SSurvDiagThis Year is the case outcome - Pending, Probable, or Confirmed. It is possible that the case could be confirmed on the same day it was entered into the table but that is RARE.
I am hoping for the The DateOfConfirmation to capture the date the case is confirmed so that I can gather some duration between the case being opened to confirmed.
SSurvDiagThisYearDateOfConfirmationDateRecordCreatedIdentificationNoPending12/31/2001269Pending4/1/1999270Confirmed7/29/2001338Pending5/14/2009375Confirmed2/20/2012440Pending3/30/2001543Pending7/1/2000552Confirmed3/30/2001596Pending8/3/2001649Pending6/15/2001672
View 1 Replies
View Related
Sep 16, 2013
Is it possible to have a query that uses criteria to pull data from a specific table?
For example: IIf([Result]'"Negative",(tblNegative goes here),IIf([Result]="Positive",(tblPositive goes here].
The tables are just text, but the query would be too long if it was used.
View 2 Replies
View Related
Oct 11, 2013
How to sort a field of data in a table that contains the letter S that is always in a specific place in the sequence? For Example:
13123S0-BAX
13124E1-ARZ
13122X2-THX
12134S0-GAB
View 2 Replies
View Related
Aug 6, 2014
I have a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.
Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.
View 1 Replies
View Related
May 28, 2014
I am trying to do a lookup from a field from one of my databases (DB1) onto another field in (DB2).... I have been using excel to do vlookups but a report that I do weekly I do atleast 7 lookups for the past weeks... So I am trying to find a way in access to do lookups and save those lookups each time i run the report... Do I have to create the query and make the ID the primary key on both the databases and then create a relationship?
View 7 Replies
View Related
Jul 30, 2015
Basically, i have a table ("Transaction") with payment date and another table ("Control") with accounting dates and corresponding year/month.
Objective: I need to know which accounting year or month these payment date fall under.
Example: If the payment date is 18 Dec 2013, the accounting year should read as 2013 and the accounting month should read as 12.
In excel, this is very simple using vlookup.
I tried for hours using access dlookup query and i'm still stucked ..
View 4 Replies
View Related
Aug 16, 2005
I have a table that stores records by "return date". I would like to create a delete query that removes all records that fall outside of the date range entered on a specific form (frmMainMenu).
I can deleted the records that match the form criteria, however that is not what I need. The criteria used to delete the records was:
Between [Forms]![frmMainMenu]![from date] And [Forms]![frmMainMenu]![to date]
Let's say that the frmMainMenu contain the date range of 8/1/05 to 8/10/05, however the table contained data from 6/1/05-8/14/05, I would like for the query to delete all records that have a date 6/1/05-07/31/05 and 08/11/05-08/14/05.
Any assistance would be greatly appreciated. Thanks in advance for your help![/SIZE]
View 1 Replies
View Related
Oct 2, 2006
I have a database with the following information:
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
Thanks
:o
View 14 Replies
View Related
Apr 21, 2008
Below is the SQL I have on a Union Query. Each Query is
based on a date range. The first Query date is Planned
Immplementation Date. The second Query date is Revised
Planned Implementation Date. If the Date in the Revised
Planned Implementation Date is higher than the EndDateTxt
Date Range I do not want the results to appear for that
ECN. Any suggestions on how to accomplish this?
SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number]
UNION SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number];
View 5 Replies
View Related
Apr 8, 2013
I have a form in which I want to capture today's date when another field on that form is changed from active to Closed. In excel I would write an IF statement but can't get my head round what I would do in Access to achieve the same result. basically it's if (field name 'status')=(criteria 'closed') then populate field (date closed with 'today's date').
View 7 Replies
View Related
Jul 27, 2015
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
View 4 Replies
View Related
