Table Search and Total

[DDJKSA]

Morning ladies and gents. This is my first post so hopefully it's in the right area. I have a table with 31 columns where each row is a weekly class schedule (MonPeriod 1 to Friday Period 6). Basically the first column is the week number and then the remaining 30 columns with the subject for each period. Some fields will have the same subject eg. History will be taught on Mon Period 1 and Thursday Period 6, Physics is 3 times a week etc. Each Period is basically 1 hour. How do I calculate how many hours each subject has used after eg. 10 weeks (where I have 10 or more rows of data in my table). In other words, I think I need to treat the table as a Matrix, search for a specific value ("Subject") across all its rows and columns and total how often that subject appears. Hope this makes sense? Thanks in advance for any advice/guidance.

DDJ

 

[Minty]

I'm afraid your table sounds like it is designed like a spreadsheet, so this will be very awkward in Access.
You tables should be long and thin with fields along the lines of

Subject, Period, WeekDay (or possibly simply ClassDate)

You then can simply add up the data by column and subject.

 

[Gasman]

Yes, get your data normalised.
Access requires different thinking than that when using Excel.

Read here before you go any further, else you will always be looking for workarounds.

Solved - Normalisation

Micron on here posted a few links on another site, the first one being for Normalisation, which Excel users to come to Access have never heard of. He also posted a good few other links. Rather than post to his original post (as it is on another site) anymore, I am posting them here and so will...

www.access-programmers.co.uk

 

[CJ_London]

Depends who this schedule is for- as described it is for an individual (a pupil or teacher) or perhaps a room or class . You say a class but presumably you have more than one class?

If for a school, would have thought any given period would potentially have more than one subject/class?

 

[DDJKSA]

Thanks for all the replies guys. I'm not new to ACCESS but my coding is limited really to modifying existing snippets I find online. Maybe I could explain it better using the attached picture. Is there a query or piece of code I could run to calculate the number of times X and Y appear in my entire table? I realise my existing table could perhaps be improved but surely it doesn't matter if I have a narrow/tall table or short/wide table - the query or code should work on either one?

Thanks again for any advice

DDJ

 

[Minty]

I realise my existing table could perhaps be improved but surely it doesn't matter if I have a narrow/tall table or short/wide table - the query or code should work on either one?

Unfortunately it really does matter.
What happens when you add a week or period to your current structure? You would have to rebuild the query you wrote.

Currently your query will be absolutely horrible , something like

SELECT Sum(CountC1) as XTotals
FROM
(

SELECT Iif(C1 = "x",1,0) as CountC1 From YourTable
Union
SELECT Iif(C2 = "x",1,0) as CountC2 From YourTable
Union
SELECT Iif(C3 = "x",1,0) as CountC3 From YourTable
etc. etc.
) as CountX

And that's just for one value, you'll need to repeat it or put something equally horrible into one line to get your results.

With a normalised structure it becomes as simple as

SELECT Subject, Period, Count(CourseDate) as NoOfCourses
FROM YourTable
Group By Subject, Period

 

[DDJKSA]

Unfortunately it really does matter.
What happens when you add a week or period to your current structure? You would have to rebuild the query you wrote.

Currently your query will be absolutely horrible , something like

SELECT Sum(CountC1) as XTotals
FROM
(

SELECT Iif(C1 = "x",1,0) as CountC1 From YourTable
Union
SELECT Iif(C2 = "x",1,0) as CountC2 From YourTable
Union
SELECT Iif(C3 = "x",1,0) as CountC3 From YourTable
etc. etc.
) as CountX

And that's just for one value, you'll need to repeat it or put something equally horrible into one line to get your results.

With a normalised structure it becomes as simple as

SELECT Subject, Period, Count(CourseDate) as NoOfCourses
FROM YourTable
Group By Subject, Period

 

[DDJKSA]

Thanks Minty,

I'm going to have a more in-depth look at what you suggest. Appreciate your help and input so far.

Cheers

DDJ

 

[mike60smart]

Thanks Minty,

I'm going to have a more in-depth look at what you suggest. Appreciate your help and input so far.

