Relations Problem With Tables

Apr 19, 2005

Hi,

I am creating some table for a database I want to use.

I had the following tables:

[Organisation]
orgOrganisationID (primary key)
orgName
orgPostalcode
orgStreet
orgCity
orgCountry


[Contacts]
coContactID (primary key)
coSurename
coFirstname
coFysicPostalcode
coFysicStreet
coFysicAdditions
coPostPostalcode
coPostStreet
coPostAdditions
coTel
coFax
coEmail


[Postalcodes]
poPostalcode (primary key)
poPostalCity
poPostalCountry


I have two postalcodes in the Contacts table. I can't create two one-on-many relations from the Postalcodecodes table to the Contacts table.

So I thougt I split up the contacts table and create a one-on-one relationship between the two new tables.

So it would look like this:

ContactsGeneral
cgGeneralContactID (primary key, autonumbering)
cgSurename
cgFirstname
cgFysicPostalcode
cgFysicStreet
cgFysicAdditions
cgTel
cgFax
cgEmail


ContactsPost
cpPostContactsID (primary key, numeric)
cpPostPostalcode
cpPostStreet
cpPostAdditions

I would create a one-on-one relationship between coGeneralContactID and coPostContactsID. And now I can create my one-on-many relationships between from the Postalcodes table.

1) Is this clever?
2) Should both these fields have a key?
3) I set coGeneralContactID to autonumbering and coPostContactsID to numeric. is this also correct.
4) I also use prefixes before the fieldnames 'cp-' for 'ContactsPost' is this naming convention ok? I think it makes programming easier. But are there condingstandards for that in Access/VBA?

Thanks,

Jayke

View Replies


ADVERTISEMENT

Tables :: Table With Multiple 1 To Many Relations

Apr 7, 2015

I have a table named [Brand] and several tables named [Brand@@@Models], one for each field in the Brand table.I want each field from the [Brand] table to be related to it's specific Model table. I thought like that so I don't have a Brand#1 related to Brand#2 models, etc.. I didn't want to have all the models for all the brands in a single table, was I right to think like this?

View 6 Replies View Related

Relations After Splitting

Oct 21, 2005

Hi,

I've split a database and the backend relationships are still intact but the front end they are not and it looks like this is causing a problem. Is this usual?

View 5 Replies View Related

Reports In Relations?

Aug 30, 2007

How do I make the reports show in relations?

I'm (as said before) totally new to everything that has to do with programming, and therefore I work in the visual way, and thus I need to have the reports shown in relations in order to work with it...

Any ideas?

View 1 Replies View Related

Is Relations A Kind Of ERD

Jan 12, 2008

After designing the database that mean after creating all the tables and when pressed on relations buttuon it shows a diagram with relations established. Is this a kind of ERD. in this it shows relations between entities so can we call that an ERD?

View 3 Replies View Related

Help With Some Simple Relations

Nov 24, 2004

Hi there,
I'm a bit stuck on this but it should be pretty easy.

Say I have a table secondHandCar - this contains infromation on the makeID (eg 123), price, number of doors. Now each car can have a number of images, this number isn't set so I have an images table...

tbl_secondHandCar
makeID - key
price
number of doors

tbl_images
makeID - key
Image (ole, binary whatever)

Now clearly I have a problem: the images table is going to have many makeIDs with different images. I've just got stuck here - do I need an image id or something? But then my make ID will still repeat in tbl_images.

Thanks for the help :rolleyes:

View 1 Replies View Related

Relations Driving Me Crazy

Mar 3, 2007

Hi,
I'm trying to build a mdb which holds customer details, which insurance they have, who sold them this and which Insurance company provided this.
The relations I have to make are driving me nuts !
When I try to enter a new customer in a Form, there is a advisor added, the package the customer wants is added. But this shouldn't happen for the advisor and the package allready exists !

Who can help me with the proper relations ?
Attached is screendump of the current relations.

Thanks a lot !

View 8 Replies View Related

Table Relations Problems!!

Aug 8, 2007

I have a DB and for some reason queries aren't working properly. My guess is the table relationships I made. If anyone else has an idea on changing my relationships so that all forms and queries will work to thier full potential please give my DB a look.

Any Help will be greatly Appreciated
18338

View 2 Replies View Related

Relations In Db For Same Fields But Different Records

Nov 1, 2005

Access user,

:confused:

I am looking how to generate a relation in one database between records of the same field type.

f.i. you have a database where you try to keep track of your parts.

You order a part1 but they deliver an identical part but from another brand so it has another part number.

