Creating Tables
Jul 19, 2007
Hiya iam new one here, and fairly new with access. I got my self into a mess to what is proberly very simple to solve. I have 5 different products, which have 18 components for each product, each component has a part number and a price. Iam finding it difficult to arrange this data into tables in 3rd normal form. Do I have a table for each product, then a table for components, then a table for part number and price, but how would I create relationships for these?? Please help its driving me mad, any help would be greatly appricated.
View Replies
ADVERTISEMENT
Dec 11, 2012
I created two tables, but i don't know what kind of relationship i should create.
In the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do?
View 6 Replies
View Related
Oct 11, 2005
Good day all,
up to now i only used simple databases with one table.
but now i am stuck.
here's what i need to do:
i have certain products that i put available to "borrow".
On certain products you can just borrow one particular product, but other products are linked. So if you need to borrow one, you're going to need another one as well.
So as soon as you book a certain product, it has to check whether there is linked products, and if all wanted products are available. Then it has to set a date for delivery and a date it needs to be sent back.
I'll use asp to link to the database.
i know i'll have 2 tables - 1 for the available products, and 1 for the custmer bookings.
What i'm asking though is how to set up these tables and then link them together. And also what type of info goes into which table.
Do i need other tables as well?
thanks
lee
View 1 Replies
View Related
Nov 9, 2004
I am trying to create a table using their login as the table name. I have captured their login but, how and can I apply that to a table name. :cool:
View 3 Replies
View Related
Sep 6, 2005
Hi there
I have made a form I am trying to bound 3 tables to.
The 3 tables are
tblPersonalInfo
tblBookingInfo
tblSkills
How I am trying to set it up is that I enter someones personal details in once. But using the same personal details someone can have more then one booking.
With the skills....each person can have lots of different skills.
Basically I work for a recruitment agency and I have been asked to build a database that can add new people.add skills to that person.....then add different jobs to the same person.
I have made 3 tables
tblPersonalInfo
PersonalID
chrForename
chrSurname
dtmDOB
chrEmail
tblBookingInfo
BookingID
chrJobTitle
curPayRate
PersonalID
SkillsID
tblSkills
SkillsID
Skills
I have joined all tables togeather (I think wrong but trying) the tblbookinginfo is joined to tblbooking using a 1 - Many relationship using the primary key of tblPersonalInfo to the field PersonalID in the table tblBookingInfo
I have joined tblSkills to the table tblbookingID through SkillsID
I created a query using all 3 tables and have used this to bound the form....
The form contains 3 pages and the 1st page works ok (it only contains info from the tblPersonalInfo) the 2 other pages don't seem to work as I want them to.
I would like to be able to click to add a new person and also new booking and new skills
Currently I am able to add new personal details but nothing else.
Can anyone see what I am doing wrong?
Thanks for taking the time to read this
View 11 Replies
View Related
Oct 25, 2005
I am having problems trying to create a new table in an existing database I am working on in Access 2002. When I try to save the new table I receive an error message saying 'Invalid Argument'.
I have checked if the database is locked but can't find anything to suggest that it is.
Can anyone suggest what might be the cause. :confused:
Thanks
View 14 Replies
View Related
Apr 2, 2008
Iam new to access and having problems create database tables and their relationships, from the following details without duplication.
Tape Identification Numberà unique tape identity
Date -à the date the backup is done .
Backup type à either Daily or weekly or monthly or Full System or Yearly or Special
Duration à either automatic or Manual
Technician name à Person performing the Backup
Comment à comments about the Tape’s history
Location à tape is either located onsite or offsite.
Number of use à Number of times the tape has been used
Restoration à tapes restoration Details, either yes/no
Requirements
1.perform backup everyday
2.Perform restoration everyday
3.Search for tape from offsite storage
a.Tape should be located offsite
b.If it was used for daily backup, it should be at least 60 days old from current date.
c.If it was used for Weekly backup, it should be at least 6 months old.
d.If it was used for any other backup type, do not use.
** the database should provide history
View 1 Replies
View Related
Mar 20, 2006
Hey :)
is it possible to create a query for 1 table and for the same query to retrieve some data from another table
Cheers
Tom Evans
View 2 Replies
View Related
Aug 24, 2007
Hi All.
Need union three TableA, TableB and TableC into one table called TableX? All table has same column name. TableX shouldn't has dublications.
Thanks
View 1 Replies
View Related
Feb 23, 2005
Hi,
If i have a new entry from the user, and i want access to create a table on its own, with the table name defined by the user and the elements of the tables already pre-defined by a sample table that i already do out... Is there any way of doing it? Thanks for any helpful souls around...
View 2 Replies
View Related
Dec 2, 2004
Hi all.
As i'm still what can be called a "newbie" in MS Access, maybe one of you wizards
can help me out with this.
I'm having a product table in ms access, containing a few columns. The regular stuff (type, ID, ..).
It also contains a column i want to store the links to their documentation to, called "download'.
The fields of that table are loaded into a data access page.
Now the data access page is loaded into the frame of an HTML page. Everything works well, but when it
comes to the download-field, i get no link, but a piece of code instead.
Now these links are relative links (not absolute ones). What i'm searching for (for quiete some time now), is a way to make the links show as normal links.
Is there anyone who could give me a hint or who can help me out with this?
Thank you very much in advance,
Kind regards
View 8 Replies
View Related
May 18, 2006
Hi all,
I am a complete newbie so please beware/be patient.
What I am trying to do is to create a report from two table, using the wizard it starts complaining about the relationships between the two tables, though I only want to produce the table on one report.
Please help.
Thanks,
The Gurkentopf
View 1 Replies
View Related
Oct 30, 2012
I was wondering how to create relationships between tables?
View 2 Replies
View Related
Apr 2, 2012
I am very new to access and I am trying to create two tables called Customers and Orders.
The fields in Customers are:
CustomerID (Primary Key), First Name, Last Name, Address.
The fields in Orders are:
Order# (Primary Key), CustomerID, Product Purchased, Address.
There is a One to many relationship between CustomerID in the table Customers, to the CustomerID in Orders.
The problem I am having now is with the Address field.
GOAL: What I am trying to do is have the Orders table automatically get the Address from the Customers Table.
If that doesnt make sense, whenever I create a new Order, I want to enter the customerID, and have it get the Address data from the Customers table automatically put into the proper field in the Orders table.
View 5 Replies
View Related
Nov 3, 2004
Hello:
I'm new to Access but not SQL, and am having difficulty coming up with a solution to the following problem:
I have one table for all Strings used in all other tables (to make Internationalization easier). The fields are: zId, zType, and zString. zId is an AutoNumber field. zType is a number which defines what type of string it is ( Item Name, Person Name, etc. ). zString is a text field with the actual text string (can be empty).
I have another table for Items, which have (among other things) Names (field name= zName). zName is actually a lookup into the Strings table and stores just the ID.
I want to create a form for people to enter a new Item. When creating a new item using this form, they will have to give it a Name, which will need to add a new entry into the Strings table with the appropriate type (ie, Item Name).
So, my question is "How to do the following..."
* on my form to enter Item Data, when a user goes to create a new Item, I must first create a new entry in the Strings table with the correct value for zType. Then, I must get the value for zId for this new entry and assign it to the zName field in the Items table for the new record that I am creating.
Any help is appreciated.
_C
View 2 Replies
View Related
Jan 1, 2005
I am having trouble getting ASP code to create a new column in an Access database.
This is what is currently coded, but does not work. Something tells me that the code should be Create instead of Alter, but not sure what the correct terms would be. I need the code to automatically create this column called PCName with the listed properties in the table called member. Any help would be greatly appreciated.
re.Open "Alter Table in member Add Column " & PCName & " varchar(50) default None",conn2
sql = "Update member Set " & PCName & " = 'None'"
View 1 Replies
View Related
Mar 20, 2008
Hey all.
I'm having a few issues with creating a Lookup on an Access form. I'll recreate the scenario below as best i can to help with my explanation.
I have 2 data entries. The first is a Post Code (Zip code), and the second is an Area Rating that applies to the Post code. For example:
Post code AB1 8 is awarded a rating of A
Post code SO1 3 is awarded a rating of F
I have already created a table in Excel with all 2,922 Post Codes (AB1 1 to ZE3 9) and the corresponding Area Rates (A to F)
What i'm making is a form that holds customer details. I'd like a combo box or text box that when you type in the Post Code, another text box automatically displays it's corresponding Area Rating.
Code:Post Code: Area Rating:------------- --------------| CM2 7 | | A |------------- --------------
I apologise for my really very poor diagram :P
So the Post Code box is either typed into or a combo box with the list of Post Codes, and the Area Rate box cannot be typed in to, but it automatically displays the record dependant on what is in the Post Code box.
Ideally, these will also be recorded onto the main table (IE not the table with the list of post codes or area ratings, but a 3rd table holding the rest of the details that will be used to create statistics and graphs)
Can anyone help me with how to do this? I've been trying to work it out all afternoon!
Any help is really appreciated.
View 3 Replies
View Related
Mar 4, 2013
I have a database of students that have taken tests. That database includes how many attempts it took them to pass. I have successfully generated reports that sort the exams by type, date, student, etc. I would like to also somehow insert a formula to show the pass rate of those exams on the top of the report. I know I can get into design view and build an expression, but how would I build an expression that would create a pass rate percentage based on whether or not a student passed an exam on the first attempt.
I have a DB with a column titled "Attempt" and the options "First, Second, Third, Fourth". I would like the expression to generate a percentage of students that have "First" in their box (as opposed to anything else) out of the total number of student records.
We need to track student pass rates in my school for a re-certification process.
View 3 Replies
View Related
Dec 1, 2014
My database exports data to one of our customers who then inputs the data into their system.
One field i have is "RO Number" which i have as an autonumber which works for the purposes of my part of the system.
However they have it set as "RO9999", (Their system has been set up for years, we are a new "branch") but they aren't in access.
They want the RO number to be a running serial from RO30000, RO30001 etc
The data in entered on a form.
View 3 Replies
View Related
Oct 27, 2014
I have an Access database, where one of the tables is near the maximum amount of fields (241 of 255), was just wondering if I could set up a secondary table that would be linked to the original table by a specific field and would store the other fields that would like to add to the table.
View 7 Replies
View Related
Aug 29, 2014
I am working on a dataBase and I need to have it check if a set of tables exist based on the data of another table and if any do not exist create the table(s) based on a template. the caviat is that the data in one table are both names of tables and field in another.
Table1 has fields named 'first name' and 'last name'; the data in that field is for example Peter Adams, John Smith. I need to check on start that the tables named PAdams, JSmith.and so on exist, if not create them from a table template with fields 'vacation', 'personal', 'sick, etc. also i need to check on startup that a table named Department exist; if not, create it with fields named PAdams, JSmith and so on.... is that possible?
View 1 Replies
View Related
Oct 30, 2004
Hi, I am fairly new to ACCESS. I understand the basics, thus i am a novice user.
I am trying to create a new database for managing booking and storing info on various artists.
In my main table where bands' info will be stored I am trying to make data input as efficient as possible and to ensure consistancy.
So, it leads me to ask this question... regarding inputing info of bands' location.
I want to use a lookup table to enter data for:
1 Country (Canada/US),
2 Province/State
3 Cities?
Questions:
1 Whats the best way to organize this?
2 Do i have to create seperate tables for each country and province? Or any kind of suggestion would greatly appreciated.
Furthermore, when entering data on the form, for instance if i select:
Canada --> then I'd want only Canadian Provinces to show in the following field ---> then i select Ontario --> then i'd like only ontario cities to show in the next field....
3 What would require for me to create this kind of thing... Not sure if there are 'if' functions in ACCESS.
Could someone please guide me towards the light?
thanks.
View 2 Replies
View Related
Jun 2, 2006
Could you please help me out.
I am trying to develop a DB that is multi user. i ahve not split the data from the forms into separate db's as the users are in different locations and our network can be very slow.
part of the requirerment is to produce a drawing approval form, my current sticking point is that the method i am currently using to create the forms only allows one person at a time to use the process.
currently i have a command button that runs a query and then creates a table from the results. the table is then used as the data source for a mail merged form in word.
what i ultimatly need is a process which allows multiple users to generate these forms at the same time and have the ability to save the form (with no code behind it) as a word document (either RTF etc)
i am thinking that if i create a query on the fly in VB and then use the results to use in a report this would allow me to do the above.....
So my questions are:
1) is my assumption correct
2) If so, how do i do it as i have never done it before
3) If the above is not the best solution/ does not work... what will/is
View 4 Replies
View Related
Jul 17, 2006
Hi
I was wondering how to create a form which is using data from three tables, the data in the tables does not have any relationship setup as they are not related to each other.
When I use the wizard and I select the three tables, Access starts complaining about the fact that no relationship has been setup...
Please help,
Thank you,
Gurkie
View 2 Replies
View Related
Aug 21, 2013
Using access 2010; anyway to create an er diagram from an existing database? I have inherited a fairly large database and need to make changes to some field properties and need to find out where these fields are located. I know I can go into tools and run the documenter but doesn't give me an easy format to look at.
View 5 Replies
View Related
Jul 19, 2013
My challenge is to take about 200 boxes, all of which are stuffed with file folders, and to create a database of their contents. That's all well and good. I made a simple table that listed the record, the date, etc, but I ran into trouble fairly quickly when I came across a folder that had folders inside of it. Basically I need a way to represent the folder structure the way it is in the box that makes sense within Access. What I'm imagining is something like this:
Record 1
Record 2
Record Group:
- Record 1
- Record 2
Record 3
Record 4
More recently though, I'm wondering if don't need to make a whole new table for that set of data. I just don't know how to set up the relationship
View 8 Replies
View Related
