I am confusing myself more and more as i read on normalization and queries and everything else. I am trying to create a database for parts that our company sells and am having trouble deciding how to lay out the tables. I figure I should use the part number for the primary key as it will not repeat. from there though, i have a problem, each type of part has different fields nessesscary to decribe its attributes. for example, one part number may be for a box and its describing fields might be brand, width, length,load rating, and height. but another part number might be for a circuit breaker which would have completley different describing fields like brand, amperage rating, voltage rating, connection type, and trip type. Should i create 1 table for everything even though many fields would be blank depending on which type of item it was or should i create seperate table for each type of item with only the required fields to decribe that item?
I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
i have having such trouble setting up some tables in a new db, and am wondering if someone could give me some tips as I really don't know what I'm doing (I am new to this).
I have one main table, called Client Information. The PK is CaseNumber. I also have 11 other tables. The PK in each of these is called things like CasedetailsID, witnessinfoID, etc. Each of these tables contains the CaseNumber field (supposed to be from the Client Information table). The Client Information table contains the PK from each of the other tables.
The way I have the relationships set up at the moment is that the Client Information Table is linked to each of the other tables via the fields called casedetailsID, witnessinfoID, casetypeID, etc. The relationships are all one to many (the 'many' side being on the Client Info table, the 'one' side being on the related tables). There is obviously something incorrect about the way I have the tables set up, however, as when I try to enter data into the form, although the data gets stored in the individual tables, none of my queries or reports seem to be working (ie, I try to run a report, but it doesn't show any data).
any tips or advice regarding a good way to go about setting up tables and relationships would be very much appreciated.
I have an Access 2000 database with several tables in it. Against each field in each table I would like to set every REQUIRED and INDEXED value to NO. Is there a way I can do this using VBA?
If it helps, one of the tables has 2 fields in it named TABLE NAME and FIELD NAME with every field against every table listed.
Hello everyone. I'm pretty new to access and need some advice on table structure for a new project I've been given.
One of our customers sends us an Excel spreadsheet each week containing their order. Currently, someone formats the spreadsheet, prints it out, and manually picks out the products we need to ship. I want to import this into an Access table. Basically, the sheet with some data examples looks something like this:
There are usually 30-40 records on this list. The COMPONENT field is actually a part kit. We have tables in a Excel sheet that list the parts in the kit as well as the quantity. For example.
What I basically want to do is run a report based on a query where each page has a contract number (one for each record) with matching ship date and location information and a list of parts needed on that kit.
I'm a little confused by the COMPONENT field linking to more than one part. In the little experience I've had with Access, one record was always linked to just one other record in another table. Also, I want the Contract Number, Location, Ship Date, and Kit # show up once at the top and the complete kit show up below.
I would appreciate any suggestions as to hot to set up my tables. We have about 30 different kits. Should I have a separate table for each or a huge table? Also how will I make relationships between the main table and the part table/tables? Am I going to have to have the kit # as a field in the part table and list it for every part/quantity record? I want to just list the description information (location, ship date, etc...) only once while listing all the parts and quantities for the kit.
I've only been using Access for a short time, so this might be a really simple question. I just need to be pointed in the right direction, so I can set the system up the right way. Any information would be greatly appreciated.
I would like to create a small HR database that holds:
* Employee details (Name, Date of birth, Entry Date, Exit Date, Function,Department, Shift) * Departments * Departments and capabilities needed * Employee, departments and capability achieved (1=yes, 0=no)
The idea would be to have a link between the employee the departments capabilities I can tick fields of the capabilities they have or have achieved.
When I have this I could run a report that shows a score per employee For example:
Name: Department: Orderpicking: Packing: Cutting:
John Warehouse 1 1 0
As the tasks / capabilities required are different per department you would see something different if you would have user Jane that works in accounting
Is there anyone that can give me some hints so that I can get any further??
For the moment I have 3 tables:
1. Employee information - John, Jane 2. Departments - Warehouse, Accounting 3. Department tasks - Department, Cap1, Cap2, Cap3, Cap4
When I set up lookup fields that point to a table do I save those tables in the back end with the main tables or is it okay to have in in the front end and not the back end? Users will need to make udpates to the tables for instance an employee table they would need to add employee names.
I need to create an inventory database that will track customer owned inventory as it is received stored serviced and sent back to my customer. I am in the oil and gas industry and store large amounts of customer owned pipe. many joints will have the same part number but each joint is one of a kind in that it has a joint,heat, lot, and batch number. here is an example,
Job Name: Shell ex Part Number: 129001 Quantity: 1000
Each one of the thousand joints will have a combination of this information making it one of a kind.
My system needs to allow my users to receive jobs, record where we store the pipe in a rack location, pull the specific joints out of inventory as the customer calls for them. We will receive 1000 at a time but the customer calls for portions of that job over long periods of time..
I agree completely with everyone when it comes to not using lookup fields in tables.But does that extend to Display Control of yes/no tables? What are the problems with setting the Display Control to "Check Box"?
Is there anyway to set a calculated field as the primary key? Or how to duplicate the value of a calculated field into a new field and set the new field as the primary key?
I need setting up a history table for contacts and the companies that they are associated with. I am sure this will be obvious to some of you database veterans but I am fairly new to Access and I can't seem to figure out the best way to accomplish what I am trying to do.
Here is what I need to do:
When a contact's employment status changes, I need to change the contact's current company association but somehow maintain his or her association with the previous company so that s/he can still be associated with past projects.
So, in my contacts table (TBLContacts), I have a foreign key field "CompanyFK" that links to my companies table (TBLCompaniesPK). There is a one to many relationship between TBLCompanies and TBLContacts.
I want the CompanyFK field to be the current company but somehow link the person with past companies too so that the project directories and subforms will continue to show the contact's association with the parent company.
Maybe I don't need a history table but something else?
I have a similar problem with companies that change name, too. How to deal with takeovers, name changes, mergers, etc.
Field 1: Group Text field ( Primary key) Field 2: Group Description Text field Field 3: Uidgroup( Autonumber)
Table 2: Subgroup Text field
Field 1: Group ( I want to bound this column to Table 1's Group field that is column 1) I have set bound column property to 1 and column count 1 and the Subgroup table is showing group fields as input perfectly no issues in that )
Field 2: Subgroup, Text field( Primary Key )
Field 3: uidsubg( Autonumber)
Table 3: Email
Field 1: Group ( Bound to Table1's Group ; showing values in combo box, setted bound column property to 1 and showing group field perfectly, no issue in that )
Field 2: Subgroup (I want to bound Table2's subgroup field, which is column number 2, so I wrote 2 in bound column property and row source is table subgroup ; Here is some error comes up, values from subgroup field of subgroup table not being shown up in Email Table's subgroup field as combo box. )
Field 3: Email Text field
Field 4: uideml (Autonumber ) primary key
I want to prepare a Data entry form should have all these fields from all the tables. That should work in following way, first user selects Group then User selects Sub Group and write Email and save the record.
What relationship should I set, or shall I change the table structure.
I have a contacts database and I am trying to set the relationship between the contacts table and the locality table. The contacts table has a LocalityID field that is a long integer and the Locality table has an autonumber as the PK. When I drag the LocalityID on one table to the other LocalityID I get the Can't create this relationship. When I look at the Edit Relationship dialog box the primary table is the Locality table not the Contacts table. I want set up a lookup on the contacts form that relates to locality.
I have a user that uploads a spreadsheet weekly. I want to assign a unique id to each record that is the current date plus the start and end date for the data they are loading which would be the week prior plus a counter.
For example: Data from the week of 5/4-5/10 is loaded on 5/12.
Record one would be 050414-051014-1 Record two would be 050414-051014-2 Record three would be 050414-051014-3
and so on. How do I accomplish this in my table design? They will be deleting the prior week data and pasting the new data so the table design will not change.
I run a soccer league where we track players attendance for each game. I currently do it on a spreadsheet where each game date is a column and each player is a row. We also track which team they play on at each game (they can play on different teams different weeks). I currently have a second tab in the spreadsheet to record which team a person plays on each week.
Setting up a table of fields for this is relatively easy. The problem comes to data entry. I want to be able to visually see the data like I can in a spreadsheet (names in rows, dates in columns, intersections containing either team name or whether attended) and whilst a cross-tab query gives me the layout, I cannot input data in a cross-tab query.
Access 2010..One organization that we work with provides us with a block of numbers for each of the two types of contract products we order from them; we do order non-contract stuff from them also.The block of numbers are the same (i.e. 20000 to 30000 this year) for each of the two products. This means that each product can have the number 20000, for example. We call this the Tracking Number. If it is one of these products, we need to select the Contract Number.
For all other one off orders we have with them, we assign our own Tracking Number starting with 00001. This Tracking Number cannot duplicate unless it is one of the aforementioned two products.Both the Tracking Number and Contract Number are in the same table. The user selects the Contract Number from a form (connected to the Contract Number table that has all the details on the contract) and the Contract Number is populated in the same table that has the Tracking Number.Each order must have a Tracking Number (no null)..Not all orders need a Contract Number (null okay).The Tracking Number and Contract Number combination cannot duplicate.I tried setting the primary keys to more than one field in the table, but they cannot have null values.
If not... I have been working on Plan B.... an AfterUpdate on the form (either the form or a field... don't know yet) that looks at a query that only has results if there are duplicate values.
I have a numeric field (long integer) in a table. I've set the format to fixed. No matter what I set the decimal places to, auto, 0 or 2, when I enter a value of 0.71, it displays as 1.00
In my form, I've also set the field property to fixed, but it displays the value as 1.
ok I work a lot on databases but have recently been asked to set up an access database for my team. We are a marketing team within a large insurance company and need a small access database for MI and project management.
I have made a start on this and going well so far but there are some things I need it to do that I dont know how :P
Ive never messed about with Acess before so bare with me :P ok so this is what im looking for, any help would be greatly appreciated.
I have set up various tables for look up wizards for some of the menus on another table I designed for the raw data entry.
I need two tables, two forms - one table for very basic generic data such as
- Job Number - Description - Business Unit - Owner - Due Date - Completion date
I then need to drill down into this to add more information, so its basically two tables on top of each other with co-dependencies.
So for example one of my team enter a new project at the very basic lvl, they have a job number, they know what business unit it is for and they know the date its due, as time goes by and work is done on the project I want them to be able to click on a button that takes them to another form where they can enter information that is linked to the original information.
So they search for job number 00000 and come up with the above data on the first form, I want them to then click on 'Project Information' button and be taken to another form that has a lot more stuff on there, like a description, complications, notes, links to documents etc etc. I tried to do this linking two tables together but it doesnt work as the two forms/tables are not co dependend and are irrespective of each other.
Does this make any sense ? and does anyone know how I might make this work ? Please let me know if I am not clear and I will try to explain as best I can
I would also like to change the form 'theme' aswell and have my companies logo transparent behind the data fields etc, anyone know how to do that as I only have the option to use the standard themes.
Is it possible to set which VBA reference boxes are ticked each time a database is loaded?
A problem is created when a person amending the database to the person using it has different setups and so some of the references show as "missing" and so compile errors show on loading.
I made on option box on a form called Status. The 2 radio buttons are marked Open = 1 and Closed = 2. Is it possible to open a query based on what the user selects. In the query I want open to stand for Is Null and closed would be Is Not Null. I know this sounds unusual but it works in the query they way I want it to. Is this possible? Thanks...
I tried various searched and came up empty handed.
I have a user that is using a database that is on a network. When she enters records using her computer and exists the database, they don't save. If she goes back in, they aren't there. However, if she uses another computer, they save.
I can assume that it's not user error because it's the same user on two different computers.
i have a subform within a form. when the form is loaded, i would like the subform to automatically be ready for a new record instead of shown the last record entered. i have tried various macros ( not VB ) but to no avail. the form shows the persons info and needs you to add a certain piece of info. this would be the only reason for the form.
