Making Sure My Tables/relationships Are OK
Nov 13, 2007
Hello,
I'm creating a database for a home builder to track a lot of info, but mainly: house info, prospect info, actual customer info, and contract info.
My main tables are:
tblCustomers
CustID PK
(a bunch of customer fields)
tblHouses
HouseID PK
(a bunch of house fields)
tblContracts
ContractID PK
CustID FK
HouseID FK
(a bunch of contract fields)
The releationships are as follows:
One house can have many contracts
One customer can have many contracts
I thought I could use one table, tblCustomers, to track the prospect info and actual customer info, as so much of it will be identical (technically someone should be entered as a prospect before they can be a customer, and all info will be the same for both except for extra information fields for prospects, but I always want to be able to refer back to those fields) and I would know a prospect became an actual customer because at least one contract in the tblContracts would have their CustID associated with it.
Is it acceptable to have two foreign keys in tblContracts? And am I missing something that should exist between House and Customer? House and Customer are only releated when there is a Contract, so it is implied (I think) that a Customer can be associated with many different houses through many different contracts. Is that the right way to look at it?
I've come up against a wall regarding linking two forms so that the second form displays the data associated with what was showing on the first form. If I'm good here with the tables I suppose I'll post my detailed question in the forms area.
Thank you so much!:o
~Merissa
View Replies
ADVERTISEMENT
Dec 10, 2013
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.
View 3 Replies
View Related
Oct 28, 2005
Hello,
I want to comine the following queries into one so that I can alter the parameters from a webpage.
I have a users table that has the department in and I have a TimeTable Table which has dates and option codes.
I need to select everybody in a department in the example it's Logistics.
I then want to link these people to the timeTable Table based upon a field.
The problen is that I want to select only a specific week. I can do this using two queries but that isn't going to work in ASP.
The Results should be as follows:
Person Date Option
A
B 01/10/2005 1
B 02/10/2005 2
C 01/10/2005 1
The two Queries look as follows:
SELECT TimeTable.*
FROM TimeTable
WHERE (((TimeTable.Date)>=#10/24/2005# And (TimeTable.Date)<=#10/30/2005#));
The above is then saved as Query1
SELECT Users.*, Query1.*, Query1.TimeOfDay, Query1.Date
FROM Query1 RIGHT JOIN Users ON Query1.UserID = Users.ID
WHERE (((Users.Department)="Logistics"))
ORDER BY Users.Surname, Users.Firstname, Query1.TimeOfDay, Query1.Date;
Any help would be great!
Cheers,
Matt
View 2 Replies
View Related
Jul 11, 2006
Hi there, i have a database i am setting up with 4 tables and am trying to think of the best way to input new data into it. All the tables are linked and its all based on printers. When a new printer is brought if it exists in the database then thats cool it just needs a Printer number assigned to it and the database will do the rest looking up printer model and what cartridges it requires. But if u add a model for a HP printer for example you need to add the model number to the tblink and create new cartridges and use the cartid to link the modelID i hope u get the picture!? lol there is a pdf i have created using onenote with screen dumps and comments about what i need it to do. If anyone has some advice or help it would be very greatful! Thanx mike
View 1 Replies
View Related
Jul 10, 2013
Here i have a situation i have a table where like below
Name | orderno | mail no | contact No |
---------------------------------------
a |123 |5555 | 553453 |
b |321 |8569 | 52353 |
a |123 |2344 | 553453 |
c |143 |567 | 553453 |
d |173 |6787865| 553453 |
But i need a separate table where the entry of all the entries where a is coming only once.
View 2 Replies
View Related
Mar 27, 2013
I am attempting to set up a saved import procedure in an Access database that will be run programmatically using docmd.RunSavedImportExport. The source document is an Excel file.My goal is to trap any import errors caused by inconsistencies in the source data. I'd also like to provide the user with some small amount of feedback on what is causing these errors.
My initial thought was to make use of the "File_ImportErrors" table that is generated following an unsuccessful import. *Not once* has this import procedure consistently generated an error table - sometimes the table is created, sometimes it isn't.
I have tried changing the file format of the import file to comma- and tab-delimited files, respectively. Both of these formats do consistently generate an error table, however none of the error values in the table are accurate - it reports a slew of type conversion errors for fields that are completely consistent and unproblematic, whilst completely overlooking conspicuous errors from text strings in date fields.
I have tried the same import, both procedurally and manually, using all available Excel and text-delimited formats, with the same respective results.I notice that nearly every thread on AWF pertaining to error tables discusses how to delete them, rather than make effective use of them. I suspect this is perhaps why?
View 1 Replies
View Related
Apr 14, 2014
I've been doing some reading on multivalue fields and it seems to be exactly what I'm looking for. My question: Is there a way to use the multivalue field without making it a lookup? I need the user to be able to put multiple values in a field but can't limit them to a list. The field that needs multiple values happens to be for SKU numbers from a large department store franchise.
One work around I've come up with is to have a pop up that asks for the separate sku numbers so they can be used for the value list each time, but then the user also would have to select those from the value list in order to enter it.... too many end user steps.
I want to avoid making 10-15 different fields each for SKU number, Item, and cost... I also need to avoid having to populate my db with the department store's full list of products...
View 5 Replies
View Related
Jun 11, 2013
I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed.
View 2 Replies
View Related
Sep 24, 2012
I am trying to make my primary key in my table a calculated field.
I want the field to be subject + catalog + topic no. The only problem is that there is not always a topic no.
How would I write this expression?
View 5 Replies
View Related
Aug 24, 2013
Im trying to create a database to track who worked on what item and on what date, with four tasks required to complete the item.To give an example:I have a factory that builds Lego models, for each model there are four steps:
1)Unbox Parts
2)Sort Parts
3)Build
4)Check build
Any employee can work on any task, and multiple tasks can be done by the same employee.Having a completed item table with a field for each task, and a date for each task, with one to many relationships from employee id to each task type. This failed as I needed many to many, so I made a junction table to link them, and this is where I get a little muddled.
Do I need a table of task types, then a table tracking each task to feed into the item table, or is there a simple solution I'm overlooking?
View 4 Replies
View Related
Nov 19, 2004
CompanyID pk (just one company)
CompName
EmployeeID pk
companyID fk
roomID fk
extensionID fk
LName
FName
LocationID pk
RoomNumber (many employees might share same room)
PhoneDirectoryID pk
ExtNumber (employees might share same extension number)
roomID fk
ItemID pk
ItmName (messengers take envelopes to different employees)
equipmentID fk
employeeID fk
EquipmentID PK
eqmtName (equipment might be used many times to deliver jobs)
I just need to know if the relationships for these tables are right.
If you need more information about this, please let me know.
View 9 Replies
View Related
Nov 30, 2004
I am converting / developing a database that stores information pertaining to individual birds and their recaptures over many years. Here is a condensed version of the many tables in this database:
tblIndividual Bird:
Autonumber (Primary Key)
Band Number - also, unique to the individual bird
Sex - M or F
etc ..
tblCaptureInformation:
Autonumber (Primary Key)
Band Number - look-up from tblIndividualBird (using hidden Primary Key)
Capture #- # which indicates what capture this is (ex. Intial capture - 1)
Place
Age
Date
etc ...
Each time a bird is captured, we record information pertaining to TIME, MEASUREMENTS, and NEST INFO. So, I have seperated the data based on these headings and made them into individual tables.
Now, my problem .... I have already created a relationship between CaptureInformation and Individual Bird. However, in the last 3 tables I would like to create a drop-down menu which shows the Band Number and Capture Number and make relationships there. What is the easiest way to do this? As of now, when I make a look-up field in the last 3 databases to show this info, the Band Number comes up with the Autonumber (because I am using the CaptureInfo table) which does not really help someone entering the data. Thanks for your help.
View 4 Replies
View Related
Nov 30, 2004
Hi,
I am trying to create a db for service orders for customers. At the moment I have four tables, customer, service_order, parts and totals.
I have one form for customer records that has a button that when clicked opens another form for that customer's service orders. The service order form has two subforms, one for parts and one for totals.
When I try to add a new service order for my test customer it says "you cannot add or change a record because a related record is required in the table 'customer'.
As you can see here (http://www.abstractmusic.org/relationships.gif) I have three relationships setup. cust_no in customer table is a PK and so is service_order_no in service_order table.
Also I am having problems with the totals, as the fields are from different tables the equations won't work from within the subform (I guess I need some kind of query). I need the totals in a seperate table other wise I have a total for every part entry.
Any help would MUCH appreciated.
Cheers
Housey
View 6 Replies
View Related
Mar 29, 2006
The attached application is what I need to design a form in Access around. Please see if i set up the tables correctly and the relationships. Thanks.
View 1 Replies
View Related
Sep 6, 2007
I am trying to set up a database to detail dances published in a magazine over the years.
I currently have all the information in an Excel Spreadsheet but know that Access would be better.
The columns in my spreadsheet are:
Dance
Choreographer(s)
Level
Count
Date Published
Song 1
Artist 1
Count In 1
Song 2
Artist 2
Count In 2
Song 3
Artist 3
Count In 3
Song 4
Artist 4
Count In 4
Song 5
Artist 5
Count In 5
Song 6
Artist 6
Count In 6
Song 7
Artist 7
Count In 7
There can be two or more dances with the same name
The same choreographer(s) could have written more than one dance
The same count can be used for many dances
About 15 dances are published on the same date
One artist can have more than one song used
One song can have more than one artist singing it
One song and relevant artist can be used for more than one dance
I tried using Access For Dummies but it has confused me even more. I cannot work out what tables there should be and what relationships.
Not all dances have 7 songs for it - some have 1, some 2, some 3, etc.
What is listed as song 4 for one dance could be song 1 for another or song 5, etc.
Any advice gratefully received!
Thanks in advance
Chris
View 14 Replies
View Related
Jan 31, 2008
I'm a novice and I'm confused. Maybe it's the way I think. I feel like there is an easier way that I'm overlooking, but I can't seem to get a satisfactory solution.How would YOU create your tables/relationships if you had the following:* The general purpose is to manage orders* You have to store information about the order (like order number, date)* You have to store information about from what company the order is from (like address)* You have to store information about from which department of that company the order comes from, each department has their own information that needs to be stored (like contact person).Keep in mind that you don't want to memorize which department is from which company nor do you want to be able to make the mistake of entering an order from a department that is not a part of that company.It seems like it should be an easy thing to do, but I'm stumped. I've thought about creating a new table for every company with a sub table for every department but that doesn't seem very practical. I tried creating one table called Company and one called Department, then merging them on a third table which is then linked to a fourth table called Orders. I'm not convinced this is the best way to do it, but it's my best guess at this moment.All help is greatly appreciated.
View 1 Replies
View Related
Feb 10, 2008
I'm relatively new to Access so am unsure if i'm on the right lines with my system, but here goes:
The system should be able to have new records of students input, and their grades recorded.
At present I have it laid out as follows:
-tbl Pupil
--Pupil ID (pk)
--Surname
--Forename
--Year
--Address
--Phone Number
--Parent's e-mail
-tbl Present Grades
--Pupil ID
--Grade ID (pk)
--Subject
--Term
--Grade
-tbl Subjects
--Subject ID
--Name
Any advice on relationships between the tables would be appreciated. Thanks in advance.
View 1 Replies
View Related
Oct 31, 2006
i know tecnically you can create a table with no relationships but is it "ok" to do so?
im using a table to store some values which are only referenced through a query but it is completly detatched and has no relationships with any other tables, im awear my database will function perfectlly happily but is it an acceptable programming standard?
cheers guys
mike
View 4 Replies
View Related
May 18, 2013
I am creating a database of medieval labor contracts and have come across an issue.
I have a table of Contracts, and a second table of People. I want the table of People to show every contract in which that person appears. Each contract has multiple roles - there is always at least a Laborer and an Employer.
The same person might appear as a laborer in one contract, and an employer in a second contract and I want my People table to pull every contract in which that person appears, regardless of the role they play in the contract.
So far I have not been able to get this to work. I set up two different one-to-many relationships which link the People table primary key (personID) to two separate columns in the contract table. However, in the People table, instead of pulling contracts in which the person appears as either Laborer or Employer, it will only pull contracts in which the person appears as both Laborer AND employer (a situation which will never occur in my actual data but which I tried out as a test).
View 2 Replies
View Related
May 3, 2013
I have one database called asset management. It consists of one main table called cyber assets. Most fields in this table are linked to a manually created lookup table inorder to restrict user input. There are also two additional, none lookup, tables used to list a) the IP addresses (there can be more than one) and b) another similar 1 to many type table. Basically this DB is used to manage basic cyber asset data, excluding most items related to configuration management.
So, this above DB serves the purposes of asset management. Now I essentially need a similar DB for Patch Management. What I've done for this is to assess each patch initially (i.e. just by looking at the patch title and determining if we even have any of those device. i.e. this assessment is not based on OS, model number... just a general 'may' or 'may not' be applicable). Here's what this SEPARATE DB looked like:
Since each patch is essentially assessed against itself, or maybe a better way to describe it is against the users memory of what we do and don't have, only a single table and form was needed.
So now we've been thru this process and the DB is filled, all initial assessments are complete. The next step is to take all the ones that are applicable to our company (based on the initial assessment when you answer, yes is applicable) and do assessments based on each device we have.So what I want to do is to link the two DBs on a new table called Patches_by_device, inside the original patching DB... so the relationships would look like this:
But as you can see, the linked table CYBER_ASSETS has some sort of undefined relationship type, which is causing my issues.So the next thing I did was to autocreate a form based on the Patches_by_device table, and here's the result.I need to change the patch_key to the Patch_ID+Patch description+URL, etc, and to change the device key to the the UNID+IP+functional description, etc...so I changed the form record source like this:
Now I should be able to change the control source of the Patch_key and Device_key to more useful information. so I changed: Patch_key control source to Patch_ID and Device_key control source to UNID (which is in the cyber assets table)
As you can see, it worked for the patch_ID but not the UNID which is part of the linked table.Must it be within one DB, because we have a ton of other modules to implement (e.g. config management, vulnerability assessments, audit stuff, and more...) and I'd like all these to be in individual DBs, all liked back to the main cyber_assets/Asset management DB.I've considered just modifying that patch table so that each device has its own column heading in the table, but this will cause issues when new devices are added.
View 7 Replies
View Related
Oct 4, 2013
I have three fields in one table that need to be related to the PK of another table.
tblProject - Engineer_ID, Producer_ID, and Project_Maner_ID
tblEmployee - Employee_ID (PK)
employees can take on any of the positions for a given project, so i'll need to have multiple employees filling up different roles for each project.
when i try to set up the relationships i get the following message:
A relationship already exists.
Do you want to edit the existing relationship? To create a new relationship, click No.
I click No, and it creates a table named tblEmployee_1. Why? is this ok?
View 2 Replies
View Related
Jun 27, 2014
I want to build a Financial Database. We are provided a certain amount of budget under different heads each year. Every month we spend some money from some or all heads. Then we provide a detail of expenditure during the month under each head and the balance thereof. My request is how many tables I need in my database. My opinion is 5 tables each for Years, Months, Heads of Expenditure,Budget Allotted, and Expenditure.
View 1 Replies
View Related
Feb 24, 2013
I've inherited a data base which has the address details of our members spread across multi fields i.e. Add 1, Add2, Add 3, Post town, Post code etc.
Not all the fields contain information which means when I do a mail merge for address labels there are blank lines.
I would like to either be able to create a single field in the table (like a memo field for example) which contains all the address detail, or create a mail merge without blank lines.
View 3 Replies
View Related
Nov 29, 2012
I have two tables containing data from two separate sources. The unique "link" between the two is a JobNumber field. I need to set up a relationship between the two tables using the JobNumber field.
Problem:
In one table, the JobNumber has been input in this format: 12-00345-01
In 2nd table, the JobNumber has been input in this format: 12-00345-1
How can I tie these when the 2nd table is missing the "0"?
View 8 Replies
View Related
May 19, 2014
I have imported 3 different excel worksheets into Access. I then created a query in which I made some relationships and select the fields that I need to make a new table. The problem is, when I run the table the final product is missing about 20,000 rows of data. I have used this protocol successfully for other other files, but for some reason, with this dataset Access is pasting 10,000 rows into the new table, where the original file has 39,000.
View 1 Replies
View Related
Jun 19, 2006
I have a database with a table on a shared network drive. The table contains a list of buildings, building details and a unique building code. I want to be able to use that database as a master copy so any new buildings that need to be added can be.
I have another database with accounts and another database with some other information. I can create a relationship between the accounts and the linked table of buildings (by the unique building code) but if I am to go into the building table, there is no "expansion option" to see all the accounts for that building. Is there a way to create a proper relationship or at least make a copy of the buildings table so that each time the database starts up it can get the latest version?
Thanks in advance,
Bob
View 2 Replies
View Related
