I am about to set up a database but wanted to check the relationship of the main tables before I add to it. I have attached the relationship design
For a PROJECT, there can be many TESTS, for a TEST, there can be many PRODUCTS
Is my design reasonably sensible?
I have a split database. I have just created anew table in the back end and created a relationship to an existing table. All done in the backend.
I have opened up my front end and imported the back end end. But when I check the relationships in front end neither the table or link is there.
I have two tables tbl1 and tbl2
tbl1 has 10 fields named tbl1.id tbl1.field2 tbl1.field3 tbl1.field4
tbl2 has only three fields tbl2.field1 tbl2.field2 tbl2.field3
Now i need to insert values into tbl1::
tbl1.field1 tbl1.field2 tbl1.field3
from
tbl2.field1 tbl2.field2 tbl2.field3
respectively, but i need to make sure if tbl2.field3 value is already there in tbl1.field3 then we don't need import those records. so we only need records if value of tbl2.field3 is not already there in tbl1.field3.
Please let me know What statement do I need to write so i can import all data from tbl2 into tbl1 by comparing as above.
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I have 3 tables.
Table 1: contains staff names and contact numbers
Table 2: contains training above staff have been on or need to go on
Table 3: contains pc and printer asset numbers of above staff
I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.
What's stopping the new entries from showing up in tables 2 and 3 ?
I've only just started using Access 2007 at my new job. I've been asked to create a database that will show appointments for all 10 of the employees. I have created a table for the main schedule (where ill put all the data) then one for each of the employees. I've managed to link the tables no problem but it wont let me create and updating relationship. It keeps saying "no unique index found for the referenced field of the primary table". How do I fix this?
I want it to automatically update the date, time, location, customer name and description, if its changed on the main schedule for a certain appointment on the corresponding employees schedule.
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?
I am trying to define a relationship between tables. However, the unique nature of my data doesn't seem to be allowing typical relationships. I am not sure if I need to somehow create a relationship (junction table?) or just keep things as they are. Do I need a Foreign Key? FYI, I am only querying the data - no updates.
I have two tables that I am able to join by using a field in Table1 tied to a portion of a field in Table2. No other columns in either table can reliably relate the two tables. For example:
Table1.ColA has a 5 character string.
Table2.ColA has a 10 character string.
I need to match Table1.ColA to Table2.ColA where the first five characters in Table2.ColA match Table1.ColA.
This match, produces 1 to Many results (for every Table1.ColA string, there are 1 to many Table2.ColA records that match.
IMPORTANT FACTORS:
- Table1 contains data at a SYSTEM level.
- Table2 contains the parts that make up the "SYSTEM" in Table1
- The PARTS in Table2 can be in 1 to many SYSTEMS from Table1
- Table1.ColA is not unique by itself (it's part of a composite PK in Table1).
- Table2.ColA is not unique by itself (it's part of a composite PK in Table2).
I am very new to Access and Visual Basics. I am trying to write a VBA code to do the following."If any table(s) exists in the current db, drop all the table(s)."
Basically, I am trying to drop all existing tables.
Hi
I run a small domestic house cleaning company in the UK. We are currently franchising our concept throughout the UK. I have a requirement for 2 databases, one for my clients & one for cleaners.
Would anyone be interested in creating something like this for me? One of the most frustrating things about databases on the market is that they do not come with a functionality to key in a letter code so that you can easily send letters/e-mails to the people in the database.
If anyone is interested please email me at csinclair@easynet.co.uk (put Access World in the subject box) I will then supply you with screen shots of the type of database I'm looking for.
Thanks
Chris124
I have 2 identical database in terms of structure but it differs in data.
Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.
I have used the "link to datasource method" through importing the tables to have the "updating" function.
However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.
Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)
I am splitting a database and have created the Back end already. When I create the front end and link to the tables on the back end... The front end does not link to all the tables in the back end. The list that comes up when creating the linkings does not show all the tables in the back end. What would cause this?
View 1 Replies View RelatedHi,
I have a db with two table.
Table1 Trade has the following among other fields: RefNo (autoNo) PK, TradeDate, HostName, Methodology, etc.
Table2 Market has the following among other fields: MarketDate as Date ( ), USD/EUR_Rate, USD/CHF_Rate, USD/BRL_Rate, etc.
I need to link both tables so that the TradeDate correspondes to the MarketDate. what is that say on TradeDate if it is on 15/01/06 I can see what the Market values for the MarketDate corresponding to that TradeDate.
I tried to link MarketDate as PK on Market table2 to TradeDate on Trade Table1.
It seems to work but it does not seem right as sometimes I get errors in entering data, etc on the Market table2.
The MarketDate is unique meaning only one data of values for a given date, they can be modified but not duplicated.
Can anyone help, on the best solution for the links to work?
Thanks
dfuas
I feel confused about something and I have the need for assistance because solving this small thing would make my life easier. I very much appreciate you taking the time to read this.
I'll focus in on a small part of my database:
----------------
| tblBreakpoint|
----------------
| BreakpointId |
| Remarks |
| ... |
----------------
each Breakpoint can have only one Module and a Module can have many Breakpoints
----------------
| tblModule |
----------------
| ModuleId |
| ModuleName |
| .... |
----------------
each module can have only one Function and a Function can have only one module
----------------
| tblFunction |
----------------
| FunctionId |
| FunctionName |
| ... |
----------------
I want to normalize my database as much as possible.
I will make a make a form for Breakpoint containing two comboboxes. The top combobox will contain all Modules and I want the second combobox to contain only the Functions that are related to the selected Module from the first combobox.
I know this can be programmed, that's how I do it now: in the onChange-event of the first combobox, I load the content of the second combobox.
However: what I would like to know specifically 3 things:
--> what is a common way to solve this?
--> Is there a way to solve this using just table-relationships? (drop 2 cbo's on the form and with correct relationships access populates the second and/or the first automatically)
--> What are the correct table relationships for this and therefore what missing ID's (used for relationships) have to be filled in in the above 3 tables?
Hi:
Tables:
I have 2 tables, there are same fields: Name, Date, and Invoice #
I make a relationship between 2 tables, connect Name, Date, and Invoice #
And click "Enforce Referential Integrity", "Cascade Update Related Fields", and "Cascade Delete Related Records".
One-to-One.
After that, I go table 1, create a record, then close it, then open table 2, the record I created in the table 1 is NOT over there. Does the relationship create record? It only update and delete. What about Add ?
Please let me know about it. Thanks.
I have broken up by master database, table, into several tables. They all share the same ID values since they came from the master table. So each table that I split off has a unique, matching, identifier, ID, in both the master table and the new table. Plus a previous matching identifier listed immediately below.
Example: Permit the master or main table and Builder a supporting table. There are five tables that I split off and all have an identifier similar to the two listed below.
Permit->ID
Permit->BLD
Builder->ID
Builder->BLD
All my tables have the same ID. But each table also has a identification numbers such as builder BLD, Subdivision SB and inspector INS>
Permit has the BLD, SB and INS
Builder has only one occurrence for each builder in the Permit table. As in the past it was identified by BLD number and now also ID. the use of BLD is from when I used visual dBase. The big difference here is that I have two ways to identify one is ID any other is by BLD, etc. My first thought is to identified by the BLD. I don't know why, but I guess I fear using a single "ID" to identify my various split off tables unique value or BLD.
First time user!!!
I work for the school dist. and we have 2 tables for 2 different groups.
I need to pull data from them both and create a new Query.
Example
Table 1:
Name, address, phone
Table 2:
Name, address, phone
(No similarities in ether database, and we'd like to NOT export data)
I'm hoping it's an easy fix that I've overlooked.
Michelle
Hi guys i know this is a simple one but i have been struggling for ages now i would very much love some help. I have a customer database that has a number of tables customer, quotes and materials. I keep getting errors all the time from the database when i try enter information im not sure whether it is my relationships or my tables setup.
The first table that will be displayed is the customer one once all the information has been entered into that one then the customer database should be linked to the quotes table and then from the customers database it drops down into the quotes with the little + sign and then the user enters all the information to do with the job but the field Materials must call a dropdown box, and then display the contents from the materials database, but only the material name.
If anyone can help please do as i have been struggling with this for over a week now and have tryed to solve it myself but with no joy Thanks in advanced i have included the database link below:
I am a newbie to access and i am working on a project that has two tables
the primary table has for example the following fields:
firstnameID(primary key) data type auto number
Firstname data type text
the second table has for example :
LastnameID(primary key) data type auto number
Lastname data type text
FirstnameID (foreign key)data type number
I created a relationship between the FirstnameID in the primary table and the FirstnameID in the second table ..
My problem is this whenever i try to view the relationship when i open the primary table it don't show the relationship ,
The Field FirstnameID in the secondary field is always Empty the only time the relationship is created is when i manually insert the autonumber that is generated in the FirstnameID field , but i thought that since a link was created then that field would have automatically be inseted with data , am i assuming wrong or am i doing somethingn wrong? how do i get the autonumber to be inserted in the field automatically to create the relationship?
Please Help
Hello guyz,
am currently havin a problem with a report but i fink the main problem is with the relationship between my tables!. so i have built a scholarship system. I have details like school certificate,A levels result and degree information.
i have built a report which retrieves the school certificate results,A level and degree results for a particular candidate, you have to enter the id for a candidate to get the details.
But the fing i have notice is that all these 3 tables must contain records so that all the information is displayed, for example if a candidate is applying for an undergraduate degree for the first time, he'll obviously be having only school certificate and A levels results....The report doesnt display anyfing!!....But if for a particular candidate having all three records in the 3 tables, the report displays correctly!.
Is there an option for fixing the cardinality among tables so that even if a candidate doesnt have one among the three information, the report gets displayed??
Kind Regards!
I have been trying to design an Access 2013 desktop database to report on user access to shared folders within the company I work for. Acess to folders is granted by users being added to a permission group and groups being added to folders.
What I want is to be able to query the data and find out which users have access to which folders. My tables are currently:
tblGroups
GroupID: PK Autonumber
GroupName: Short Text
GroupType: Lookup (Permission; Distribution; Mailbox Sharing)
[Code]....
If I do have to have a junction table is there anyway it can autopopulate from the other data entered?
(Access 2013 is the version I am using)
I have created a many-to-many relationship but am now having trouble figuring out how to add new data.
I have a table PEOPLE:
ID First Name Last Name
1 Participant A
2 Participant B
3 Participant C
And I have a table BEHAVIORS:
ID Behavior
1 Yelling
2 Grabbing
3 Smearing feces
And I linked these together in a many-to-many relationship in a table PEOPLE_BEHAVIORS:
ID Participant Behavior
1 A Yelling
2 B Yelling
3 C Yelling
4 A Grabbing
5 B Grabbing
6 A Smearing feces
7 C Smearing feces
All that's good to go. But now I need a table so I can count how often these behaviors occur every day. I need to link PEOPLE_BEHAVIORS to a new data table that will count how often I see the behaviors linked to the people every day. So my table should look like:
ID Participant Behavior Date Frequency
But I should only document the frequency of the behaviors linked to that participant (so for Participant A, I should have a space for yelling, grabbing, and smearing feces whereas for Participant B, I,d only have a space for yelling and grabbing). Is it possible to create a table that will basically partially create records for me? So if I open the table to add data today, I'd see the following and fill in the frequency blanks?
ID Participant Behavior Date Frequency
1 A Yelling 6/16/14
2 B Yelling 6/16/14
3 C Yelling 6/16/14
4 A Grabbing 6/16/14
5 B Grabbing 6/16/14
6 A Smearing feces 6/16/14
7 C Smearing feces 6/16/14
I have to make a database for my class project, and I am having problems with the relationship between tables. I have these tables:
Application
advertisement
employment agency
listed position
In the advertisement table I already linked listed position and employment agency. It shows the position name from the position table and in the agency column shows the agency name. When I link the application table to advertisement table and select position and employment agency column, it just shows up as the id numbers in the application table. How can I have it show "engineering| we find you jobs" for the table?
I have created multiple tables in my database and now for some reason it keeps telling me it can not find a few of my existing fields.
View 5 Replies View Relatedlooking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.
in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste..
Hello....
Here is what I am doing. It is an inventory database that also is an order tracking per se database.
When you are entering in the order, the top part is the vendor with an order number, date etc (will show table later).
The subform is the order details. This is the tricky part that I am having problems with.
In the subform I also need to have the unit that is placing the order for that particular item.
IE
product 1 pens black ball point unit=exams
product 2 pens red ball point unit=admin
product 3 paper legal color white unit=personnel
I can get it to work but the unit part is what is driving me nuts.
I am attaching a very stripped down version no queries etc....
Thanks