Access or Excell for my needs

[Welshgasman]

My 10p (I am British ) is to try and apply db principles to an Excel workbook. You will not get everything an Access db could give you, but should come close. Reporting might be an issue, but I am thinking of the change of logic from excel to Access, which is a bone of contention for many. They tend to develop bad structure. A bit like learning to drive instruction from a horse rider.

[madpiet]

Gasman is definitely on to an important topic - fun with proper table design. FWIW, here are my thoughts:

I'm just going to address the visits part (columns A to AJ), because that's going to be fun to fix.


The obvious:
There is a section for each year, and then for each of those, there are multiple columns: (Start[Date], End[Date], Nights (datediff between Start and End), Site, Marshals. Because "Year" can be derived from either StartDate or EndDate, you don't need to store Year either. So now we're down to these columns:


Start[Date],
End[Date],
Site,
Marshals


The rightmost table is just a summary of counts.
So you could have a form (based on the table above) with {StartDate, EndDate (both use a datepicker), site (combobox), Marshals (combobox) - and your form is pretty much done. Super easy.
Questions it has to answer:
How many different venues (Distinct count of venue),
Events at each venue, Count of events, grouped by venue
Total events (count of events)
How many events each manager ran? Count of events, group by manager.
How many different managers used DISTINCTCOUNT('Event'[ManagerID])

Once you restructure the tables, the forms almost fall out. A form for Events, and then you specify a Venue, StartDate, EndDate, Manager (could be a child table if there can be more than one manager per event). But this brings up another topic: What are you business rules for things like this? Is each event managed by at most one manager?

[madpiet]

10p? Wow, talk about inflation!

[Welshgasman]

10p? Wow, talk about inflation!

Yes, 10p is just a little over 10c these days
When I was in school it was $4 to the £

[madpiet]

Okay, I got all this to unpivot... except for one chunk in 2022 (well, it unpivots, but the End Dates are mostly missing). This is a really good example of why I'm not a fan of doing data entry in Excel... it lets you do whatever you want, and that's not always a good thing (like leaving columns empty, entering any value you want...)

One thing in your data that should just be calculated on the fly in a query, the "Nights" column... It's just a number of days between the StartDate and the EndDate. Just use DATEDIFF() for that.

SELECT [Rally Count All].Start, [Rally Count All].End, [Rally Count All].Nights, [Rally Count All].Site, [Rally Count All].Marshals, DateDiff("d",[Start],[End]) AS NightsCalc
FROM [Rally Count All];

Not 100% sure I got all the data right, but maybe it's because I'm not carefully eyeballing every table. But a bunch of the 2022 data is messed up (the dates don't come through... I'll retry it and check back later.)

Even so, doing this in PowerQuery is infinitely easier in PowerQuery than in VBA. Absolutely no way I'd even try this in Access. maybe because I'm stubborn. (I suppose you could create all your columns in Access as text, and then validate, but in PowerQuery all that stuff is pretty much built in... oh, and PowerQuery writes the query for you... ) =)

[madpiet]

Okay, found some errors. I was looking at your data, and you can do the date math in Excel, or Access, or PowerQuery or ...

I did one like this to test why things were going sideways:
= Table.AddColumn(Source, "Nights2", each [End]-[Start])

At any rate, what I discovered was that you have data entry errors in your data that are causing problems. (One nice thing about Access is strict typing - so if you don't enter a valid date, it barks at you.)

Was fixing this very hard? Well, it was more tedious than it should be, because I'm not great at PowerQuery. But I basically selected each set of columns {Start, End, [Nights], Site, Marshalls}, it was super easy. You don't need [Nights] at all as I pointed out above. You can just tell PowerQuery to do it for you. And when you're done, you just link the file that does the cleaning to your database and import. yeah, it's kinda ugly, but it's flexible. Maybe one day (over the rainbow), MSFT will incorporate Powerquery into Access, but I'm not holding my breath.

[hollies]

Apologies for the delay but I have had a long Bank Holiday weekend away.

Once again, many thanks for everyone's comments and suggestions.

With great help from mike60smart, I have gone down the access route.

I am now working on a database to get it to look how I think I need it.

Many thanks

Rob

[mike60smart]

Just to let everyone know that Rob Pm'd me and I managed to talk him through a solution.

He settled with the following Relationship Diagram:-

[Welshgasman]

So we can expect you asking questions soon for this db on the other two forums?

[mike60smart]

Hi Welshgasman

No he was quite happy with what he currently has.

[Welshgasman]

I wish the O/P the best of luck.
The task does not sound that hard to me, but that is just me.
I have found as one develops a db without any prior forethought, you need to keep amending the structure slightly. Best to try and get that as accurate as possible to start.

[madpiet]

The way I was taught to test a database that made the most sense was to add as few records as possible to each table to test the queries. (So at most five records per table). then write the queries, eyeball the answers to make sure they're returning what you expect, delete the fake records and then add the real data.

[hollies]

So we can expect you asking questions soon for this db on the other two forums?

Not too sure what you mean by the other two forums but I certainly expect I will have some questions along the way. As I said in my original post, I am a novice at this and don't have the knowledge or experience of many on this forum.

mike60smart has given me some great help and I am working on it to make additions and also to learn a little more about Access for another similar but very much larger project.

[Welshgasman]

Not too sure what you mean by the other two forums but I certainly expect I will have some questions along the way. As I said in my original post, I am a novice at this and don't have the knowledge or experience of many on this forum.

mike60smart has given me some great help and I am working on it to make additions and also to learn a little more about Access for another similar but very much larger project.

That was in reply to mike60smart, not you, as that is what he does.
He even replied to that in post #25.

[hollies]

That was in reply to mike60smart, not you, as that is what he does.
He even replied to that in post #25.

Ah. That's why wasn't sure what you meant.