Chris:
Firstly you'll need a means of equating the values to one decimal place in
table A with those to two decimal places in Table B. So paste the
following
function into a standard module in your database:
Function RoundDownTo(dblNum As Double, _
intDecs As Integer) As Double
Dim strNum As String
If dblNum - Int(dblNum) = 0 Then
strNum = Format(CStr(dblNum), "#.0")
Else
strNum = CStr(dblNum)
End If
RoundDownTo = Left(strNum, InStr(strNum, ".") + intDecs)
End Function
To do this either select an existing module from the modules tab in the
database window, or select 'New' to create a new module. The module will
have a couple of lines already in place in its declarations area. Just
paste
the above code below these to create the function. Then save the module.
If
it's a new one be sure to save it under a different name from the
function.
I use a 'bas' prefix, so you might call it something like basNumbersStuff
and
use it for any number handling functions you create.
Once you've done that you can call the function in a query. To avoid the
duplication you can use a subquery to restrict the rows appended to only
those without a match in Table B like so:
INSERT INTO [Table B] (Area, [Date], Rate)
SELECT Area, [Date], Rate
FROM [Table A]
WHERE NOT EXISTS
(SELECT *
FROM [Table B]
WHERE [Table B].Area = [Table A].Area
AND [Table B].[Date] = [Table A].[Date]
AND RoundDownTo([Table B].Rate,1) = [Table A].Rate);
Because the above uses a subquery you can't create the query in design
view,
do create a new query in the usual way, but don't add any tables. Switch
to
SQL view and paste in the above. If any of your table or column names
differ
from what you gave in your post then amend the SQL accordingly. Note that
I've enclosed Date in brackets. This is because Date is the name of a
built
in function, so otherwise could be misinterpreted. I'd advise you change
the
column name to something more specific in fact, TransactionDate for
instance.
Running the query should append only those rows where there is no matching
row in Table B already with the rate rounded down to one decimal place.
If you have only one row per area per date you can do it more simply.
Just
create a unique index on the Area and Date fields in Table B. When you
append rows from Table A using a simple append query, any with duplicate
Area
and Date values will be rejected.
Ken Sheridan
Stafford, England
C. Bailey said:
I have two tables:
Table A:
Area Date Rate
1 4/5/2007 60.1
1 4/6/2007 50.3
1 4/7/2007 47.5
1 4/8/2007 46.0
Table B:
Area Date Rate
1 4/3/2007 50.0
1 4/4/2007 53.3
1 4/5/2007 60.13
1 4/6/2007 50.36
When I append Table A to Table B, I get a table with 8 rows. What I want
is
a table with only 6 rows because the data for 4/5/2007 and 4/6/2007 are
duplicated. How do I avoid this? Please note that the rates are not
quite
identical on these dates, because one of the tables has more decimal
places
than the other.
I am a novice. Please speak slowly
Chris