You will store it under his correct partnumber being part2.

Due to the fact you still have stock of part1 and just recieved part2, now you will run out of stock when part1 + part2 reach zero stock.

In order to be able to monitor this you must be able to generate a relation between part1 and part2 showing that that parts are fully interchangeble.



How can you realise this in access?? Or is this impossible?



Also would it be possible to have for a certain part number multiple relations?

f.i. parts are fully interchangeble

restricted interchangeable

one way interchangeable



Thanks in advance

View 1 Replies View Related

Structuring Table Relations!!

Aug 9, 2007

Hi all,

I built a database and I was wondering if anyone has any suggestions on how to normalize my DB. I think my table relations are wrong.

I'll post my DB for you people to view.
18354

Any Help will be appreciated

View 5 Replies View Related

Urgent DB Relations Seem To Have Broken Down

Sep 14, 2004

Can anyone shed light on why my Database seems to have stopped working so far as I've a combo box on a main form, which provides one half of a PK/FK relationship. The subform displays one or more records for this value. However, I've just entered the same 'value' (string) for an entry in the combo box as another, which shouldn't display any records in the subform yet - but it displays the records of the first combo 'entry'. This important thing is that the key's are all unique (I've doubled checked this), so why does it seem to not work?
Please.

View 1 Replies View Related

Form/subform Relations Help

Aug 16, 2006

I have 3 tables Metrics, FixedExpenses, Variable Expenses, I want them all to have a date in a form, and a subform for all the records to be added. I want to be able to do this without having 3 seperate date tables linked to the other three tables. I am not sure how to do this correctly. (I want to be able to see all the records for a certain day in these forms.)

View 1 Replies View Related

Tables :: Linking Field Data Between Tables And Within Tables

Sep 26, 2012

Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.

The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.

View 2 Replies View Related

Tables :: Consolidating Multiple Tables In One Single Table (Sorted Ascending By Time)

May 30, 2014

What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tried Union coding but always get Syntax Error etc.

View 8 Replies View Related

Tables :: Two Identical Database - Importing Tables By Linking To Data Source

Dec 3, 2012

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?)

View 1 Replies View Related

Tables :: Export Multiple Tables Into Single MS Excel 2010 Workbook

Feb 10, 2015

I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.

How can I export three tables into a single excel workbook.

View 1 Replies View Related

Tables :: Developing Related Multi Level Child Parent Tables?

Jan 14, 2013

I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?

This is a skeleton view of what I'm going for:

Master Table:

tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor

Sub Table:

tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes

Sub table to Sub Table

tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes

Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.

View 1 Replies View Related

Tables :: Calculated Fields As Data Type In Tables - Calculating Total?

Apr 23, 2013

I am using calculated field as a data type in access 2010.

They are working fine.

However, I added a new field and now the final calc won't work.

I have Subtotal adding loads of fields together. Works fine.

Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.

The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?

View 2 Replies View Related

Update Query Using 3 Tables: Source, Joining, Destination Tables

Apr 19, 2007

I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.

I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.

I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.

I have tried a few options without success ... anyone knows how to do it?

Thanks,

Josée

View 1 Replies View Related

Tables :: 2 Similar Tables / One Takes Too Long To Append Data

Jul 19, 2014

I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:

boss - client - type
12345, 67890, 1
12345, 54321, 2

If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:

12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2

That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.

67890, 12345
12345, 54321
...

In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this

67890-12345, 67890, 12345
12345-54321, 12345, 54321
...

Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.

View 2 Replies View Related

Tables :: ODBC Imported Tables / How To Ensure Index Is Created

May 13, 2015

I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:

Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"

one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?

View 5 Replies View Related

Tables :: Calculated Fields From Two Tables / Based On Relationship In Third Table

May 29, 2014

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.

View 6 Replies View Related

Tables :: Joined Two Tables - Unable To Filter / Lookup On Second Table?

Feb 18, 2013

I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.

View 14 Replies View Related

Tables :: Deleted Error In Microsoft Access Linked Tables

Jul 24, 2014

I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.

The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,

View 3 Replies View Related

Tables :: How To Fill Local Tables In Application With Disconnected Record Set

Sep 26, 2012

I want to fill local tables in some application with disconnected recordset. The tables in the front end application having the same table structure as in the back end database. The front end application was linked with the back end password protected database tables. I want no connected linked tables in the front end application. How can I fill the local tables in the front end application with the back-end password protected tables?

View 8 Replies View Related

Tables :: New People Added In Table1 Not Showing Up In Other Tables Despite Relationship

May 5, 2013

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 ?

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved