MS Access Newbie - Help Needed!

nortellini

New Member
Joined
May 13, 2004
Messages
28
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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 13, 2004
Messages
28
EventTicket NumberTypeReasonPerformanceSold OutDate
Romeo & Juliet1PlannedPlan to VisitMatineeYes01/09/2020
Romeo & Juliet5On SpecUnplannedEveningNo03/09/2020
Romeo & Juliet19PlannedPlan to VisitMatineeYes12/09/2020
Romeo & Juliet29On SpecUnplannedEveningNo20/09/2020
Romeo & Juliet30Gift CardOtherMatineeNo20/09/2020
Merchant of Venice200PlannedPlan to VisitMatineeYes01/10/2020
Merchant of Venice209On SpecUnplannedMatineeNo03/10/2020
Merchant of Venice219PlannedPlan to VisitMatineeYes12/10/2020
Merchant of Venice243On SpecUnplannedEveningNo20/10/2020
Merchant of Venice245Gift CardOtherMatineeNo20/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

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows
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

SQL:
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

New Member
Joined
May 13, 2004
Messages
28
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

SQL:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,935
Members
416,143
Latest member
JoyceMB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top