Query for years of service (1 Viewer)

[PeggyC09]

So I'm new to Access and need help to run a query counting up the number of years someone has worked an event. I have a table with 5000 people and the columns tell if they were a volunteer (vol), if they withdrew (wd) etc for each year (2017-1992). Each year has it's own column.

I need to find out who is getting 25, 20, 15, 20 and 5 year awards in 2018 without going back to excel (very tempting).

Can someone walk me through this?

 

[Gasman]

I think you'd be better off in Excel as your data is Excel orientated.

In access you would have a table along the lines of
AutoNumber
YearID
StatusID
VolunteerID

The IDs could just be the actual data as well.

Then you would just easily count Status/StatusID for those that have vol and do the math from there. As you have each year in a column that does not lend itself to Access.

With Excel just use SumIf and probably a Vlookup? or Ceiling?, just something to see what band they fall into for a reward.

 

[PeggyC09]

I'm trying to switch over from Excel so understanding is really the issue. I'm not sure where the years should go in what you are saying...

 

[mike60smart]

Hi

Would this be the result you would want?

 

[PeggyC09]

Hi

Would this be the result you would want?

View attachment 68183

I think that is the result of how many people worked each event. I'm looking for the result of how many events each person worked (Luke worked 2 events, Sam worked 3 and Carol worked 2.)

I REALLY appreciate you helping me... I'm such a novice! And no one to teach me outside of a million youtube videos. I'm taking over a database that has been exclusively in excel but we keep having a lot of mistakes with repeated spreadsheets so I'm trying to get it into access to eliminate the errors.

 

[Gasman]

Haven't looked at your DB, but Excel and Access work in different ways, each has their strengths.
Concentrate on the best program for the job.

I have been carrying out various processes using Excel and in the short term they have worked well, after all I was working from a basic Excel file(s) and only tidied/automated it somewhat.

Then the requirements meant going to Access would be so much easier despite the extra work in Access to begin with, especially for a novice.

From what you have said so far, I'd still use Excel.

I'm trying to switch over from Excel so understanding is really the issue. I'm not sure where the years should go in what you are saying...

 

[mike60smart]

Hi

Is this more like what you want?

 

[PeggyC09]

I think it's similar to you in that the basics are definitely easier in Excel but because I am managing over 5000 volunteers each year, different teams they are assigned to, all their contact info, t-shirts, etc, there are many different issues that arise trying to separate them into different spreadsheets at crunch time. One major issues is the T-shirt ordering per volunteer with different names/sizes/colors/teams. Years of Service is just a side thing I'm trying to solve before the summer event. When we take in the applications I really need to be able to use queries to distribute so that when a volunteer is reassigned a team their info goes with them and it's distributed across on the t-shirt queries and such. I may have to just export to excel the years of service to get what I'm looking for but really need this formatted in the future so I'm not switching too much and duplicating efforts.

Haven't looked at your DB, but Excel and Access work in different ways, each has their strengths.
Concentrate on the best program for the job.

I have been carrying out various processes using Excel and in the short term they have worked well, after all I was working from a basic Excel file(s) and only tidied/automated it somewhat.

Then the requirements meant going to Access would be so much easier despite the extra work in Access to begin with, especially for a novice.

From what you have said so far, I'd still use Excel.

 

[plog]

If you really want to move to Access (and from what you described you should) then you need to read up on normalization: https://en.wikipedia.org/wiki/Database_normalization

that's the process of correctly structuring your tables to accomodate your data. If you look at Gasman's first reply he tells you how one of your tables should be structured, and not like you had done in your first post.

Tables are to grow vertically with more rows, not horizontally with more columns. When a person has 20 years of service, the database should have 20 records in a table, not 1 record with a column for each year. Again, read up on normalization and structure your data properly if you plan on using Access in the future.

 

[PeggyC09]

Hi

Is this more like what you want?

View attachment 68184

Yes!!! This is it....

 

[Gasman]

Again, I have not looked at what Mike has produced, BUT
speaking from experience, whilst what you need now has been produced by Mike, what that will work in the future for all your changes probably means a lot of compromises.

Spend some time (if you know what is (exactly) required, and try and plan for the future.

Basic example would be for New Year. Add 2018 column in Excel. For Access you would add records for 2018 for the relevant data.

HTH

 

[Uncle Gizmo]

I'm taking over a database that has been exclusively in excel but we keep having a lot of mistakes with repeated spreadsheets so I'm trying to get it into access to eliminate the errors.

If you're serious about transferring your Excel spreadsheet into MS Access then you have got to make minor changes both to the database and the way you think about it, otherwise, as already stated by Gasman, you will be better off keeping it in Excel...

I've done a YouTube video "Excel in Access - Transpose YEARS - Nifty Access" specifically showing you how to modify the sample database you provided. If you want to modify you dB in this way, then send me an email and I will send you instructions on how you can obtain the Transpose_5.mdb Tool...

Excel in Access - Transpose YEARS - Nifty Access​

In the meantime have a look at my website here >>> Excel in Access <<< Where I explain in detail why you should carry out this conversion as early as you can in your database construction.

 

[PeggyC09]

This helped me a lot!!! I really appreciate the video especially! I realized I need to start over on setting up my database. I bought yet another book to explain the concepts behind Access. I love having this forum! Thank you again!!!

 

[Uncle Gizmo]

This helped me a lot!!! I really appreciate the video especially! I realized I need to start over on setting up my database. I bought yet another book to explain the concepts behind Access. I love having this forum! Thank you again!!!

Hi Peggy, good to hear! I'm trying to get to 1000 YouTube subscribers on my YouTube channel. If I do, I get a level up! So it would be really great if you could subscribe, it would be very helpful.