I am trying to design a database for an HR management system.
I am not very clear on how to handle historic employee data in this database and will need some help and advice please. Any comment you can provide, i'd really appreciate it.
We will like to have record of historic data and the date of change:
Employee address change
Employee name change
Salary change
Position/Title change
Office location change
Number of dependents change
Bonus received year-to-year
E.g. if an employee name changes, we should still have to have a record of what the old name was.
Scenario:
Jacqueline Peters got married 2years after her employment date and now goes by Jackie Harrison (the changed was made on the system), she leaves the firm 10years later. Many years later, someone calls about a Jacqueline Peters that worked here, we have no way of knowing who that is.
Now, we would like to the salary she was earning 5years after she started here though her endsalary has changed 10years later she got a big promotion (salary change) and changed department, we have no way of knowing what her salary at a particular time was and what her old title was.
What's the best way to design this database to handle historic data? Have you worked on something similar? How did you go about it?
Any ideas and comments will be greatly appreciated.
I have little experience using Microsoft Access, but am attempting to create an order management system.
I have designed a system to store customer details and allow the entry of orders. I now want to develop this so that the system can handle delivery notes.
There are currently two tables for order information, since each order can consist of multiple items. So, general information, like the order date, order number and salesperson are stored in an orders table. A second table, Order Detail lists the items and item specific information along with the order number of which they are a part.
A whole order (i.e. all items assigned to one order number) may be delivered at once, or in smaller consignments. However, every consignment must have a delivery note with a unique number. So, there may be several delivery note numbers associated with each order, relating to different shipments.
I therefore need to create a system which allows the user to select which items from an order are being dispatched and then get Access to do the following:
Create a new record in the delivery notes table. The delivery note number will therefore be created (it's an autonumber). The user should be prompted to enter the delivery date and this should be stored in the date column of the delivery notes table, alongside the delivery note number. Enter the delivery note number in the "Delivery Note Number" field of the Order Details table, for every item that the user selected as dispatching.
Once this is in place, I will then be able to pull all the required details together with a query and then print the delivery note.
Bearing in mind that I'm very new to Access and so don't have any knowledge of scripts/macros etc, can anyone tell me how I can achieve this?
I am trying to create a database where I can search for a specific vacation spot based on the things to do there and qualities about those things.
Lets say I have the following
Arizona --> Grand Canyon --> Hiking, Rafting, Dining, fun, costly Arizona --> Family --> Dining, Scary, free, scary Utah --> Zion Ntl Park --> Hiking, Photography, inexpensive, fun Florida --> Disney --> Family Fun, Parks, Dining, fun, costly
So now I want to run a search to show me places that have "hiking" which would return Grand Canyon and Zion Park and it would return Zion under the Utah Category and Grand Canyon under Arizona.
Also, would it be possible to run a search of places that meet, say, 80%, of the results. So if i result for places that are costly fun, and hiking it would return results that matched it about 80% or so?
Hello !:) I need to make a SQL query in Microsoft Access. The database is in French but you will easily understand, if not please ask me to translate. You will find the MDB Access file inside the attached ZIP file.
The query I need to make is ::rolleyes:
We want for the next sent of books to the members of l’EST-SUD (WEST-SOUTH in English), to test the equivalences of the books.
We want to send for each book from the “genre” (theme in English) “Animaux” the most expensive equivalent book, we want to give a list with the original book, the recommended price (PrixCons in the database) and the most expensive equivalent book with his price.
-“NoLiv” is the book number in the database. -“TitreLiv” is the book name (title) in the database. -“NoGen” is the theme (genre) number in the database. -“NomGen” is the theme (genre) name in the database. -“PrixCons” is the recommended price for the book.
This is the query I tried, but it isn’t working ::confused:
SELECT DISTINCT * FROM LIVRE AS L, EQUIVALENCE AS E WHERE L.NoGen IN (SELECT G.NoGen FROM GENRE AS G WHERE G.NomGen IN ("ANIMAUX")) AND E.NoLivEq IN (SELECT LI.NoLiv FROM LIVRE AS LI WHERE LI.NoLiv IN (SELECT MAX(LIV.PrixCons) AS [Prix Max Livre Equivalent] FROM LIVRE AS LIV WHERE LIV.NoLiv=LI.NoLiv)) ORDER BY 2, 3;
SECTEUR (N°Sec, NomSec) ADHERENT (NoAdh, NomAdh, PrenomAdh, Sexe, DateNaissance, #N°AdhResp, #N°Sec) MEMBRE (NoMemb, PrenomMemb, Sexe, Age, #N°Adh) GENRE (NoGen, NomGen, Exemples) LIVRE (NoLiv, TitreLiv, ThemeLiv, PrixCons, #NoGenre) ENVOI (#NoMem, #NoLiv, DateEnvoi) ETUDE (#NoMem, #NoLiv, DateEtude, EvalEt, Commentaire) EQUIVALENCE (#NoLiv, # NoLivEq) Primary keys are underlined and foreign keys have a # before them
I'm using Access 2010. I have a form that has a combo box. The combo box lists all employee names and one has to be selected. I've been adding employees over time as new employees come. This DB is 3 years old so there are many employees showing in the combo box selection that are no longer here. I'd like to only have the current employees visible but I do not want to just delete them from the Table because then the historic data wouldn't be complete. How can I only show the current employees in the combo box and maintain the data in the table?
I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.
I have a field in a table that is comprised of mostly numerical data but some records are text.
I want to convert this field to numerical only and make a new field to put the textual data in.
However converting the field will delete the textual data. What is the easiest way to convert the field but save the textual data AND append the textual data to the SAME record that they were in originally in the new field?
I have built a table off a form that is being used for record keeping and my question is.... This table will be constantly updated with new information so how can I make it to where when new info is added the old info will not be removed or written over?
I want to export details from a table in access to a word document or any other document that will keep the current design and let me make changes afterwards.
Any idea how i can create historic view of the table/record so i can view any changes that have been made to a specific record?
For example...
I create a record. Later somebody comes and changes some of the data on this record. I would like to be able to view all the previous data before it was modified for this one record.
I want to know if possible how to define the structure or function that when you have a table "customers" and yoy (f.instance) change the address, you can save, modify the actual value but conserve the historical value.
Previously I am reading on how to create a Form that permit to obtain all the data required (not to complete) but to consult and if u want to modify.
I think this form could be easy but I want to create the register of the modification so that if any problem I can check the person who changed the data when and what items.
I have a database that contains details of payments returned to a bank account without sufficient information to identify who the money belongs to.
The DB is used by a team of people to retrieve details of an individual entry and when they have figured out who the money should be sent to they can mark that the payment has been "located". We can only send the refund at certain times of the month, so there is another activity "applied" where we have entered details on internal systems for the refund to be processed. Finally, once a month we physically transfer the money out of the bank account and add a "completed" date to all entries that were "applied" before that date.
My manager would like to be able to look back at a certain date to see what the balance was so he can make charts and whatever else in Excel. The balance will be all payments that were not marked as "completed" on that particular date.
I'm having trouble making a query that will give him the balance for each day in the past.
The important tables/fields here are
TBL_MAIN - dateReceived (date) and amountRefunded (currency)
TBL_DATES - activityDate - just a list of dates, which I use for a similar query of the amount we located/applied each day
So I'm looking for a query that will list all dates in tbl_dates against the sum of amountReceived up until that date, with the sum of amountReceived where completedDate is less than the date listed subtracted.
I have a need to convert the export of an Access report to an ASCII file.
The export needs to have a specific format - described below:
Field Position | Field Length | Field Name | Field format
1-6 | 6 | Member # | text - right justified 7-12 | 6 | Trans Date | YYMMDD 13-15 | 3 | Pay code | Alpha/ NUM - Left 16-26 | 11 | Amount | -9999999.99 (*) 64-71 | 8 | Check # | Right
general comments and questions:
This report needs to interface with some sort of transaction accounting system - No Idea what it is, but this format of the report will interface.
The field positions go from 1 to 73. I need only supply the data above, so the check # is way out of sequence. Do I need to have the space inbetween "Amount" and Check Number in the report? Will a simple text file work with all the fieds together? or will I need the space in there? Is there a way to design a report in an ASCII format in access?
Specific Questions
I think I need to change the format of the amount and date fields. I am currently using the short date format 8/30/04, but need to return the date as YYMMDD - 040830. How can I change this format to reflect the ASCII format. The form is also set with the default "=Date()" so the user doesn't have to enter the date everytime - it can also be edited for older data. I would like the date to appear the way it is on the form 08/30/04 but get entered to the table as 040830. Is there a way to do that?
The amount field is odd - I'll include the exact criteria that is desired:
"All Amount Fields must be right justified and space filled. If the amount is a credit, show the "-" sign at the beginning of the number. Payment should be entered as positive amounts. If zero, assign the fied as 0.00 (space filled)"
Is this just a mask that needs to be added? Currently, The field shows $7.00 when 7 is entered to add cent, you must type in 7.50, tab will put in the "$". I need to get rid of the $ and space fill the field?
I'm creating an automated system on access, basically it uploads client's files and analyses their data. The files will always be different, with the amount of fields changing and with different field names each time
One part of it, is appending new contacts to their data. This means records which we can add new contacts to, needs to be duplicated with the new contact placed at the end. So it needs to be like
Company Name New Contact Name A B B Tom B Harry
Because it's automated with different field names each time, the duplicating part is an issue. I can use the * rule which appends all fields, however this will not work in this case, if we are adding more than 1 new contact, the new contact will be duplicated rather than having 2 new different contacts.
Ideally I want rule saying, append all fields EXCEPT the fields where the new contacts are placed, but I don't think this is possible
I'm using Access 07 for this. Using a mix of VBA and SQL in the modules
The plus indicates a night shift worker eg started at 19.18 and finished at 5.37 on the 12th
so for every record I have staff id , date, and clock time ( I have stripped out the + ) and created a yes/no field to identify the records where field2 should actually be field2+1
I have sat in query design screen for ages and cant think how on earth I am going to calculate hours worked for a given staff member and date combination.
for every combination of staff id and date there should be 2 records - a clock in and a clock out
I thought about creating new fields clockin and clockout but struggling to see how I can link the 2 "paired" records together
I have over the last few months been building my own Project management system.This is based around A Phase task principle but the phases and tasks are used to create an estimate for a project depending on how much time is assigned to each Task.The system can use multiply Estimates with different billing codes plus their is a billing code system where billing can be done by a default list of codes (Rates 1-5) Or assigned rates for a client.I'm not after any money for it and will make it available to members hear.If anybody want to look at it and maybe make suggestions on improvements or let me know of any errors that would be greatIt would be an mde with an mdb back end.You should be able to update the back end that I don't think will be a problem LOL When it comes to new versions.As I said I don't want ANYTHING For you using it but would be nice to get it working better.mickp.s Ill Post some pictures if ya want. And It is a work in progress?Please use this topic from now on http://www.access-programmers.co.uk/forums/showthread.php?t=121117 this is where the beta 1 downloads available from.
date___ name 1_______1 2_______1 3_______2 4_______3 5_______4 6_______4
field date is unique while field name has what can i do to get the following such that there are no duplicates for the name field _________________________________________________ | name | date1 | date2 | date3 | date4 | date5 | date6 | -------------------------------------------------------| |1 ____|1 ____ |2 ____| ____ | ______| ______| _____| |------------------------------------------------------| |2 ____| _____ | _____|3 ___ | ______| ______| _____| |------------------------------------------------------| |3 ____| _____ | _____| ____ |4 ______| _____| _____| |------------------------------------------------------| |4 ____| _____ | _____| ____ | _______|5 ____|6 ____| |------------------------------------------------------|
Hi i have 2 fields order date and delivery date using a calender operation does anyone know how to make it so that the delivery date cannot be sellected on a date before the order date? cheers kev
I'm just wondering if anyone has made a very advanced budget management program using just access?? I tried to make one for myself but I just want it to advanced for my little knowledge of access. I would love to be able to keep track of all my expenses including everything bought from groceries to hair cuts. I would love to be able to take my receipt from walmart and enter everything bought and the price for each item. Then I would love to be able to at anytime search for soap and thing with soap in the Item name will be displayed with the location and the date and price. This way I can keep track not just what I spend on groceries but what I spend on mountain dew or what i spend on hamburger in a given time. This is just way to complicated for me! I hope someone can help me out here maybe give me a shell and let me try to fill in certain parts or if someone already has one that works kind of like this maybe i could just change a few things to the way i want it! I really appreciate this!!
I am trying to create a access database system which will show classes, students, attendance, progress report, teachers, departments etc...
i have repeatedly failed in accomplishing this database as i dont have the knowledge.
the place that i get messed up is for example the attendance, i want it so that i can select a date and then all the students associated with that class showup. so 1 date and a list of student only on that class.
another problem was adding a progress report for every class and individual students.
this is for a community based school which teachings on saturdays, there are over 15 volunters who do not get paid to teach but are still willing to give something back to the society.
i beg you guys out there, you all are geniuses!!! please help us design this simple system. please if somebody already knows where i can get a system like this free of charge then let me know ASAP!
I would like to thankyou in advance for reading thins messge.
Hi all, does any one one have or knows of a daycare attendance tracking and management database. We are a non profit org. Action for children out of Columbus, Ohio.
Hai guys Iam looking out for a sample DB for employees leave management. # With leave application form # Leave approval form # Leave status # All with username and password
