We help IT Professionals succeed at work.

Append Query - add criteria

pdvsa
pdvsa asked
on
Experts,

I am importing an excel file XLImportToAccess and comparing with tblImportToAccess (production table).
It works fine.
I have to add additional criteria though.
If XLImportToAccess.DATE doesnt equal tblImportToAccess.DATE and all other fields match as shown in the append query below, then I need to update tblImportToAccess.DATE to the date found in field XLImportToAccess.DATE.  

I hope that makes sense.  Please let me know if I need to add something else.  

INSERT INTO tblImportToAccess ([Trade #], [Buy CP], [Quantity BBLS], [Data], Batch, [Origin / Deal], [Date], Grade, State, [Trade #1], [Sale CP], [Operation #], WorkingDate, SentAwayDate, Notes)
SELECT XL.[Trade #], XL.[Buy CP], XL.[Quantity BBLS], XL.[Data], XL.Batch, XL.[Origin / Deal], XL.[Date], XL.Grade, XL.State, XL.[Trade #1], XL.[Sale CP], XL.[Operation #], XL.WorkingDate, XL.SentAwayDate, XL.Notes
FROM XLImportToAccess as XL LEFT JOIN tblImportToAccess as I2A
ON XL.[Trade #] = I2A.[Trade #]
AND XL.[Buy CP] = I2A.[Buy CP]
AND XL.[Quantity BBLS] = I2A.[Quantity BBLS]
AND XL.[Batch] = I2A.[Batch]

AND XL.[Date] = I2A.[Date] 
WHERE I2A.ID IS NULL

Open in new window

Comment
Watch Question

Top Expert 2014

Commented:
I think you may need to invoke three queries.  The first one as you've shown.

The second one where the dates are not equal, the input date field would be Null.
The third one is an update statement that would set any Null date fields to the desired date value.
pdvsaProject finance

Author

Commented:

Thank you aikimark.  Much appreciated.  Its a little out of my league though :(   I am very rusty on access.  

Top Expert 2014

Commented:
please post some before and after data (representational) example
pdvsaProject finance

Author

Commented:

Hi Aikimark,


Thank you for your kindness.  I have attached a pared down db with only 2 tables and an append query.  

To recap what I need:   If XLImportToAccess.DATE doesnt equal tblImportToAccess.DATE and all other fields match as shown in the append query above, then I need to update tblImportToAccess.DATE to the date found in field XLImportToAccess.DATE.  Let me know if you require anything additional. Thank you very much.


Database: 

EE_importing.accdb

John TsioumprisSoftware & Systems Engineer

Commented:
This seems like an Update case not Append since you are changing a record not inserting new
Check a look at my attachment
EE_importing.accdb
Top Expert 2014

Commented:
In your example database, do you want XL rows with ID in (107, 109) to be inserted into tblImportToAccess?

Then, do you want some columnar data for tblImportToAccess.ID = 54 to be updated from XL row with ID = 108?
pdvsaProject finance

Author

Commented:

Hi,  just letting you know I will be able to respond to your question after a few hours or possibly tomorrow.  Backed up with job.

pdvsaProject finance

Author

Commented:

John,

That's a nice query.  However if I understand, it updates all the records for criteria IIf([XL].[Date]<>[I2A].[DATE],1,0).  I do not need to update all records in that way.  Only for certain ones.  But maybe you are saying that I will need to run that update query separately AFTER the append.  Let me know if that is the case.  


aikimark:

< do you want XL rows with ID in (107, 109) to be inserted into tblImportToAccess?

ans:  Yes

< do you want some columnar data for tblImportToAccess.ID = 54 to be updated from XL row with ID = 108?

Yes. But only the [Date]

Top Expert 2014

Commented:
That is why I said you would need multiple queries.  Now that I have a better idea of what you need, I can reduce my estimate to two queries - one append and one update.  I may not get to post these queries until tomorrow.
pdvsaProject finance

Author

Commented:

ahh ok.  I follow you.  Grateful for your assistance.  

Top Expert 2014

Commented:
These two queries seem to work as you wanted.

INSERT INTO tblImportToAccess 
( [Trade #], [Buy CP], [Quantity BBLS], Data, Batch, [Origin / Deal], [Date], Grade, State, [Trade #1], [Sale CP], [Operation #], WorkingDate, SentAwayDate, Notes )
SELECT XL.[Trade #], XL.[Buy CP], XL.[Quantity BBLS], XL.Data, XL.Batch, XL.[Origin / Deal], XL.Date, XL.Grade, XL.State, XL.[Trade #1], XL.[Sale CP], XL.[Operation #], XL.WorkingDate, XL.SentAwayDate, XL.Notes
FROM XLImportToAccess AS XL LEFT JOIN tblImportToAccess AS I2A 
ON (XL.[Batch] = I2A.[Batch]) 
AND (XL.[Quantity BBLS] = I2A.[Quantity BBLS]) 
AND (XL.[Buy CP] = I2A.[Buy CP]) 
AND (XL.[Trade #] = I2A.[Trade #]) 
WHERE (((I2A.ID) Is Null));

Open in new window


UPDATE  tblImportToAccess AS I2A LEFT JOIN XLImportToAccess AS XL  
ON (XL.[Batch] = I2A.[Batch]) 
AND (XL.[Quantity BBLS] = I2A.[Quantity BBLS]) 
AND (XL.[Buy CP] = I2A.[Buy CP]) 
AND (XL.[Trade #] = I2A.[Trade #])
Set I2A.date = XL.Date
WHERE (((I2A.date) <> XL.Date));

Open in new window

pdvsaProject finance

Author

Commented:

Aikimark,

Very nice.  I tested the append and its appending as needed.  Thank you. 


Update query: 

I will try to explain.  I think its updating I2A (tblImportToAccess) with the value found within the same table   I2A (tblImportToAccess).  I need it to update with the value found in XL (XLImportToAccess).  When you get a sec maybe you can check it.  I fiddled around with it but it always seemed to grab the value found in   I2A (tblImportToAccess) and not   XL (XLImportToAccess).  


thank you...

Mark EdwardsChief Technology Officer

Commented:
One tip about LEFT JOINS.  Putting criteria where you compare a field on one side of the join to a field on the table on the other side of the join:
WHERE (((I2A.date) <> XL.Date))
may cause the join to act as an INNER JOIN.  You can test this by changing your query to a SELECT query and see if you get any records on one side that DO NOT have a matching record on the other side.  If ALL records on one side have matching records on the other - you have an inner join.

Also, when linking fields, be aware that a field that is Null does not equal anything.  ie., a Null does NOT equal a Null (or anything else), so the records won't link.  If you want a Null on one side to equal a Null on the other side, then you need to use an empty string for the Null field value - not a Null.

Also be aware that on those records that DO link, if there are more than one record with the same linking field values, you can end up updating the SET fields with multiple values - one for each record that has matching linking field values.  Whichever record updates the SET fields last - wins!
Mark EdwardsChief Technology Officer

Commented:
When you said:
" I think its updating I2A (tblImportToAccess) with the value found within the same table   I2A (tblImportToAccess)."
that could be the same thing as the value in 12A is NOT changed, so it has the same value it started with, right?

Look at what's happening in those records that you THINK should be changed, but aren't.  See my 1st post for possible reasons why.
Top Expert 2014

Commented:
You're right, Mark.  This should be an inner join.
Mark EdwardsChief Technology Officer

Commented:
Hey Mark H!  I see you have nothing better to do on a Saturday afternoon like I do.  But, we're having fun, right?
Top Expert 2014
Commented:
UPDATE tblImportToAccess inner join XLImportToAccess AS XL

ON         (tblImportToAccess.Batch = XL.Batch) 
AND (tblImportToAccess.[Quantity BBLS] = XL.[Quantity BBLS]) 
AND (tblImportToAccess.[Buy CP] = XL.[Buy CP]) 
AND (tblImportToAccess.[Trade #] = XL.[Trade #]) 

SET tblImportToAccess.[date] = XL.[Date]

WHERE tblImportToAccess.[date]<>[XL].[Date]

Open in new window

Top Expert 2014

Commented:
Some fun before the UNC game comes on.  What nerds call fun. ;-)
Mark EdwardsChief Technology Officer

Commented:
hmmmmm….. "UNC"?  Don't you mean "Chapel Hill Community College Girls School" says the NCSU guy.....
Top Expert 2014

Commented:
I think the latest loss for NCSU was against the Chapel Hill Community College Girls School.
Mark EdwardsChief Technology Officer

Commented:
;-(
pdvsaProject finance

Author

Commented:

Hi Mark and Mark,


I see now.  The update query is displaying the value before the update but when you execute then it updates like it should.  I feel a little dumb now but now I know.  Totally lost on the inner, left joins but that is why I am a member on EE ....hehe.   


Thanks for the advice about Nulls.  I know those are tricky and they scare me. 

<Also, when linking fields, be aware that a field that is Null does not equal anything.  ie., a Null does NOT equal a Null (or anything else), so the records won't link.  If you want a Null on one side to equal a Null on the other side, then you need to use an empty string for the Null field value - not a Null.

==>Do I need to be careful about NULLS on those fields that have the criteria?  I mean if there is a NULL in any of those fields then would a record get appended and shouldnt (essentially a dupe)? 

Answer:  I just checked on that and yes if there is a null then it will append so I now know I  need to put a test in for that.  

<Also be aware that on those records that DO link, if there are more than one record with the same linking field values, you can end up updating the SET fields with multiple values - one for each record that has matching linking field values.  Whichever record updates the SET fields last - wins!

==>Understood.  I will develop a query that lists any records that are like this.  


Thank you both!

Most Valuable Expert 2014

Null can be a pain, but doesn't necessarily have to be scary.

It ABSOLUTELY demands awareness and handling.

Null, nil, zero--in everyday English they all equate to an absence

In database terms, Null means you haven't specified something, so it could be ANYTHING, including nothing.

Comparisons use logical operators, and if you think of Null as ANYTHING, you get why NULL <> NULL.

Anything may or may not equal another anything, who can tell?.


Nz(SomeValueThatCanBeNull, TheValueIWillSubstituteForNull) is your friend.

Any time that a value you are passing or using in code could be null, you use Nz() to ensure that your code has a SOMETHING to work with, and you code for what needs to happen if the value is SOMETHING.


When you design tables, if it can make sense, you build in a default value that will get added with a new record if a field isn't expressly given a value.  Sometimes you really do want to leave null in there because it conveys the information that the end-user really and expressly never created a value.  Sometimes that information isn't important and you can say a string value will default to "" or as number to 0, or a date to some arbitrary date, or a Boolean to FALSE (depending on the field type) if the record is created without a value, or the user removes the value.


You have to be aware of where you have left room for null to occur, and then handle it.


Nick67