Random Schedule for Access 2007

[DJ Rhino]

I need tons of help. I need to make a random schedule every week. I have a new DB that I have attached and the current one being used that was built by some one else. The reason for building the new one is I could not figure out what to change to add a new section/Area for scheduleing.

What I have are 3 levels of auditors

Level 1 are the basic auditors which I will need 1 per shift per day
Level 2 Are operationl Auditors I will need 2 of the per week
Level 3 are management auditors and I need one of those per week.

When I get these compiled per week I will need to make a report to e-mail out.



I attached both DB's the old one to show what I need, and the new for a more stripped down and easier to update version.

Thanks for all the help you will provide to me.

[orange]

You may get some insight from this recent post.

[DJ Rhino]

You may get some insight from this recent post.

Thanks for the reply, I no nothing of VBA, I normally use queries and macros. I know a little about sql. also can you look at what I have in the new LPA audit and tell me if it is set up right for what I want to do.

Thanks,

[orange]

I always look for a data model -in Access the closest thing is the relationships window.
Your database does not have relationships identified.
Your tables do NOT all have Primary Keys. Every table in a relational data base should.
Field names should not have embedded spaces. Some of yours do.
Fields should be atomic --1 field 1 fact. Your database has first and last name in same field.

People here are volunteers whose purpose is to assist/advise/recommend/respond to specific questions.
What exactly is your question? What have you done so far?

Here's an overview for designing a database.

[DJ Rhino]

You must have been looking at the wrong Datbase, the one that I wanted looked at to see if I was on the right track is called "New LPA Audit" I made changes to it based on some of you suggestions. The other database was attached to demonstrate what I was looking to do but in a much easier fashion. That database was created by someone eles who has more knoledge than me. Some of what he did is what I want to do, like with queries and such because I do not know how to do VBA, but I know a little of SQL. What I'm looking for is guidance in what steps I would need to do next.

Thanks,

[orange]

Hmm.
From earlier post

can you look at what I have in the new LPA audit and tell me if it is set up right for what I want to do

I'm looking at https://www.accessforums.net/attachm...4&d=1423758943

-no relationships
-Audits does not have a Primary Key, and has spaces in "Date of Audit"
-Holiday has no Primary Key, space in Holiday Date
-Auditors has space in "Entry Name"

Spaces are not a show stopper, but most here would tell you to not adopt a naming convention that allows embedded spaces -use only alphanumeric and "_" underscore.

What am I missing?

[DJ Rhino]

Ok, maybe I was mistaken, sorry about that. So I made some changes based on what you said. I'm not sure what relationships will do to help me, I normally don't use them as I find it slows things down, but you guys know way more than me so I'll try it on this one, so I'll need help on which ones will need to be related to and such. I also attached what it will look like when I'm done(Based on how the old one looks like) Accept it will be Level 3 on top then level 2 and then Level 1 with 5 names per shift. So what I'm looking for is help on setting up the queries to make this happen. By all means I'm not asking anyone to do the work for me because I want to learn how to do it, It's just my knoledge is not extremly advanced yet I'm more of a middle of the road Database maker.

[orange]

Tables look better, PK and no spaces. But why 3 tables for Auditors?? Just curious.

If the level is the only thing that differentiates 1 table from another, typically you would
put all data into 1 table. You can always get out the Employees based on their current Level.

Select People from Table where Level = 2 will get all the Level 2 people.

When do people change Level? How would you reflect that in the database?

I am playing devil's advocate here a little -- my objective is to suggest things that you could be asking before/when you make any changes or respond to comments.

You said you need tons of help.

Database design Principles
Some data design videos <---several related videos follow this one
Lots of good Access and vba things here
Access Youtube Videos xipoo

[DJ Rhino]

The separation I thought would help in using less code, I could make a query for each one and have 1 macro run all three for me into a report that would have the 3 subforms, this way I could label them in the report separately. Chances are that people do not move up, but for that I would put in a yes/no box to disable it. I'll try and look at these videos, my work system blocks most but I'll try. If not I will look at them at home. Thanks for the suggestions.

[orange]

Good luck with your project. Enjoy the videos.

[DJ Rhino]

Good luck with your project. Enjoy the videos.

Watched the videos and got a few ideas. Here is where I am at for the moment. I still need it to generate a different number for each row and tie in the holiday list. Any ideas?

[orange]

How about outlining what you have changed since the earlier version of the database?
Just a few comments/point form would be helpful.
I see more queries, no structure change and no relationships in window. Is that it?

[DJ Rhino]

How about outlining what you have changed since the earlier version of the database?
Just a few comments/point form would be helpful.
I see more queries, no structure change and no relationships in window. Is that it?

I added more queries to filter the shifts for me instead of useing code. I now have the random number working, but having problems with selecting the top* entries.
Relationships: not sure what I need a relationship for as I only have one table, but I will need to incorparate the holiday table at some point and a day someone is scheduled for that week.

I was trying the Select top() SQL but it was giving me errors and highlighting Select.

[orange]

You may get some help from my material here or here

[DJ Rhino]

You may get some help from my material here or here

Good info, but I'm not using VBA. I need to write SQL in a query field. Can you write it in a proper format as in : Select top 5 {Query name]. Field in Query

When I tried it like that I got an error and after I fit ok on the error pop up box it highlights Select. Am I suppose to make a query based on the query I made the random numbers in, then type that in a field in that query?