I have an employee and asset database. If an employee gets fired, I need remove them from the general employee records, but I want to save a record of that employee. Is there a way to delete an employee from one table and have it automatically added to another table?
my database just records end of day sales figures which are entered though a form, after they are entered the total for the day is archived. However the problem i have is that the i need some way of clearing the input table with a command in the form.
I have two four tables. Invent, trans, NewOrder and reorder tables. The new order table is not joined by a relationship unlike the others which are joined with the Invent ID field.
The new order table has the fields(ID,QTY,NAME,Order Date). I want to use this to order new items that are not in the Invent table. What do I do to check that I don't enter a new item name in the NAME field that is already present in the Invent table?
Hi all,I'm working on a library database for a small collection.I have two fields, 'Date Out' and 'Date IN'. Is it possible that whatever date is written for the date taken out, I can set the 'Date IN' to be 7 days later than that date?Thanks in advance.
I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.
I'm a maintenance coordinator for a small trucking company and I'm putting together an access database to track and manage all of our fleet maintenance. I'm rank amateur when it comes to access but I've been doing ok with it and so far I've been able to get it to do almost everything I set out to have it do.
But I'm having a hard time figuring out how to make this trick work. Here's what I want it to do. We have two groups of trailers, working trailers and storage unit trailers. We do preventative maintenance every six months on road trailers and once a year on storage units. I have a field in tblEquipmentMaster which shows the last PM date. I have a query and an report which use that LastPMDate field as well as the value of a StorageUnit checkbox to calculate what units are due/overdue for PM.
In addition to all this, I've got tables and forms set up to track maintenance records. I'm tracking the maintenance invoices as well as the details of each line item on the invoices (changed water pump, replaced tail light, PM service etc). So what I'd like to have happen is for the LastPMDate field in tblEquipmentMaster to update to the value in InvoiceDate in tblMaintenance anytime the field Description in tblMaintenanceDetails contains the text 'PM' for the unit number in question. Alternatively tblMaintenanceDetails also has a lookup field which contains maintenance codes. So the field VMRSCode could also be used as the trigger anytime the value 'PM' is selected.
I have two list boxes on a form. The first listbox contains CATEGORIES and the second contains ITEMS that belong to respective categories. So, when i click a category the next listbox displays the items that belong to that particular category.
I need to know how to add new items to a selected category and also delete items from a selected category. If no category is selected or item already exixts, item should not add.
I have a MS Access database, which contains three main tables. With these I have a completed table, which holds either Yes/No within it.
One of the tables, I have linked to the Completed table as a list box, which when a job has been completed, either yes or no can be selected.
But, what I want to happen, is, when the Yes is selected, I want Access to insert the date the 'Yes' was selected, so that the employee cannot lie about the date the job was completed.
Hope I have explained this in a good enough fashion.
I don't have a clue how to go about it, could anyone help.
I am try to build a small invoicing interface in access 2007. I have designed a purchase invoice form with a table purchase_detail. When I receive products from supplier, products have a specific batch # on its cover.
I entered products in purchase invoice with these batch #. Now when I sale these items through sale invoice form, I need to get batch # automatically in batch # box from purchase_detail.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?
I am working with a table where I want to be able to add choose the customer name from a dropdown and have the customer number automatically populate. I set up the dropdown already using the query builder for customer name and it works perfectly. I also chose customer number in the query, but it does not show.
Even better, in case of duplicate names, I would like to be able to select the customer number and have the name populate.
So ultimately, when I select either field, I want the other to auto populate...
Assignment Info: Assignment ID (Primary Key) Assignment Number Criteria Number
Grades: ID (Primary Key) Student ID - Linked to [Student ID] Assignment ID Criteria Number Grade
What I would like to do is be able to link the tables in such a way that for each student entered in the Student Info table, entries are automatically entered into the grades table for each assignment criteria.
For example: Assignment 1 has criteria 1.1, 6.3, 7.2, Assignment 2 has 4.2, 3.3
When John Smith is entered in student info, the grades table is automatically updated with 5 new entries in the form:
John Smith - 1 - 1.1 - Enter Grade John Smith - 1 - 6.3 - Enter Grade John Smith - 1 - 7.2 - Enter Grade John Smith - 2 - 4.2 - Enter Grade John Smith - 2 - 3.3 - Enter Grade
That way I can have a form that automatically shows the possible criteria for each assignment on the sub form for each available student without having to type it in each time.
I want to automatically hide all the tables in the database, and automatically hide any new tables that are created, imported thereafter, except one mastertable. How do i achieve this?
A field in a table can be populated by a lookup up but it has to be done manually or with a form.A "new" table can be created with a query that matches the data.
Is it possible to skip these steps and create a field that automatically populates with the data from another table based on other common data?I can do this in Excel but not sure it can be done in Access.
I created two tables, let's refer to them as Cars (VW, BMW and Audi) and Colours (White, Black and Grey).
Is it possible to create another table based on these tables - i.e. in the new table the rows will be the Cars and the columns the Colours as such:
White Black Grey
VW
BMW
Audi
And should I enter another Car or Colour in one of the first mentioned tables, then I would like this "new" table to update automatically. For example, if I have a new Car (say, Merc), then I would like the "new" table to update to the following:
Okay I had an idea and I thought I might get ffedback as I am relatively new to this. I went through a period a couple of years ago when I used access alot and was becoming familiar with VBA etc but I havent touched it in 2 years so Im pretty rusty.
I am running a study and need to have to create a database that:
a. collects data about participants b. Has a number of questionnaires (5), each of which can be filled out by participants.
THe main table has a number of fields that collects info about the participants the most important of which will be the ParticipantID - an automatically generated number which is my primary key.
In table 2/form 2 I will host questionnaire one. This will be linked to Table 1/Form 1 (Particpant Information) by this tables primary key - also the Participant ID. The relationship will be 1:1. Each participant can only have one Participant ID and will only need to fill out questionnaire one once.
Is it possible that when I add a new participant to the Participant Information table/form I also automatically create a record in Table 2/Form 2 (Questionnaire 1), as well as Table 3/Form3 (Questionnaire 2) and so on so that they have the same ParticipantID...?
I was reading a similar query somewhere else and they said to use the Form_AfterInsert Event command? Is this right (see here http://www.pcreview.co.uk/forums/thread-1687644.php)?
I feel a bit stupid but I am willing to learn and try new things Once I get started I think I will be okay. If you could steer me in the right direction it would be much appreciated.
I have a simple and small database having only one table. I want the data table to be exported in a spreadsheet format automatically at the end of the month. Is there any code or function to do that?
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
I have an Access table with data fields however at after the last field column, I have about 15 blank and unselectable or uneditable columns that I don't want there. How I delete these? Reason I want to delete them is because table is a subform on a main form and these extra columns are visible on it and they also cause a scroll bar to shown
