I have 2 tables with the same structure but some of the data in one field is different
The tables are called Current and New
They both share a field called feesch but in the new table data is updated
The tables have share the same primary key of unique ID
How can I make a query in design view to show the feesch records that don't match?
Thanks in advance
New member, so forgive me if this has been covered before:
Trying to build a Business database (more for the exercise than any desire to run a business) and have the following problem -
Have a Purchase Ordler Line table and an Item table - the item in the PO line is a lookup from the Item table - I want the PO line price to default in from the the item table.
Created the query and generated the form & subform - on screen, the substitution works, the PO line price does get picked up from the Item table, but the PO Line table does not update with this value.
Is there a soltion to this?
Any assistance gratefully accepted
I have created my first Make-table query and it actually worked!!!Yeah! But now, is there a way to make this table updateable each time the query is run?
View 3 Replies View RelatedHere is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
Need Help...
I deleted a field (Senority #) in my table and so on with the queries, forms. reports that had that field. I redone my query so that it would calculate my senority (thanks to all that helped). When I went back into my form that previously had the senority field I could not edit or change anything. All I got were beeps. I do not have any locks on the text boxes, combo boxes, forms, ect. Please help!!!!
I've inherited a rather large Access DB with many tables,forms, reports, and queries. I need to duplicate a process, but I'm having trouble finding the update qry that creates new records in a table.
I could write my own, but I am trying to create an exact duplicate so I'd really like to see the code that creates the new records for a specific table.
I have created a form with two subforms within it.
The TOP subform enables the user to view a specific record (not change it)
The BOTTOM subform enables users to view a datasheet form of all the records and amend them where necessary.
I want the TOP subform to automatically go to the last updated record, meaning that if the user changes the data of a record in the BOTTOM subform it will automatically display the last record on TOP that was updated in the table.
I've looked at the macros available and it only allows me to 'GoToRecord' to either Last, Previous, New etc... Is there any way to go to 'Last modified in the table'??
I have developed an application using MS Access and MySql tables.
The Access forms maintain/display orders that are stored in the MySQL linked tables. The MySql tables can also be updated independently by another process. The problem is that the Access forms do not reflect the backend changes when they are made by the other process.
For the moment I have implemented an on Timer process that causes a requerys for the forms every 5 seconds but this is not very elegant and causes the form screens to flicker.
Is there a better way I can automatically make the front end forms reflect changes to the back-end database when it is updated indepenently ?
When I add new records to the form and close the form, the tables are updated with the new records, but the new records don't show in the form (navigation).
View 14 Replies View RelatedIs it possible for a query to automatically get updated each time a table is added. For example I am looking at prices and each month a new table of information is added for that month and I use a query to view each month next to each other. Is there a way that each time I add a table the query will automatically update?
View 1 Replies View RelatedHello,
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.
Thanks!
Hi,
I have a table with four columns - A, B, C, D
I want to compare the values in columns A, B and C in row 1, with the values in row 2 and if they are the same, I want to copy the value that is in column D in row 1 into column D in row 2. I then want to move to the next row in the table and compare the values in row 2 with the values in row 3 in the same way. This will continue for all rows in the table.
Any help would be greatly appreciated as my VB is not up to much.
Thanks in advance
I Have made a change to a field in my tables. it was was based on ethnic background and originally i had just created the field but had not added in the options ( via adding it into the row sources).
So now the tables field have been updated but unfortunately on the form it has not updated into the dropdown i had created containing the options..
Currently running Access 2010.
I have a small database where we enter audit log data. In my add add screen from the switchboard, one of the fields is the user the audit is assigned to. So that our group wouldn't be typing their names a bunch of different ways I created a reference table called "Ref Data 005 - Contact Name". So i pre-populate the Contact Names such that they will be more consistent.
The add works perfectly. I built all my screens using the forms wizards.
Now for the modify routine. If the user wants to go in an modify one of the other data elements, I force them in the wizard to do this modify by choosing thier name. Therefore, when i built this screen using the wizard I reference the "ref table" mentioned above. I then throw open another form displaying all of the fields based on the records that it finds for that user when they choose the name from the Ref data 005 table. Hope that makes sense. Ie.. they choose the records they want to possibly modify by first choosing their name from the ref table.
It correctly pulls the records for whatever user you choose. However, when you make the update and save, it is changing the Ref table name as well.
So for example if three users were in my ref as :
bill
joe
john
And you launch the form, lets say joe shows up in the list, when i change it to bill to retrieve bill's records , it will do that but when i make the change, then Joe is changed to bill in the reference table.
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.
Your prompt response is greatly appreciated
Thanks
Hi there
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.
Thanks in advance for the assistance
Craig:)
I'm willing to be this is simple, but I haven't been able to figure it out.
I have created a database with one table, tblUserData. I have one form called frmLogin.
tblUserData has two fields, Username, and Password.
frmLogin has two textboxes for user input, User and Pass, and has one control, cmdLogin.
What I am trying to do is - When a user types their username and password, and clicks on the control
button, the value of User.Value is compared to the Username field of the UserData table.
If a matching record is found, then Pass.value is compared to the Password field from the matching record.
If they match then the program proceeds.
Can anyone point me in the right direction please?
I am trying to create a query based on a transaction table which compares the time between two types of transactions.
The initial record would have "A' in the type field and the second record would have "D" in the type field.
each pair of A and D records would be matched on o_time and idnum fields, and I would want to pull each pair and us the datediff function on c_time, using records marked with A as the initial time and records marked with D as the followup time.
Is there any way to accomplish this in a single query or do I need to create two subqueries?
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 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.
View 6 Replies View RelatedI am running into an issue when trying to compare data stored within the same table.
tblParcels - holds data relating to an individual parcel of land; unique identifier is ParcelID. No repeating data.
tblValues - holds data relating to the value(s) of each parcel; new values entered annually; unique identifier is TaxID; has field to link to tblParcels. One parcel can have multiple entries for each year tracked.
I have two unbound text boxes on frmMainForm where users can enter Tax Years to compare (txtYear1 and txtYear2). Theory being that users will be able to select any two tax years and compare the parcel values.The report (rptMaster) is built using three queries and is used to show the change in value between txtYear1 and txtYear2:
qryYear1 - pulls all values from tblValues where the TaxYear equals the year typed in txtYear1.
qryYear2 - pulls all values from tblValues where the TaxYear equals the year typed in txtYear2.
qryCompare - joins the results from qryYear1 and qryYear2, along with other common fields, and populates rptMaster.
My problem lies when a parcel does not have any values for one or both of the years entered into the text boxes. I completely understand why, qryCompare can't show any records if there are no results coming from either qryYear1 or qryYear2. What I am not getting is how to work around this issue.I would like for the report to show "$0.00" for the value if there is not a value for that Parcel in tblValues for the year entered.
tblValues example data:
TaxID ParcelID TaxYear Value
1 5 2011 100,000
2 5 2012 200,000
3 6 2012 75,000
In the example above, when comparing 2012 values to 2011 values, my report shows the values for ParcelID 5 but not for ParcelID 6 as it does not have a 2011 value (and won't since information about that parcel didn't exist in 2011).
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 am making a dp for end of day sales, and i want to compare data in a table to the numbers inputted in a form and see whether this value is higher or lower. the table displays the target sales figures for each day, and the form allows the area mangers to enter in the current day figures.
View 3 Replies View RelatedI'm working on creating an events log. In my table I've got a field that pulls the individual's username (that they use to log into the computer) and logs it against their log entry.
As part of my query (for my daily events log report) I'm pulling up said username.
I'd like to give access to the specific user to edit his (and only his) entries from the report, but I'm getting a type mismatch with the below code:
Code:
If me.UsernameID = "KraussLJ" Or Environ$("Username") Then
DoCmd.OpenForm "frmShiftLog"
DoCmd.FindRecord Me.ID, acStart, , acSearchAll, , acAll
Else
MsgBox ("You are not authorized to edit this entry")
End If
(before you ask, "KraussLJ" is our ops manager who insists on having final editorial control over the shift log before sending it higher up the chain)
I can get the msgbox to run if I block out the
Code : Environ$("Username")
, but beyond that I'm a little stuck. Should this be a dlookup function or am I overanalyzing this way too much?
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:
Flammability
Rating 4
Product 1
Product 2
Product 3
Rating 3
Product 1
Product 2
Product 3
Rating 2
Product 1
Product 2
Product 3
Health
Rating 4
Product 1
Product 2
Product 3
[code]....
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 have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:
Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:
1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.
2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" .
3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn"
4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete"
5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .