hi,
i need to design and build a access database for school where staff for a business could request holidays with there holidays then appearing on sum sort of calendar which can be viewed.
does nebody know where i can find an example to help me with my desing?
thanx
glen
To all you access Gurus
I have been asked to look at setting up and access database to do the following
To keep a record of pupils and 9 tasks the pupils have to do and also the total amount of time it took a pupil to do this task
They have a total of 25 Hours to do all tasks but this is spread out over the year.
What I need is someone to tell me the best table layout to do this
The pupil record has to have the following
First Name
Surname
Form
Total Hours taken for all 9 Objectives
A description of what the pupil did to achive each Objectives
record if that task is complete
Also Date Task was Complete
I have done a test database with just one table in it and inside that table
had all of the above but apart from name, form and total hours I repeated the rest nine times.
This just does not seem right
What I want is to pull up the pupil name and then select Task say from a Drop down box this would then insert a new field if it did not allready exist in pupil recored and then you can fill in the task details of course if the field/s all ready existed then to open that up to allow you to update this
It would then need to update the total Hours field in the pupil record with the hours it took to do that task/objective.
I have basic Access knowledge but i cannot think of best way to achive this can anyone help please
Stuart Purvis
ICT TECHNICIAN
HILBRE HIGH SCHOOL
Hello all,
I finally got TreeView to work, by downloading the example database from this (http://www.access-programmers.co.uk/forums/showthread.php?t=80805&highlight=treeview) thread. I haven't had as much luck with ListView, though. Does anyone have a database they could post that has a working listview? I'd greatly appreciate it!!
Thanks,
Eric
I have been tasked with setting up a certificate request database. I have an Access 2032 FE & SQL 2012 BE. I need to store PDF documents from all our branches.
I have been working on this for the past 3 working days and I have seen a great deal about saving the file path to the database vs saving the actual document in the DB. The documents would be located on each users computer.
I have a Form with all the information that is required by the certification department. On the form is a button (Called InsertID(ID in this case is the persons Identity Docuement)) and the button is linked to a table called dbo_CertificationSupportingDocuments. The table is referenced to the Main Certificate Request table.
The dbo_CertificationSupportingDocuments table is structured as follows:
Code:
[CertSupportingDocID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[CertificateRequestNumber] [numeric](18, 0) NOT NULL,
[RequestBlob] [varbinary](max) NULL,
[RequestFileName] [nvarchar](50) NULL,
[RequestFileExtension] [nchar](10) NULL,
[RequestFileMimeType] [nvarchar](50) NULL
When I click the button I get a Run Time error 3075 "Syntax error (Missing operator) in query operator.
The code I am using is:
Code:
Dim fd As FileDialog
Dim strFileWPath As String
Dim FileNoExt As String
Dim FileExt As String
Dim StrSQL As String
Dim db As Database
[Code] ....
I have been looking at this code for so long that I cannot see what is wrong.
Just a reminder if you're expecting responses to your question that Monday September 4 is a holiday in the U.S. and Canada.
View 7 Replies View RelatedI have read more post than i care to think about but I can't stil get my head round the following problem.
I have created a database (with this forums help) but I am stuck on the following query/form/vba etc....
Our staff holidays run from 01/05/05 until 30/04/06 and i have a query that calculates what holiday entitlement staff are due from 01/05/05 on a daily basis up to 23 days worth of hols ending on 30/04/06, but I can't work out how to set next years holiday period 01/05/06 - 30/04/07 without changing the form manually, is there a way for it to auto update??
Hi all I am quite new in access, and I have been looking around the forum, but have not find anything that really matches my project, I don't even know if I can achieve in access!!
There is a screen shot of what I would like to create wich is a screen that shows me the full calendar year, the year can be changed with arrow buttons next to the year, the grey area are the saturdays and sundays per each year. Finally for every they in the calendar including the saturday and sundays I can insert a letter or a number the mark as holiday or other events and the field would change color as in the picture!!!
Is this possible?
Thnks
M
I've a nice formula which work the majority of the time, the only time this doesn't work is Bank Holidays. Is there a way to build on this:
>=Date()-IIf(Weekday(Date(),2)<2,4,2) And <=Date()-1
The above simply runs a report for the last working day, expect if today is a Monday then it runs Fri-Sun.
Im assuming if I know the dates of the Bank Holidays I could hardcode them into the beginning with multiple IIF statement followed by the one above??
Hi everybody,
Recently I created a database for staff details in my company and I used OLE Object in Data type to make it. The problem now there are more than 500 employees in the company so it is very difficult to scan one by one photo. Is there any way that once I click on command button photo will be detected from scanner and saved directly in the field instead of doing it manually.
Thanks and regards
Hi all,
I'm relatively new to Access and would like a few pointers...
I have been asked by work to create a schedule database. We would like to be able to put in dates and see who's working on that day, pull up an individual and see his/hers rota for a week. Also see if they have meetings, annual leave, sickness etc.... We want it to be a very primitive WFM tool. Is this possible and is it relatively easy? The database doesn't have to create the shifts, they will be imported from Excel....
Which leads me to believe I need to think of it differently to excel... Attached is an excel rota, which would need to be importred - but maybe I need to be think a lot differently.
I know thats a big question but any help would be appreciated, or pointers to more resources... Thanks
Neil
Hi All,
I need a query to tell me how many staff are on holiday on the same day..here what i have so far.
A query with name, startholsdate,endholsdate, totaldays, etc, etc,,
records showing; employee A books 01/01/06 to 14/01/06 and employee B books 10/01/06 to 24/01/06 C books 01/02/06 to 10/02/06 and so on,
I need to show how many employees are off on what days eg employee A and B are on hols between 10/01/06 and 14/01/06, so only 3 more staff may take days off between the 10th and 14th. I have tried all sorts of calculations but to no avail? can anyone help..
thanks
Hi all,
I'm a bit stuck with a db relationship design.
I need to hold information in a staff table about a person's Line-manager.
My initial idea was to just have the Staff Table and use the PK of a member who is a Line-manager as a FK in the Line-manager field in the same table for their staff.
I then came across a problem.
I need to be able to select all the staff under a Line-manager. For example a head of department may have 3 team leaders who each manage 10 staff. I need to know all of them, not just the 3 team leaders who are directly managed by the HoD but their staff as well.
As it happens there is no rigidgly defined managment structure per se for this organisation which makes things even more difficult.
I assume there is a reasonably standard way of doing this as all organisations have some form of line-manager/staff relationship. Anyone care to enlighten me?
Cheers
TS
i am unsure as to whether this can be acheived in a database..
basically this task is almost calendar like.
the company operates 7 days a week.
i have a table of company drivers and each has their own driver ID.
my user would like to store what time each driver comes into work each day and what time each driver leaves. this needs to be quite flexible however because some days certain drivers wont work, but each day..if a driver has a 'come to work' time then they will for sure have a 'leave work' time.
each driver also pays rent to the company once a week. within this calendar like system i would also like to store on what day the driver paid what amount towards their weekly rent.
i cant quite imagine how this can be done in a database. but if these kind of applications are common, i would be most grateful for some direction.
thank you.
one more thing..
i would also need to record during each day what time the driver left and returned from lunch (sometimes our drivers leave for a few hours during lunch time)
i want to store this because if they miss a job while they are on an extended lunch break im gonna roast em.
I have several tables and queries based on tables and cannot get them to generate the results I want when querying them (just one record shows when many should be showing), so this leads me to believe there's a problem with the way I have set my relationships;
tblStaff (Holds lists of all staff and their contact details)
queryCoordinator (Filters list of staff from all staff list based on whether they are a projectmanager)
queryLeader (Filters project leaders from list)
queryMinion (Filters minions from list)
tblProject Contains all details from project
I have the staff queries so I can choose from a list of names that are relevant to that job in a form, so we don't have to look through a huge list of staff.I then want to use a query to pull all this information together (project details and contact details of each person involved) so that I can print a single record on a report, however the query isn't working correctly and returns only one project record, not all of them.
There is only one leader, one project coordinator and one minion per project (as far as I am concerned) and I want to reflect this in the query by providing their name and contact details from the single staff list.
Is the relationship between the project and the individual roles Many-to-Many or One-to-Many. I have many projects, and many staff, however each project only has one leader, one coordinator and one minion. I'm thinking One-to-Many, however when I relate the tables together, using the name as the relationship on each form this is where I have problems.
I have a database of staff members. There is a table with the details of staff; name, dob, address etc.
I need to set something up that will record the training that staff attend and then be able to print a report of who attended the training session and also a report of training sessions individual staff members attended.
I need to set up fields in a table for date, topic and presenter and then I need to somehow mark off staff members who attended.
Hey, I am currently doing an A-level ICT project and seem to be stuck at the first hurdel. My problem being that i wish to update the 'Number of sales' field for all the staff in the 'Staff' table. I have tried serveral queries to count the number of sales, which are in the 'Tickets' table for each and indidual 'Staff ID'.
This was suscuessful although i am wondering how to transfer these values into the Staff.[Number of Sales]. Also some staff had the value of 0 so did not come up in results of the querie.
Is there any possible solution or is there a more simple way to solve the problem.
Many Thanks in advance Dan Parker
I have a table with staff in.
I have a table with the start and end of their shifts.
We have four possible locations and four weeks.
So I have four tables for each location.
I have a module that can work out hours and deductions based on time in and out. I built that into a seperate databse working on a one time in and one time out setup.
I want to incorporate the two so I can get the hours worked over four weeks at all locations and divide that by 4 to get the average weekly hours for a staff member.
Is there a way i can convert the weekend days into working days for example.
5/4/13 1:30 PM is on saturday what function can I use to make this day read 5/6/13 8:00 Am which is monday. Same thing for holidays.
Hi:
I'm working with MS access only in very basic things.
Rigth now I need to look some information in a Table that was created in MS Access 2000.I don't know if this is a problem with MS Access 2003, because i still have some ones.
1) The Zoom tool is not accessible.
2) The order of the items, change all the time and I need to sort them every time.
If someone could help me in this matter, I'll appreciate so much
Thanks
Hello all. This is my first post on here after reading many of the other threads as I try - often in vain - to pick up access.
I am in the process of building a couple of databases, the main one being one that is an amalgamation of 4 other/older mdb's. All tables/queries etc were simply imported from these older defunct mdb's and then are archived off every month so they have 3months data and no more in them. The only thing is when it comes to problem solving and/or new queries, it is impossible to tell which tables/queries relate to which sections of the Db.
So my question to you is can I rename tables and queries in any way so that any queries that are using these tables are updated at the sametime, and if so how is it done? I have asked my colleague who is working with me to develope my understanding of access and he is at a loss. We are in the process of trying the old fashioned way ..... getting a book from the libarary ... but thought I'd try this new fangled internet thingy first :D .
The thought of having to trawl through around 100 tables and queries to rename or even re write the entire query/formulae is so daunting, I simply dont have that amount of time. Im using Access 97 and would greatly appreciate any help on this.
Thanx
I have a query which selects a complete list of companynames and producttypes, and another query which selects a few companynames and producttypes. What would be the query to select the companynames and producttypes from the first query that do not include the companynames and producttypes from the second query?
View 2 Replies View RelatedHi,
First time on here, so hello all!
I have a very strange request from a client who wants to do something simple that they can't because of poor database design (not mine!) . I can't think of an elegant solution so I'm wondering if anyone on here can?
They have multiple tables containing address information, for example they have one containing information about certain buildings. They can use the softcopy of the database to retrieve information about the building but for backup they now need a hardcopy.
Now part of the building information includes the alternative names for buildings (for example "City Museum" and "Art Muesum" might be different names for teh same building). The hard copy needs to have a line entry for both names (proper and alternative) and this is where the difficulty lies.
Using a simple example the table might be:
colour fruit
red apple
orange
yellow banana
The output I would need in the report is therefore:
Red (Apple)
Apple (Red)
Orange ()
Yellow (banana)
Banana (Yellow)
Now I may be being dense but I can't see how I can do this! All help very gratefully recieved :)
Hi All,
Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be much appreciated as I promised the Boss this for tomorrow....:confused:
this is my SQL so far, it strips out the left of the postcode:
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.PostCode;
What do I add to do the count?
i have a curious and annoying problem :eek:
I have a Access 2000 db, been working fine for 6 months+ and suddenly today, two out of three forms give "not a valid password" error on trying to open them.
There has never been a password set on the db, nor on the forms - and the same user who used it successfully yesterday now gets this error, along with any other users.
What has happened and how do i fix it? All ideas welcome!!
Sham :confused:
Each time a person enters a part number into the form they will have an option to pick and load other forms. Looking to have 1 form with a cmd button that when pressed will go out and retrieve an object or objects (spreadsheet, word document and etc) and then place in the OLC’s and rename and store this object on a drive with the name of the part number and form name
I want the templates never to change or be updated. Only the objects store on the drive after the command button is pressed or the Ole object is double clicked can be edit.
I've tried hyperlinks, do not want to have operators do save as, also I would have to have all the forms entered for each part number prior to versus just using templates and having them be stored.
Example of save forms names
c:mydocuments racking12341234-Template-1
c:mydocuments racking56785678-Template-2
Not even sure this can be accomplished. See attachment for a visual idea.
thanks dmh
I will explain this situation using a scenario. Let's assumed that i'm
working on a library system where i need a loan form that will only
show books that are available for loan. In this case, this form has a
main form that shows library member's details and its subform shows the
details of the books that are loan by library member. In this subform,
one of the fields (ISBN no) displays its values using a combo box. Once
a value is selected from this combo box, values in other fields found
in the subform will be shown too. These values are based on a table
that contain books info, where only the value (ISBN no) in the combo
box comes from a query. This query will only show books that are
available for loan.
If a library member wants to borrow 'booktitle1' then this book will
not be available to be loan by other members and assumed that there is
only one 'booktitle1'.
I tried to update the field manually by changing the status field every
time the book is loan out. I hope to solve this problem in an effective
way.
I am new with access and i have difficulty to explain it in a much
better way. Sorry, if my description cause any sort of confusion.
These are the tables.
book_info (table1)
-------------------------------
bookTitle ---------- text
ISBN(pkey) ------- text
authorName ------ text
category ---------- text
dateReceived ---- date
publisher ---------- text
status -------------- text
user_info (table2)
--------------------------------
name --------------- text
userID(pKey) ------ text
address ------------ text
tel ------------------ number
hp ------------------ number
occupation -------- text
DOB --------------- date
loan_info (table3)
------------------------------
userID(fKey) ------ text
ISBN(fkey) -------- text
dateReturned ---- date
dateBorrowed ---- date
dateDue ---------- date
remarks ----------- text
Thanks.
Leah_603