I have 2 tables one Access, one FoxPro both containing similar information. The idea of my query is to compare both tables and update the Access table if the value in the FoxPro table has changed. This works fine for a number of similar queries that I have. However, intermittently, this query will see two values as different when they are exactly the same and replace the value in the Access table with a seemingly random value. I know the "Random" values are from other records within the table but cannot see any link between the values.
I have a table which is used to record electric billing period dates, total kWh and costs for a list of locations each month. I would like to compare the quarter total kWh and costs for this year "2013" against same quarter last year "2012". I have created two queries, the 1st is capturing data from 2013 and the 2nd is capturing data from 2012. I created a 3rd query which joins the 1st and 2nd together by month where fields match. Both tables have the same fields, however there is more records in 2012 than 2013. How can I join the tables together so months that have no data are left blank?
I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].
The idea of the table is that you can input and update the value of each product on a given day.
I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..
A picture of what the table looks like is attached for reference.
I have two identical Access tables, for example tblDataYesterday and tblDataToday. The data from tblDataToday has updated data changes in it that are different from tblDataYesterday. I have a maximum of 100 fields to review. I want to compare the two tables and display in a query the differences between the data from both tables.
I have a table that has a date field set to text. I use the Mid function and get just the month in a query. I have a textbox on a form that when the user adds a month or removes a month from a selected listbox it adds or removes that months number from the textbox.
I want to us that text box on the form as the criteria for the month (the Mid function getting the month) and I can seem to get it to read what's in the textbox and use it as the criteria. I can manually type in a number and it works fine.
I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.
Hi all. I have a Quotation (Table), Quotation Details (Table), with One to many Relationship. Quotation Details(Table) selects info from Products(Table). Then I have an Order (Table) connected with Quotation (Table) and Order Details (Table) for ordering products. What I want to do is to compare, when i make an order, the Quotation Details with the Order Details. For example in a a quotation the customer is quoted with 4 piesces of modem. Then the next day he comes back with the order but now he wants 3 modems. I would like to display a warning to the user or smt like that, inform him that the quotation details are different from order details. Is it possible?? Thank you in advance.
I have a database where I need to take the sum of all income and categorize it to find the commission rate according to what the sum is. For example, if the sum is between $0 and $7,500, the level is "A" and commission rate is 5%. If it's between $7500.01 and $15000, the level is "B" and commission rate is 4.5% and so on. I have this working with a switch() statement, and it finds the correct level exactly as I want it to.
Here's where I'm having a problem: if the sum of all income is $9,000, $7500 of that needs to be level "A" at 5% and the other $1500 needs to be "B" at 4.5%. How do I get that to split up?
Hi, I have 2 tables with similar data. However I need to compare this particular field called CompanyName in both tables. Reason being human error/exposition data errors. For example, Chef Kitchen Holdings Limited, in one table it is Chef Kitchen Holdings Ltd while the other table is Chef Kitchen Holdings Pte Limited, they both are the same but Access recognise them as 2 distinct datas.
Therefore I need help in comparing datas between these 2 tables for this particular field. So long as there is 75% similarities, one of the 2 tables will have the data replaced.
Is that possible using Access? if not, how about excel? please suggest.
Hi guys, i'll try and explain this as best i can....
I have 2 tables:
CurrentMonth PreviousMonth
Both tables hold financial information by policy number. What i am doing is working out financial movement on a month on month basis. For example, if in previous month the financial position was £100 but in current month the finances had moved to £150 then the movement is £50 for the month. This i can do no problem by linking the tables by policy number and extracting the financial position and subtracting one from the other to give me a movement. However, in some instances there will be some policies that appeared in PreviousMonth but have since been cancelled back to policy inception so is not appearing in CurrentMonth.
Problem: I need to be able to bring in the policies on the previous month that are not appearing on the current month (due to cancellation).
I want to compare two queries as follows: Select all the records in query1 where the value in fieldA does NOT also appear in any record in fieldA of query2. Is there an easy way to do this using the query tools or will this require a vba function?
I have 2 tables. Quotation Details and Order Details. As you fill in the Order details Form I wish to compare fields from the Quotation Details Table. Is this possible? Please advise. Thank you.
Hi - I'm working on a query, and can't seem to get it to work correctly. I have a table which contains 2 social security number fields - one for a dependent/employee with benefits, and one for the originally insured (for example, if an employee passed away, the dependent would be on the file, and the originally insured data would be populated with the original employees data). There are also cases where the employee and dependent exist in the table at the same time. I need to check the table, record by record, to identify any cases where the two SSN's are the same in any given record. The query is currently finding an employee's SSN on their record, and also finding it in the originally insured field of the dependent's record and showing it as an error. I only need to know if they occur for the same record, not for another record in the table. (I hope this makes sense!!!)
Is there any way that I can do this??? Any help would be greatly appreciated!!!!
I have got 2 access tables named Table1 and Table2. There is one field common in both the tables and that is "Reference" . So I need to match the data in Reference field in both the tables using VBA.
steps to perform:
1. Check each Reference in Table1 against all the values in "Reference" field of Table2.
2. if match occurs then store all the details of that Reference in Table3.
There could be more than 1 match of that Reference in Table 2 and we need to check the whole Table2 for each "reference" value in "table1".
As a Access newbie I am looking into a problem that I hope to solve structurally and "transparently" via Access in stead of Excel.
I would like to compare two similar tables for different months. I am looking for:
+ loan id's that are new in table(t), i.e. not exist in table(t-1) + loan id's that exited: that is exist in table(t-1) and not in table(t) + loan id's that stayed in both
I have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.
I am trying to build a an equation in a query field, which compares each quantity:
Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].
If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Variance of the above equation that would do it?
I have a text box field (txtLoanSN) for users to input serial numbers, but they can be of different formats depending on the type of service.
Examples are: Type A - 020/2005, 001/2006 Type B - C2001/2006, C2006/2006 Type C - B1001/2005, B1021/2006
I would like to perform a series of actions based on the type of serial no. e.g. disabling another textbox (txtPNo) if txtLoanSN is like Type B or Type C.
We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.
Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables.
This may be a little difficult to explain but here goes:
I have a table in access 97 with the field Account. An account name is usually written as 0000000_A, but it MAY also have a secondary account and is usually written with the same first series of digits but ends in B e.g. 0000000_B. Therefore, we could have an account 0023654_A and 0023654_B.
Now for each account name there is another field called Items which are chosen from a combo box on a form (item 1, item 2, item 3, item 4, item 5, item 6, item 7 and item 8). An account may have more than one item.
Now if account B exists then it MUST contain the same entries as account A but can also have additional items. So we could have a situation where:
Now what I am trying to implement is that if the core items (these are those that are contained in both - which would be item 1, item 2, item 3 and item 4 in the above example) are changed in either accounts making them not equal then a warning message should come up stating so. IT IS IMPERATIVE THAT THE CORE ITEMS ARE PRESENT IN BOTH.
I have some experience in Access 97, but unsure of how to tackle this. Could somebody please guide me ???
Hi, I just started doing something in access and need your help. I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"
Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.
Is there a beter way to do this?
Thanks! P.S. Sorry for my poor english. It's not my native language.
I have run into some problems by comparing two lists. My project is to compare two lists and on the below listed criteria's and with the wanted results.
Criteria's: 1. Part 2. Colour 3. pcs.
Wanted results: 1. Equal match (same part, Colour and pcs (pcs difference above and equal 0) 2. Equal match (same part, colour but missing pcs. (pcs difference below 0) 3. Missing parts (difference in org. list - 1 and 2 query)
But already in the first part I run into problems. I do a query as below. (picture attached)
SQL code: SELECT Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal, [Parts_All]![Antal]-[Set 7897-1]![Antal] AS Part_Diff FROM Parts_All RIGHT JOIN [Set 7897-1] ON Parts_All.[Lego part] = [Set 7897-1].[Lego part] GROUP BY Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal HAVING (((Parts_All.[Lego part]) Like [All_parts]![Lego part]) AND ((Parts_All.farve) Like [Set 7897-1]![farve]) AND (([Parts_All]![Antal]-[Set 7897-1]![Antal])>=0));
This query is returning 2 of the same lines with part and colour but with different pcs. Why.?