I have a main table which records employee,date,record employee is related to tble employee. i want to be able to delete an employee however keep the referenced records with the name, date, record, is this possible?
View RepliesI have 2 sub-forms attached to a main form. Each of these forms are based on different tables. Whenever I input the record, it goes into respective tables. However when I try to delete the record, it only deletes the record from the main form. I tried cascade delete. However, since referential integrity has to be established, I cannot add the record to my subform because there has to be a corresponding record in the parent table for me to add the record in the subform.
View 2 Replies View Relatedits been a while since i was last on here but i have run into some difficulty with a query
i would like to append some data to a tmp tbl.
i want to get the first appointment of the day for each employee. i have attached the relevant tables and a query that shows the information i need but it also shows more information. i have tried the nim function on the time and was successfull when i only had a few columns but when i added the colum [Items].[tblItems] it showed all the appointments for the day.
what i want to show is the first appointment for each employee.
How can I lock a picture of an employee to his Employee record?
View 5 Replies View RelatedI'm able to pull users that are logged into our network but our company uses a name that has their employee number as well. So deponding on when they were hired it could be a 5 or 6 digit number. so an example would be JSmith123456 or it could be JSmith12345 so is there a way to drop the numbers at the end? Meaning if they are entering data into a form that pulls their name can I drop off those last digits in the name field?
View 8 Replies View RelatedI have:
- tbl_company (containing company info)
- tbl_employee (containing employees info)
- tbl_cim (containing working contracts info, related with both tbl_company and tbl_empployee)
In the tbl_cim i have starting date of the contract, working time per day (in hors, according to the contract), ending date of the contract, etc.
I need:
- once a month i have to generate a "excel-like" sheet containing employees (rows) on a selected company, days of the month (1-31) i choose (columns) and hors worked on each day (according to the working_time from tbl_cim). The working hours i have to be able to modify.
- the days before start date of the contract have to be empty; the days after end date, also.
- the saturdays and sundays have to be marked ina different color (cell background).
I ask:
- what tables i have to create additionally?
- how can i create a form for this infos?
- is there a way to do this with less vba as possible?
In my country, Romania, this type of "spreadsheet" is called a "pontaj" and companies have to do it every month. I would like to step from Excel to Access with it.
I am currently creating a DB for a Customer Service team so management can keep track of who is skilled in what area, sick days, holidays etc. I have created three tables so far as follows:
EmployeeT with a primary key 'EmployeeID'
SickT with a primary key of 'SickID' and a foreign key of 'EmployeeID' linking to the EmployeeT (Reason behind this is that I may have multiple records for one Employee, meaning I would need a unique identifier)
SkillT with a primary key of 'EmployeeID' linking into the EmployeeT (Only possible to have one skill record linked to one employee)
Set up relationships as follows:
One EmployeeT to one SkillT
One EmployeeT to many SickT
I then created a blank form and used Tab Control.
On the first page I was able to populate the page with all the Employee info.
On the second page I was able to populate with all the skills relevant to the initial employee.
Then I tried populating sick records relating to the employee on a third page, but that's a no go. I was considering putting a subform on this page, but I wouldn't know where to even start with that.
I am facing one problem while calculating the number of hours an employee spent in the office. Each employee will have a swipe card and the swipe data is stored in an .mdb file. Some employees will have different shifts and may come after 6pm and will go next day 5 am , while calculating their spent hours getting the problem. The swipe data is stored in IOData table , I wrote a simple query below to get the data., it is calculating correctly for the employees who come in between 11AM to 8PM but who are coming after 6PM and going next day , facing problem. Time is storing in 24 hour format in table with Status as Entry,Exit . There may be any no.of exits and entries , so i took min and max of the time in the below query.
I am attaching the mdb file data in an excel sheet(Master_Data.xls) and also the data of the below query(Swipe_Data.xls) and highlighting the employee for which we got problem.
SELECT HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus, min(IO.IOTime), max(IO.IOTime), DateDiff("n", min(IO.IOTime), max(IO.IOTime)) AS Minutes, [Minutes] 60 & Format([Minutes] Mod 60, ":00")
FROM HolderData AS HD, IOData AS IO
WHERE HD.HolderNo = IO.HolderNo and
HD.DepartmentNo IN ('0008', '0009') and
IO.IODate between #01/20/2014# and #01/24/2014#
GROUP BY HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus;
I was wondering if it is possible to create custom security permissions in access. For example I have created an employee database, with security. I would like to have it when a manager logs on, it will only display his employee's information and no other departments. Is this possible?? In one of the tables is a field for the department the employee works on, can it based off of a table field?
View 1 Replies View RelatedJust a question, I am developing a database for the company I work for and being new to Access, I have successfully made a login menu when the database starts up. The employee selects there name and begins data entry. Is there any way to log the activity of each employee, which records they inputted, date and time and so on?
View 3 Replies View RelatedI have a form, "Update Employee Information" that allows me to navigate through records and change data like name, adress, etc. I would like to know how to create list (or autofill) so that it won't take long before finding an employee.For example: I have a Employee ID and would like to search on that so the form can fill in the rest of my information. I have thought of two methods that I like:
(1) Click on a list "Lookup Employee", select ID and then the form will fill in the rest
(2) Without clicking a link, typing in the ID in the form and the rest of the data gets filled in.
I have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError
Hi,
I have a database where by users can input images. The database then copies the selected image into the current working directoryimages (c:dbimages as an exmaple) the it keeps a reference to the path in a table. When users then browse the records on the form I have an ImageFrame which looks up that path and displays the picture. This all works fine.
The problem I have is that sometimes the working directory changes. I then have problems with the previous images inputted as the tbale where the reference is stored is still pointing to c:dbimages. How can I can it so that rather than have a fixed path to c:dbimages the database will just looking in the current application directoryimages for the pictures?
Many Thanks,
use the same referenced ID more than once in the same table? I have a database for my stamps. The exact same type of stamp may be kept in more than one location (Album). So, I have TBL_Albums which has two fields "AlbumID" and "AlbumName". Can I use "AlbumID" in my main RecordSource "TBL_Main" more than once to reflect the different Albums this stamp may be found in without causing any major problems to the database?
If this is okay, I could then assign different alias names to each "AlbumID" column in "TBL_Main" such as Location1, Location2 etc to be able to tell them apart on forms etc.
OK, Here is my issue:
CustomerTable:
PK:CustomerName
AgreementTable
PK:[Lookup]CustomerName from Customer Table:Agreement#
AddendumTable
PK:[Lookup] Customer Name:Agreement# from Agreement Table
So, Each customer may have more than one agreement.
Each agreement may have more than one addendum.
So when I create a table to create a new addendum, naturally I would like to link this to an agreement (which has already been linked to a customer). When I do the lookup wizard for the agreement field however, instead of being returned a list of customers, i get the PK of the customer and not the customer name.
How do I get the lookup to show the customer name and not the PK.
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
My database is built using Access 97.
I have the following table and 2 queries:
Table: “Materials Master Sheet”
field: “Material”
field: “Re-Order Level”
field: “Re-Order Quantity”
Query (crosstab): “Most Recent Count Numbers”
field: “Material Name” (joined to table’s “Material”)
field: “Count Quantity”
Query (crosstab): “Orders Pending Delivery”
field: “Material” (joined to table’s “Material”)
field: “Total Orders”
I am attempting to create a query called "Count vs ReOrder" with the following fields:
1. “Material Name” - comes from “Most Recent Count Numbers” query
2. “Order” - if the inventory count has reached the reorder level point, then 1, else 0; criteria: 1
expression: IIf([Most Recent Count Numbers]![Count Quantity]<=[Materials Master Sheet]![Re-Order Level],1,0)
3. "Suggested Order" - reorder quantity - count of any orders pending delivery; criteria: > 0
expression: [Materials Master Sheet]![Re-Order Quantity]-[Orders Pending Delivery]![Total Order]
This works great if all materials have orders pending delivery. However, if there are no orders pending (material not displayed in crosstab query), the material is not displayed (even though, in reality, the suggested order should be >0)
Currently does this:
material 1 - count = 1, re-order level = 2, re-order quantity = 8, orders pending = 4: shows material and suggests ordering 4 (correct)
material 2 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 4: material not shown (correct, as suggested order would be 0)
material 3 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 0: material not shown (incorrect! should be shows material and suggests ordering 4)
How can I go about addressing the missing “Orders Pending Delivery” numbers?
I'm creating an amortization schedule database using Access 2010.
I designed it such that I'll input actual principal and interest amounts in tbl_LoanActivity when I get statements in. I'd like to be able to press a button to update forecasted principal and interest amounts, then later I'll add code to make a report pop-up.
I initially designed this with only one loan to see if I could make the thing work, and it did. I've since added another loan and made adjustments, and this is where I'm having an issue.
tbl_LoanActivity references tbl_Loans, which has interest rates and monthly payment amounts. Needless to say tbl_Loans is the RecordSource for frm_Loans, and as such has a field LoanID. I have two queries that need to reference [Forms]![frm_Loans]![LoanID]:
1) qry_LoanActivity_MaxDate,
2) qry_LoanActivity_withPrincipalBalance
I can run these 2 queries (individually) with a reference to frm_Loans.LoanID just fine. (Without that reference, forecasted principal and interest numbers are highly inaccurate.) I have a qdf append query (vba) to calculate forecasted principal and interest amounts based on the most recent principal balance (referencing queries above), and relevant data in tbl_Loans (InterestRate, and RecurringAmount).
So here's the problem: without the above queries referencing [Forms]![frm_Loans]![LoanID] the qdf runs, yet produces inaccurate numbers. With the above queries referencing [Forms]![frm_Loans]![LoanID], Access gives me a message "Too few parameters. Expected 1." If I take the qdf and put it into a standard query it runs just fine. I need this append query to be a qdf so it will loop until RecordsAffected = 0.
I've sterilized the data and attached the database. I was thinking this adjustment of adding the reference to [Forms]![frm_Loans]![LoanID] in the 2 above queries would work, but it seems like the qdf is not inheriting the parameter.
Do fields in Access Tables have a 'Number' property that you can use to reference them?
For example, if you have the following fields:
EmpID
FName
LName
Department
Hire_Date
Is there a way to query an employee's Hire_Date by telling Access to return the value that is in the 5th field [Hire_Date]?
[URL] ....
I am trying a to build a slot booking database in which users will be able to book slots (ranging from 1-30) on a particular day for a specific site (location).
When trying to build the relationship between slot in tbl_available and slot in tbl_appointment i get the following error "No unique index found for the referenced field of the primary table" the same error pops up when trying to build a relationship between site in tbl_available and site in tbl_appointment.
I need both relationships to be 1 to many.
I am trying to create a one-to-many relationship between these two tables. I want to be able to access the 3 fields on the [Processors] table within reports based on [AllItems]. [AllItems] is a listing of account activity where the [AccountNumber] repeats. I have every field set as the "Primary Key" on [AllItems] as that is the only way to avoid importing duplicate data. I am getting the error: "no unique index found for the referenced field of the primary table"
View 3 Replies View RelatedI have a form with two tables referenced. I am using the form only to update one of the tables. I am using the other table to pull a reference field. When I add my second table using the query builder, it makes it to where I can't edit/add in my form. I assume its because of the SQL insert statement, but I don't see this statement and can't find where it is to edit it. How to have the form only update one of the tables, while just using the second table as a reference for a field?
View 1 Replies View RelatedI have a query of records that are not related to records is the secondary table. I want to delete these records but when I select the records and click delete I get the confirmation to delete answer to delete them and all the records disappear. When I run the query again, all the records are still there. Does anyone know what is happening? This is an access front end that connects to a SQL server database.
View 1 Replies View RelatedThis works perfectly.
SELECT DOCSADM_PROFILE.SYSTEM_ID, DOCSADM_PROFILE.AUTHOR, DOCSADM_SECURITY.THING, DOCSADM_SECURITY.PERSONORGROUP, DOCSADM_SECURITY.ACCESSRIGHTS
FROM DOCSADM_PROFILE INNER JOIN DOCSADM_SECURITY ON DOCSADM_PROFILE.SYSTEM_ID = DOCSADM_SECURITY.THING
WHERE (((DOCSADM_PROFILE.AUTHOR)=21941909) AND ((DOCSADM_SECURITY.PERSONORGROUP)=4038));
Now how do i tell this query to delete these selected records from security table....?
I have a survey form that has the questions on a subform based on the response to one of the survey demographic questions (via a query based on that response). The answers to the questions are on a sub-sub form. How can I get the program to delete the answers that were entered (they are related to the primary survey form with a common ID number) if the user changes the survey response.
In other words, if the user of SurveyID 5 decides that they want to fill out the list of blue questions rather than red questions, how can I delete all the answer blue records for SurveyID 5? Right now, it will display the red questions if you change to red and enter red answers, but the blue answers remain in the database, creating 2 sets of answers for SurveyID 5.
Any suggestions...if this is not too confusing?
Thanks!
Hello all:
Form created based on a query. When i go to the form, the add new record and delete record buttons are disabled.
I have checked and re-checked the form, tables, queries and all seems well.
Does anyone know how I can fix this problem?
Many thanks,
Dion