Apend/Delete Query using multiple fields criteria

[SteveBB]

Hi,

I have an access database and I want to move a number of records from 1 table to another based on 2 fields (Start Date & End date)

I'm fine with moving records based on criteria in a single field but can find nothing when it comes to using 2 fields.

here's what I'm trying to do in a nutshell

Append full record if Start Date <20150101 & End Date >20150630.

Once apended I'll then need to run a delet query using the same criteria.

Please help

 

[Joe4]

How do you add criteria for a single field? You just do the exact same thing under the second field.
In the Query Builder, you would just add each field, and place the corresponding criteria under each one of these fields.
Note if you are using hard-coded dates in your criteria, you need to use Date Qualifers (#).
See the second section here for examples: https://support.office.com/en-ca/ar...-queries-aea83b3b-46eb-43dd-8689-5fc961f21762

 

[SteveBB]

Hi,

Thanks for the below - Looks like I was doing it right but have found an issue with one of my data fields. Due to the size of the data I converted everything to text except one of the date fields which I converted to number.

Guess I'll have to re-import all 1mil+ regords again and start over (not enough resource to convert :P)

How do you add criteria for a single field? You just do the exact same thing under the second field.
In the Query Builder, you would just add each field, and place the corresponding criteria under each one of these fields.
Note if you are using hard-coded dates in your criteria, you need to use Date Qualifers (#).
See the second section here for examples: https://support.office.com/en-ca/ar...-queries-aea83b3b-46eb-43dd-8689-5fc961f21762

 

[SteveBB]

ok - back again.

I've re imported my raw data and due to the nature of the data the "Dates" have been imported as numbers (Date is in reverse order YYYYMMDD).

I've se t up a standard append query as suggested above adding both fields with criteria and in SQL view it looks fine.

INSERT INTO [Still Active]
SELECT [All Customers].*
FROM [All Customers], [Still Active]
WHERE ((([All Customers].Field14)<20150101) AND (([All Customers].Field6)>20150531));

When I run the query though I'm getting 0 results but I'm expecting close to a million????

 

[SteveBB]

Fixed it

Wondered why in SQL view it had [Still Active] in FROM. deleted that and it worked. No idea why it put that in though as I could nto see it in design view and after removing it I dont see anything different in design view.

Oh well. All is now good

ok - back again.

I've re imported my raw data and due to the nature of the data the "Dates" have been imported as numbers (Date is in reverse order YYYYMMDD).

I've se t up a standard append query as suggested above adding both fields with criteria and in SQL view it looks fine.

INSERT INTO [Still Active]
SELECT [All Customers].*
FROM [All Customers], [Still Active]
WHERE ((([All Customers].Field14)<20150101) AND (([All Customers].Field6)>20150531));

When I run the query though I'm getting 0 results but I'm expecting close to a million????