Displaying a grid, with not-records

[Dirk Space]

think you need to provide details on all 28 fields with examples of how they are populated. At the moment it looks like your data is not normalised so you would need to create a union query to normalise it. Or if you want to use code it will be extremely complicated and not something I would want to even consider tackling.

I feel like I did, but maybe I don't understand what you are asking.

These are the fields in the table (actually, there are more than 28, but the 28 are for the Start and End Times) and each is entered for that schedule:
ID (auto random), Name, Sun1_Start, Sun1_End, Sun2_Start, Sun2_End, Mon1_Start, Mon1_End...Sat1_Start, Sat1_End, Sat2_Start, Sat2_End

Each day there is a possibility of 2 shifts.

Two examples (you'll have to forgive the odd formatting):
Sun1_Start | Sun1_End | Sun2_Start | Sun2_End
08:00 | 12:00 | 14:00 | 18:00
11:30 | 20:00 |<blank>|<blank>

 

[theDBguy]

I've tried that, but in their example there is one field for Date Booked, per guest. And that field is the only relevant field, when looking to see if it's booked, and to then sum that count.

I've got 28 fields of start and end times, per employee, that can't take the place of one "relevant" field per employee (unlike a guest being booked or not booked on a date).

Hi. As I said in my post, the link was merely to demonstrate how to generate non-existing values. I said it doesn't apply to your particular situation, as is. However, you might be able to adapt the approach given to get to what you need for your database. If I had a copy of your database, I probably would have given it back to you already with a possible working solution. I really believe you'll end using a crosstab query somehow, somewhere.

 

[CJ_London]

These are the fields in the table (actually, there are more than 28, but the 28 are for the Start and End Times) and each is entered for that schedule:

As I said - your data is not normalised. You have an Excel structure so you will need to use an excel solution. Databases cannot use excel structures except for the simplest of things. To use a phrase, your data is 'short and wide'. Databases store data 'tall and narrow'

If you cannot normalise the data itself, you will need to use a union query to provide a normalised view. When you have done that, the solution I provided will work

based on what you have provided, your data needs to look something like this

ID (auto random).Name..Day..shift..Start..End
1.......................fred.....Sun..1......8:30...12:30
2.......................fred.....Sun..2......14:00..18:00
3.......................fred.....Mon..1......11:00..15:00
4.......................fred.....Tue...1......11:00..15:00
...
18.....................John.....Sun...1.....9:00....13:00
etc

 

[Dirk Space]

As I said - your data is not normalised. You have an Excel structure so you will need to use an excel solution. Databases cannot use excel structures except for the simplest of things. To use a phrase, your data is 'short and wide'. Databases store data 'tall and narrow'

If you cannot normalise the data itself, you will need to use a union query to provide a normalised view. When you have done that, the solution I provided will work

based on what you have provided, your data needs to look something like this

ID (auto random).Name..Day..shift..Start..End
1.......................fred.....Sun..1......8:30...12:30
2.......................fred.....Sun..2......14:00..18:00
3.......................fred.....Mon..1......11:00..15:00
4.......................fred.....Tue...1......11:00..15:00
...
18.....................John.....Sun...1.....9:00....13:00
etc

I don't think I will be able to. There's over 400 employees... times 7 days... plus the ones with more than 2 shifts in a day... that's a lot of rows on a table to enter.

Plus, these are written and presented as schedules and bid on.

Schedule 1 - specified times with specified days off.
Schedule 2 - specified times with specified days off.
Schedule 3 - specified times with specified days off.
Schedule 4 - specified times with specified days off.
etc.

From what limited knowledge I have of Union Queries, they need to have matching fields. Since I don't, what would you recommend I look at to see how I could accomplish this?

 

[CJ_London]

number of rows in a table is irrelevant - access can hold millions of rows

if you have a holiday table as well, even more reason for normalisation

Your explanation for what you actually have keeps changing so I hesitate to such what this might be perhaps:

tblEmployees

tblHolidays and/or schedules and/or shifts - this 'Plus, these are written and presented as schedules and bid on.' is not clear

your limited knowledge of union queries is wrong

what would you recommend I look at to see how I could accomplish this?

google 'normalisation', try the wikipedia site or this link may help https://www.thestudentroom.co.uk/showthread.php?t=1879146

 

[arnelgp]

can you post sample db and an excel of expected output.

 

[Dirk Space]

number of rows in a table is irrelevant - access can hold millions of rows

That's why I said "that's a lot of rows on a table to enter" and not "I don't think it can hold that many rows."

if you have a holiday table as well, even more reason for normalisation

Your explanation for what you actually have keeps changing so I hesitate to such what this might be perhaps:

tblEmployees

tblHolidays and/or schedules and/or shifts - this

There is no need for a holiday table.

I feel like my explanation has not changed, but been elaborated upon.

I'll try again.

'Plus, these are written and presented as schedules and bid on.' is not clear

Let me try and give an example:
A call center can estimate how many calls they receive per week; 100,000
Each call is estimated to take 15 minutes.
So, they need to cover 1,500,000 minutes or 25,000 hours
There are 40 hours in a work week, so they need 625 "shifts" to cover those calls for that week.

625 schedules are created, based on the estimated busy and slow times.

Employees pick ("bid") on the schedule they want, based on seniority and their other various metrics, one at a time.

your limited knowledge of union queries is wrong

Well, that is why I said limited knowledge.

google 'normalisation', try the wikipedia site or this link may help https://www.thestudentroom.co.uk/showthread.php?t=1879146

It seems more and more like Access won't be able to do this.

I can only start with x schedules needed, with these specific parameters (start and end times for each day of the week).

It appears it wants a day of the week table, with all the 15 minute increments and each "schedule" number then associated with it. Then repeat, for each day of the week. So, I'd have to mentally unpack every schedule. And that sounds like thousands of entries.

That's a lot of manual entry for something that, at first blush, appears to only have 14 (or 28) fields to deal with.

I assumed Access would be easier because things are associated with other things, instead of crazy look ups and brute force in Excel.

 

[CJ_London]

I assumed Access would be easier because things are associated with other things

it is, but 'things' are tables, not fields.

At the moment you have one 'thing', so there is nothing to associate it with.

You wouldn't fly to America in a bus (Excel), you'd go in a plane (Access) - and the way a plane works is completely different to a bus.

You need to learn and understand normalisation before you will make any progress with Access. Excel is about as far away from normalisation as you can get.

I have paying clients to attend to, we are 27 posts in with zero progress so I'll take this opportunity to bow out. Perhaps someone else will take up the cause.

Good luck with your project

 

[Dirk Space]

it is, but 'things' are tables, not fields.

At the moment you have one 'thing', so there is nothing to associate it with.

Since it it can tell me if anyone with the last name Davidson has sold a Coke on a Wednesday in the past 30 years, with what discount code, and how many... just assumed this would be easier.

I just have one table. Figured less info would be easier for it to accomplish things.

You wouldn't fly to America in a bus (Excel), you'd go in a plane (Access) - and the way a plane works is completely different to a bus.

Since a bus is unable to travel the ocean to America, yet Excel can produce the desired results, I feel like that is an ill-fitting analogy.

But I get your point.

You need to learn and understand normalisation before you will make any progress with Access. Excel is about as far away from normalisation as you can get.

The only way to normalize the simplicity of only 1) a schedule and 2) its associated start and end times is tons of tables and thousands of rows... which seems not "normal".

