Have devloped a database to log all support issues for an application I work with.
I periodically update the database by importing from another database
for example my main table tbl_issues has fields IR_NO, DESCRIPTION and STATUS
I then import data from tbl_import which also has these fields. The following query works fine:
UPDATE tbl_support
SET tbl_support.STATUS = tbl_import.STATUS
WHERE tbl_support.STATUS <> tbl_import.STATUS
AND tbl_support.IR_NO = tbl_import.IR_NO
However I also want to do this at the same time:
UPDATE tbl_support
SET tbl_support.DESCRIPTION = tbl_import.DESCRIPTION
WHERE tbl_support.DESCRIPTION <> tbl_import.DESCRIPTION
AND tbl_support.IR_NO = tbl_import.IR_NO
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 need to campare two tables. if there are new records from table A would like to add it to table B. Also if there are any changes in a field of record i would like to see the change in table B. thank you Mark
Hi I'm using MS Office 2000, I have an Access database table that contain many fields, namely I need to update FieldA from an Excel Spreadsheet according to the specified primary key of CustomerID. Is there a way to write automate process to import the records from an excel spreadsheet and then place those records in FieldA of the Access Database according to the corresponding CustomerID? The reason I ask is that these two fields are not exactly in the right order in my spreadsheet and my table and to do it manually will take lots of effort.
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
i import data into a Landing table, this will always contain d days woth of data, i then run an apend query into another table were the records are stored up to 20 days, the problem i face is some of the record data may change for eample a field called ShippedQty may be 0 in a monday but on tuesday it may say 5
Import on monday Product ShippedQty Date 123____ 0 ________01/01/2013
Import on Tuesday Product ShippedQty Date 123____ 1 ________01/01/2013
I need to look at any changes and updatein the master table, is there a way to do this.(I cannot change the import as its the only way i can get the data)
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 have a date field, ID field and a memo field (I have to have this memo field). I want to compare the memo fields by two certain dates and find out what doesn't match and if the ID field is blank. I have been trying with quaries but no luck.
I am not sure what forum this would fall under so I put it in general. feel free to move.
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 table "Customer Plans" with 100s of rows of data with 3 columns
'Name', 'Company', 'Plan Type'
I want to create a new column or table "Customer Details" with the the column name 'Customer' under the following criteria:
If the data in 'Customer Plans.Company' Column meets a certain criteria as in = "Corp&Gov" or "Head Office" or is "blank", I want it to populate the new column/table "Customer Details.Company" with the corresponding data in the 'Customer Plans.Name' column
e.g. if Name = Anna B Company = Corp&Gov then Customer = Anna B
I want to populate 'Customer' with Anna B for that record and so on down the list of data in 'Company' column.
If however the 'Company' column doesn't meet the criteria, I want to populate the new column/table 'Customer' with the current data in 'Company'
if Name = Anna B Company = Dealer then Customer = Dealer
This is probably quite easy but I cannot make it work so would appreciate some help. I have a table containing vehicle information. I have 2 fields, Current Mileage and Max Mileage. I want to do a comparison of the 2 fields and should the Current Mileage be greater than the Max Mileage, the query returns the Vehicle Registration. Test data has been entered in the table I am querying and I was expecting 1 value to be returned. However the query as I wrote returned either all the vehicle Registrations or none of them. Can someone help with this please.
In Table A Badge_Number has entries 101, 102, 103, 104, 105
In Table B Badge_Number has entries 102, 103
How can I do a query to get a list of entries that are in A but that are not in B. (Query result would be 101, 104, 105)
This is the only thing I have to do in Access, so after trying to learn this for a while, I have decided it would be much easier to get help at this time.
In one of my tables I have a field with the following characteristics:
Listbox row source: SELECT Category.CategoryID, Category.Category FROM Category ORDER BY Category.CategoryID; row source type: table/query bound column:2 column count: 2
This gives the user the opportunity to see the category as well as the categoryID when choosing, however once selected only the CategoryID (which is a number) is visible.
I would like to put the same option on one of my forms, but Im not succeeding at this. How can I do this? Is this possible?
This leads me to my next question: If I want to load the information from the form and compare it in VBA with the one from the table, is it seen as a number or a string? I hope it is clear.. Any thoughts on how to deal with this?? Thank u.. Stacey
on "After update" event for Confirm password a code needs to find if both Passoword and confirmpassword are having the exact character (Case sensitive).
Below code doesn't recognize the Case. It allows as long as both words are same:
Dim StrString1, strString2 As String StrString1 = Password srtString2 = ConfirmPassword
I work in mental health, and when we put in a request for a consumer to receive PSR & BST services, we put the above information into our database. Each record identifies which consumer the authorization is for, a start date and end date for them to receive services, and the amount of two types of services (PSR and BST). When one period ends, we request another period, such as in the first three records above, and then the final 2 for another consumer.
What I need to be able to tell is how their service authorizations change. For instance, if I were to compare the first and second records, it would tell me that PSRunits decreased 4/22/13-7/21/13 from 1/21/13-4/21/13 levels.
I have a query that displays the records for each consumer for the current period, but I can't figure out how to identify the previous period's number of PSRUnits or BSTUnits.I've tried adding the following fields, but this isn't working.
PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart]) PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])
MS Access 2013: I have two database tables as below:
tbl1_MainDB --- It has a field named as "City" where I get huge data for some city names. Sometimes This field may have some unknown/new names which are not listed in our 2nd table ("tbl2_RefrDB")
tbl2_RefrDB --- It's a reference table which has raw names for cities, and then standard names of their city and state in another fields.
Target --- I want to create a VBA prorgram (Sql query) which can look from tbl1_MainDB.[City] to tbl2_RefrDB.[Raw_City] field, and if found then pick the "Standard_State" and "Standard_City" record values from there, and update into the 1st table "tbl1_MainDB".
...if not found in "tbl2_RefrDB" table, then user can be informed & ask for updating the new/unmatched city record as a new record in this table.
I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:
PRODUCT table: ProductName Chemical ChemicalAbstract PhysicalState NFPAHealth NFPAFlammability NFPAReactivity
qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.
The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.
In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:
and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.
I don't have any database experience whatsoever so please go easy. I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device". The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
strSQL1 = SELECT FieldA FROM table WHERE FieldA = xxx strSQL2 = SELECT FieldB FROM table WHERE FieldB = xxx
I need unit this two strings to third string strSQL3. I must have result StrSQL3 = SELECT FieldA, FieldB FROM table WHERE FieldA = xxx and FieldB = xxx
