Date...............Item...................Status
200601............A........................OK
200602............A........................Not OK
200601............B.......................OK
200602............B........................OK
200602............C........................OK
I want to construct a query which can only show result as follows:
200601............A........................OK
200602............A........................Not OK
200602............C........................OK
..by comparing the status change
Any brilliant idea? (preferably not using dlookup)
In tblFacilityType, the FacilityType field is a combonation of ProviderType and Location. Each of those fields are alphanumeric. What I want to do is have the FacilityType field in the the tblProviders table to look at the Location and the ProviderType fields in the tblFacilityType table to determine what the FacilityType field in the tblProviders table should be.
I have a form that users put in information and then it updates to a table. It works fine but i would like to add something. Right now there is a text box called item that the user puts in the item number. The problem is that sometimes they put in a wrong number. What i would like to do is have it when they hit the update button to compare that item number to a table that lists all of them. so if the text box doesnt equal one of the numbers in the table dbo_item and the field itm_num, i want it to give them a message box with an error.
I am programming an Acess Database, the primary data come from an excel sheet. Since Access one to many relationship only works for primary key and another field in another table as foreign key, I can't have normalized database.
I will explain what I mean with a hypothetical situation.
So, let's say I have two tables: WindowT1, CustomerT2.
WindowT1 has fields: WinID (the pk), WindowName. CustomerT2 fields include CustID (pk), CustomerName, WindowName, WinID.
So, in table "CustomerT2', WinID is the foreign key and I need that field filled in to make one-to-many relationship between these two tables to work. CustomerName and WindowName will be imported and prefilled already from an excel file with append query.
My question is can I make a update query (after the initial append query that brings in the data into Access) such that the criteria is matching WindowName (from CustomerT2) and the update would be the corresponding WinID (the pk number from WindowT1) that would be filled in the field WinID (in CustomerT2)? I know for this to work tables must have relationship and I did that.
Is there a better way to accomplish this task? i.e. through a form (remember info will be prefilled, so can't use combo box to fill in another form field) or maybe can I do this using the first append query (that brings in data)?
I am not good at Macro or VBA. The only thing I have done in code builder is After Update event in forms while using combo boxes. So, if there is no easier solution, I will try to deal with codes.
I need to run a condition that compares 2 field quantities together based on the same item. If they both equal the same then i want it to run a code and change a property for that record.
I will have a OrderQty and a Total receivedQty when these 2 equal each other then it updates the items Status to Received. What is the best way to accomplish this? in vba or queries/sql?
I have a table that shows Part Numbers. Each part number can be listed multiple times depending on how man Build ID's it is associated to. Example: Part Nubmer 123456 could be associated with: Build Id's F187, A910, F001, C810, E010, C809 or it might only be associated to 1, 2, 3, 4,or 5 of those Build Id's. What I want my query to do is find any Part Number that is only associated with anything less than all six of the Build Id's. Example: Part Number 123456 is only associated with: Build Id's F187 & A910 or only F001 & C809 or only E010. If this was the case I would like for that Part Number to show up in the Query along with whatever Build Id's it is associated with as long as it is not associated with all six Build Id's. The Build Id Field is also part of the table.
Is there a way to compare the value in one field with the value of the same field in the next record? I can do it in Excel by using the following formula:
=If(A1=A2,"Match","No Match")
But I need to be able to do this in an Access query?
Hello, I am just wondering whether there is any query I could use to compare the data on, say, whether all data in Table A are in Table B although some records in Table B may not be in Table A and give me the record that is in Table A but not in Table B.
sql1="select * from Exam where StudentID ="+s1+"and dateField='"+dateField+"'";
I am trying to compare the date given in the text field and the date in the table . I am getting Data type mismatch in criteria expression. Can anyone suggest me a correct query .
Hi all I want to view vehicles due for servicing in the next week. The queries giving me this information work fine. I have a form that I can select, from a drop down box, either 1,2,3 or 4. A report is then run and emailled to the appropriate people. After testing to see what was the easiest way to remember what the last week was (I tried setting the combo box to remember the value but it did not, unsure why, I also tried recording Mondays date in a field in a table and assigning each Monday a value 1-4 then running a query...long story short, to time consuming) I have used a table with one field to hold the value and after emailling the report, an update query adds one to this field and it is displayed above the combo box in a txt box. I want to reset the value to 0 if the value equals 4 before the update query runs. Is this the best way to do it or can someone suggest another way of doing this. Thanks in advance Craig
Hi, I am trying to compare two columns' values within the same QUERY table, but I kept on getting the "Data type mismatch in criteria expression" error. What am I doing wrong?
Here is my Select Query Statement: SELECT qryDedparmDedetail.EMP_ID, qryDedparmDedetail.[Employer Amt], qryDedparmDedetail.[Employer Actl], qryDedparmDedetail.[Admin Amt], qryDedparmDedetail.[Admin Actl], qryDedparmDedetail.[Employee Actl], qryDedparmDedetail.[Employee Amt], qryDedparmDedetail.FirstOfSTATUS, qryDedparmDedetail.FirstOfAGENCY, qryDedparmDedetail.FirstOfTITLE, qryDedparmDedetail.FirstOfFORMAT_NM, qryDedparmDedetail.RepUnit, qryDedparmDedetail.FirstOfDEDTYPE_CD1 AS Expr1, qryDedparmDedetail.SumOfNBR, RepUnit.REPUNITDESC, qryDedparmDedetail.LeftType FROM qryDedparmDedetail LEFT JOIN RepUnit ON qryDedparmDedetail.RepUnit = RepUnit.REPUNIT WHERE (((qryDedparmDedetail.[Employer Amt])<>"Employer Actl") AND ((qryDedparmDedetail.LeftType)="01"));
I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?
I am putting together a database for work, I have a table that is used to refer to (this table is part of our production system) in this table it lists products run, dates run and who has run.
I have the training records data table, this lists; who, machine and status: "trained" "refresh required" " not trained"
I want to build a query so that it searches the production table for the last 6 months worth of records & if an operator that is in the training table (WHO) as status (trained) is not in the production table it outputs a "to be refreshed" report or amends the records from trained to "refresh required" ...
I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.
For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
I inherited a database that has two tables that are structured identical; one called tblcurrentdata and the other called tblpriordata. The user wants a report or export query that only shows records that has differences between prior weeks data and the current weeks data. The tables have 12 fields of data in a record per quote number. The user wants a query or report to identify the QuoteNum and any changes between the prior and current records. The table structures are as follows:
Code:
tblCurrentData/tblPriorData: QuoteNum CoName State ZipcodeNo priorityColor
[code]...
QuoteNum 12345 field 5 (prioritycolor) changed from red in the prior week to green in the current week and field 7(POC) changed from Scott in the prior week to Jonson in the current week.QuoteNum 23451 did not have any changes therefore does not need to be listed in the query/report Unmatched query doesnt work because it does not compare multiple fields. I tried to structure a union query and use <> in each field but got too tedious and didnt give the expected results.
I need a mechanism that takes the excel spreadsheet or table and determine if there are any new deal groups. (In this spreadsheet I allready have a deal group field) I will be using Book and DealGroup as combination primary key to compare against the table "8 -- YTD Principal Investments" which will hose the YTD numbers and serve as a historical table we will update throghout the year.If there is a new deal group the append into table "8 - YTD Principal Investments"
Please help with query/report. I have the following problem.
One student evaluates course before and after. There is several questions related to this evaluation (all are same before and after) and for one question there is 5 possible answer (stored in option group)
I would like to build a report or a query where I can compare data from each student (before and after) for all questions. Is this possible to do in access.
So, StudentA: questionOn: answer: one columen: before, second column: after…. Next question.
Hi there! I'm bulding this database where the user will have to insert a full phone number. What I would like to know is? if is there any way where Access can pick the first 5 digits of the number to compare with information already imputed in. by example if the user input the number 01942789789, access pick the number 01942 to look for a match? I know I could do with two diferents fields one for the code number and other for the number, but my boss only want one field. Is there anyone that could help me
I've made 2 tabels tblIn and tblOut, Now I want to make a query which get the differences between the number of product A in tblIn and the number of product A in tblOut the same for product B,.... It's possible that there is nothing in tblOut from product X so the query just has to show the number of tblIn of product X, I've tried a few things but i can't make the query can anybody help me???
I have two list's of names. I'm able to run a query to find the matches but the ones that don't match i also need to id? For ex. List a has 200 names List b has 130 names, how can i find the 70 names that don't match?
Have 2 Tables that contain the same information, one table was used during registration and had the Data changed
Question:
How do I pull a comparison query to match the registration table to the Main table and pull only the data that is different so I can update the main table with the new Information? :confused:
Hello, I would like to put a restriction on the SQL statement such that if one particular field contains the character "MO", then we take in the record, else we ignore them. May I know how could this be done in the "WHERE" part of the SQL statement? I do not think SQL would read things like Instr(). Thanks!
I'm trying to setup a delete query that cleans out the records in one table that are also in another table. I was trying to use the address field (would use name but one table splits it First and Last and the other doesn't) but I just can't seem to run the delete query when using a join
anyone have any ideas on how I could setup this delete query?