Simple -> Complex -> Simple just seems unnecessarily laborious.

Another database I've made for transactions with tons of tables, queries, and forms works well. I was just looking into Access working with something less complicated.

I have paying clients to attend to, we are 27 posts in with zero progress so I'll take this opportunity to bow out. Perhaps someone else will take up the cause.

Good luck with your project

"Access won't do what you want as easily as Excel will" seems like progress.

Thanks for your assistance.

 

[Dirk Space]

can you post sample db and an excel of expected output.

Not until I get back, on Wednesday.

But here's what the Access part is.

tblSchedules
ID (auto random), Schedule <number assigned/entered>, Name, Sun1_Start, Sun1_End, Sun2_Start, Sun2_End, Mon1_Start, Mon1_End, Mon2_Start, Mon2_End... to Sat.

This is repeated 400 times, 1 row for each Schedule (Schedule 1, Schedule 2, etc.) It's a weekly schedule, with 2 possible shifts, per Schedule.

Here's what Excel would look like:

...............S M T W T F S
12:00 AM 0 1 2 2 2 0 0
12:15 AM 1 2 0 3 0 2 4
12:30 AM 1 0 1 3 5 1 4
...
11:30 PM 0 1 1 2 5 1 6
11:45 PM 3 3 3 3 1 3 0

So, there are 5 employees here on Thursday at 12:30 AM

It's a weekly snapshot of how many are present, in 15 minute increments.