Cheers

DDJ

Hi
Can you upload a zipped copy of your current Db?

 

[LarryE]

Morning ladies and gents. This is my first post so hopefully it's in the right area. I have a table with 31 columns where each row is a weekly class schedule (MonPeriod 1 to Friday Period 6). Basically the first column is the week number and then the remaining 30 columns with the subject for each period. Some fields will have the same subject eg. History will be taught on Mon Period 1 and Thursday Period 6, Physics is 3 times a week etc. Each Period is basically 1 hour. How do I calculate how many hours each subject has used after eg. 10 weeks (where I have 10 or more rows of data in my table). In other words, I think I need to treat the table as a Matrix, search for a specific value ("Subject") across all its rows and columns and total how often that subject appears. Hope this makes sense? Thanks in advance for any advice/guidance.

DDJ

First, please take a screenshot of your relationships window so we can see what you actually have designed. You are building a house. If the foundation is faulty, the walls and roof will collapse.

 

[DDJKSA]

Hi Minty

Apologies for the delay but I

Hi
Can you upload a zipped copy of your current Db?

Hi Minty

Thanks for all your help so far. I've uploaded a copy of my db as requested. Basically the tutor opens the form frmClassroom timetable and uses this to generate the timetable for a specific week using combo boxes to pick the week no. and subjects from separate tables. The data from the form is then saved in the main table Classroom Timetable. This all works fine.

If you look at the Subjects table you'll see each subject has a number of allowed hours against it. What I'd like to to is run a query or code on the table ClassRoom Timetable, examine each cell int the table and calculate how many hours are left in each subject on a weekly basis (each subject period is 1 hour).

I did read the links on normalisation which is basically using relational databases but with my limited understanding can't really see how it helps here - it depends on fields in different tables which can be linked.

Hope this helps?

Thanks again for any advice/guidance.

DDJ

 

[DDJKSA]

Actually, during my recent bathroom break (it's where I do my best thinking!), I was wondering an easier way might be to add another column to my Subjects Table "Remaining Hours" (which would initially be set to the same value as "Total Hours") and each time the record in the form frmClassroom Timetable is updated, the subjects on the form are examined and 1 (ie. hour) is deducted from the value in "Remaining Hours" for that subject for each occurrence in the form. Does this sound doable, easy or am I talking nonsense? Obviously the remaining hours would only ever be a "live" value (I wouldn't be able to eg. query how many hours were left on each subject for a specific week but I can live with that).

TIA for any advice/guidance (and if this is doable, help with the coding would be much appreciated too!)

Cheers

DDJ

 

[arnelgp]

see the New ClassTimeTable and the associated Form, FormClassTimeTable.
see Query1 for the total.
see also Query2.

 

[DDJKSA]

Thanks arnelgp....downloading it now and will feed back.

DDJ

 

[DDJKSA]

Hi arnelgp

That looks great, exactly what I need. I'll spend some more time on it today to fully roadtest it. Really appreciate your help (and all the other guys) You think you know ACCESS until you come on a forum like this and realise how little you know!!


DDJ

 

[arnelgp]

another demo from your db.
open form, frmClassTimeTable.

 

[DDJKSA]

Hi arnelgp

I came on again to thank you for you help. Roadtest was very successful so really appreciate your work on this.....then lo and behold you upload an even better and more user friendly version. Will play with it tomorrow but, again, thanks very much....you're a star

DDJ

 

[Minty]

@DDJKSA I hope you appreciate the difference in how the data is stored to make the queries work able?
This is why Normalisation is important.

Kudos to @arnelgp for taking your data and manoeuvring into a sensible format.

 

[DDJKSA]

@DDJKSA I hope you appreciate the difference in how the data is stored to make the queries work able?
This is why Normalisation is important.

Kudos to @arnelgp for taking your data and manoeuvring into a sensible format.

Totally (no pun intended!). My structure was all flat, arnelgp really worked some magic on it and then went above and beyond in the 2nd database he posted so again, thanks a lot guys....really appreciate the effort and time you've put in.

Cheers

DDJ