MS Access Newbie - Help Needed!

[nortellini]

Hi folks

Never used Access before - I am trying to achieve the following; to identify a total number of records against a number of locations - which are tagged as either Category A or Category B, and identify if a Category A was created within 1 week of a Category B being raised.

Each location will have multiple records. So, there are 10 x Oxford records for example but 6 are Category A, 4 are Category B.

All the data is in one spreadsheet. I thought it would be a series of IF AND queries, but I'm hopeless with this, and then thought it may be easier in Access but have never used this before. Can someone just tell point me in the right direction of which package would be better to use and if so, is there a help function or question that may help?

Any assistance would be hugely appreciated. Thank you in advance.

 

[Joe4]

It would be helpful if you could post a sample of the data from your spreadsheet (so we can see the structure of your data), and walk us through an example based on that data (so we can see exactly what you are after).

 

[nortellini]

Event	Ticket Number	Type	Reason	Performance	Sold Out	Date
Romeo & Juliet	1	Planned	Plan to Visit	Matinee	Yes	01/09/2020
Romeo & Juliet	5	On Spec	Unplanned	Evening	No	03/09/2020
Romeo & Juliet	19	Planned	Plan to Visit	Matinee	Yes	12/09/2020
Romeo & Juliet	29	On Spec	Unplanned	Evening	No	20/09/2020
Romeo & Juliet	30	Gift Card	Other	Matinee	No	20/09/2020
Merchant of Venice	200	Planned	Plan to Visit	Matinee	Yes	01/10/2020
Merchant of Venice	209	On Spec	Unplanned	Matinee	No	03/10/2020
Merchant of Venice	219	Planned	Plan to Visit	Matinee	Yes	12/10/2020
Merchant of Venice	243	On Spec	Unplanned	Evening	No	20/10/2020
Merchant of Venice	245	Gift Card	Other	Matinee	No	20/10/2020

Ok, so I've tried to make it a bit more simplistic so hopefully I don't confuse anyone! I just want to understand the base principles and hopefully get the message across.

This is a snapshot of the data but the basics are:

I would like to know if I can tell if there is an Unplanned ticket (Column D) sold within 5 days (Column G) of a Plan to Visit (Column D) Ticket being sold for the same Event (Column A). I also want to know if the Unplanned sale is for a Matinee performance (Column E).

As you can see - there are multiple Events (Column A) so, I'd like to repeat this for all events.

I hope I am making sense! Can anyone advise?

 

[alansidman]

What date do you wish to compare to? Some of your dates are in the past which seems to make this exercise meaningless. Having said that, I would probably use the following SQL statement to build a query

SELECT Sheet100.Event, Sheet100.[Ticket Number], Sheet100.Type, Sheet100.Reason, Sheet100.Performance, Sheet100.[Sold Out], Sheet100.Date
FROM Sheet100
WHERE (((Sheet100.Reason)="Unplanned") AND ((Sheet100.Performance)="Matinee") AND ((Sheet100.Date) Between Now() And Now()+5));

My Table is called Sheet100. Change this to your sheet name.

 

[nortellini]

What date do you wish to compare to? Some of your dates are in the past which seems to make this exercise meaningless. Having said that, I would probably use the following SQL statement to build a query

SELECT Sheet100.Event, Sheet100.[Ticket Number], Sheet100.Type, Sheet100.Reason, Sheet100.Performance, Sheet100.[Sold Out], Sheet100.Date
FROM Sheet100
WHERE (((Sheet100.Reason)="Unplanned") AND ((Sheet100.Performance)="Matinee") AND ((Sheet100.Date) Between Now() And Now()+5));

My Table is called Sheet100. Change this to your sheet name.

Hi Alan

Thank you for taking the time to come back - it's genuinely appreciated. I'll have a go with what you've proposed. Thank you.