Access Queries via Excel VBA

[mikeymay]

Posted in Excel board but no response so assuming this is a better place to post so apologies if rules are broken....

I need to perform 2 queries as follows

Query 1
A table contains the following fields
Buyer
Production

I need to return all records where a Production value contains a text string. For example there are 3 records that contain Friday Night Live, Fri Night Live and Eastenders as the Production value. Where the search string is 'Fri' I need to return all fields for the records that contain 'Fri' in the Production value.


Query 2
A table that contains the following fields
Buyer
ContactType
ContactDate
ContactDetails

I need to return records for only the last date for each buyer where the date is less than 30 days from the current date and return all table fields.

Currently I am using
SELECT Buyer, Max(ContactDate), Buyer, ContactChannel, ContactDetails FROM BuyerContact WHERE ContactDate <= #26/02/2018# GROUP BY Buyer
But this isn't working

TIA

 

[ranman256]

you cannot run MAX with lots of fields. You want to run MAX and INDEXFLD only.
Q1 , get the most recent record:
select BuyerID, Max(ContactDate) from BuyerContact WHERE ContactDate <= #26/02/2018#

Then you use that to pull all the records,
Q2=Select * from BuyerContact, Q1 where Q1.BuyerID = BuyerContact.BuyerID

 

[mikeymay]

I'm a little confused....

Query 1 is a query on it's own to return data and Query 2 is a query on it's own to return data. They are not connected in anyway.

 

[ranman256]

they are. joined on ID:
Q1.BuyerID = BuyerContact.BuyerID

 

[mikeymay]

Sorry, no they aren't.

Two completely record ID's but hold the same field data.

Basically Query 1 works on a table of where a Buyer has been flagged as working on a specific production and Query 2 works on a table that records when a Buyer has been contacted.

 

[BiocideJ]

You say "this isn't working", but that is rather vague. what exactly isn't working? (Incorrect results, error messages, etc.)

I would say based on your listed criteria, you need to make sure you are also filtering back out any max dates BEFORE 30 days by adding a HAVING clause at the end.

SELECT Buyer, Max(ContactDate), Buyer, ContactChannel, ContactDetails FROM BuyerContact GROUP BY Buyer HAVING Max(ContactDate) <= #26/02/2018#

I think more information may be needed though for more precise responses.

EDITTED: CORRECTED QUERY

 

[BiocideJ]

I believe for Q1 you just need

SELECT * FROM Q1 where Production Like "Fri*"

Honestly though, I'm only 50% sure I'm understanding what you are asking so if I am also off-base, please provide additional details.

 

[mikeymay]

Using what you have suggested the query is (when watching the query string variable - SELECT Buyer, Max(ContactDate), Buyer, ContactChannel, ContactDetails FROM BuyerContact WHERE ContactDate <= #26/02/2018# GROUP BY Buyer HAVING Max(ContactDate) >= #28/03/2018#-30

And this returns an error of - Run Time error -2147217887 (80040e21) Your query does not include the specified expression 'ContactChannel' as part of the aggregate function'

I do have a query working that basically returns all the data and I'm thinking the best way to do this is to return all teh data and then just use code to remove duplicate Buyers rows leaving the latsest date row. I appreciate Access can probably do this but it's not an area I have a lot of experience in and I need to get this out as soon as possible.

I am however still havign an issue with the query to return all records when a text string is entered to return all records where the Production field contains the enetered text string.

 

[mikeymay]

I think this will do it for this question but it could be that the entered text string is part of rather than at the start of a production value. Would I need an opening '*' as well as the closing one?

 

[BiocideJ]

I think this will do it for this question but it could be that the entered text string is part of rather than at the start of a production value. Would I need an opening '*' as well as the closing one?

exactly.


For Q2, ALSO NOTE MY REVISED VERSION, you will either need to GROUP BY all of the fields you want to show
SELECT Buyer, Max(ContactDate), ContactChannel, ContactDetails FROM BuyerContact GROUP BY Buyer, ContactChannel, ContactDetails HAVING Max(ContactDate) < Date() -30
or if you truly want it unique to each Buyer (which I suspect you do), you will need to NOT show the other fields
SELECT Buyer, Max(ContactDate) FROM BuyerContact GROUP BY Buyer HAVING Max(ContactDate) < DATE() - 30

updated the queries to be dynamic by using the date function instead of manually coding today's date