Relational Problems, Add New Record To Many Side Of One-to-many Relationship

Mar 22, 2005

Hi! I have a table named Employee Records, with a primary key "ERName". I use this to link to a table named Certification Records, with ERName as the link in both tables. My problem is this:

When I add a new record to Employee Records table, the name is not thrown in to a new record in Certification Records. This is a problem, because in my form that uses a query that includes both tables, when I add a certification record, it doesn't populate the name field in Employee Records table. I don't know where I went wrong, but I feel stupid because this is so basic and I should have caught it earlier... any ideas, please? Thanks!

KellyJo

View Replies


ADVERTISEMENT

Delete Only FK On The Many Side And The Record On The One Side

Jun 25, 2007

Hi,

I want to Delete only FK on the many side first and the record on the one side by one click of a button. I wrote some code which sometimes works and sometimes it does not!!

I wonder if any one have a better idea or doing this please?


Private Sub Delete_Click()

Dim db As DAO.Database, rs As DAO.Recordset
Dim n As Integer, i As Integer
Dim vStart As Integer
Dim vEnd As Integer
Dim vSite As Integer
Dim vRCCID As Integer

vSite = Forms![frmSite].Form![SiteID]
vRCCID = Forms![frmSite]![Roads Construction Consent].Form![RCCID]
vStart = Me.PhaseStart - 1
vEnd = Me.PhaseEnd + 1

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPhase")
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
If n > 0 Then
For i = 1 To n
If rs![SiteID] = vSite Then
If rs![PhaseNumber] > vStart And rs![PhaseNumber] < vEnd Then
rs.Edit
rs![RCCID] = Null
rs.Update

End If
End If
rs.MoveNext
Next i
End If
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing

'/////////////////////////////////////////////

DoCmd.RunSQL "DELETE RCCID FROM tblRCC WHERE RCCID = " & vRCCID & ""

'//////////////////////////////////////////////
End Sub

tblRCC is the one side of the relationship and tblPhase is the many side.

Any help will be very much appreciated.
B

View 4 Replies View Related

Query - Show All Records On The Many Side Of A 1-many Relationship

Aug 29, 2007

I have two tables which I pull records from. One table has a list of sections. The other table is a list of chapters.

One chapter has many sections.

I have the sections table populated, and want to show all of the sections, even if they are not currently assigned to a chapter.

Currently only the sections which have a chapter show up in my query.

I know this is really simple to do ..... I'm just drawing a blank on it!

Any help would be appreciated.

Gordon

View 8 Replies View Related

Reports :: Report To Show Records Side By Side?

Aug 12, 2015

So I'm trying to manipulate Access to create a Directory for my church. I'm trying to get a report to show the church staff, which I was able to do, but I was wondering, is it possible to get the records to show side by side instead of one on top of the other?

I included a picture of the design view showing what I would like to see. Excuse the way the numbers are written, it's hard to write with a mouse.

View 3 Replies View Related

Forms :: Relational Database - Add More Than One Name To Related Record

Aug 23, 2013

I have a simple relational database with the proper linking tables, etc. I am normalized through BCNF. I have a table that will track tasking details, however, more than one person is assigned the tasking. How do i add to my form the ability to add additional people to the tasking?

If I drag and drop the name field then it is a one name for one box deal. I want to create a combo box that uses a query to prepoulate multiple elgible names (already built) and then you can select the person you want. Afterwards I want the task assigner to dynamically add additional people to the task with no true limit as the group can be as small as one and as large as thirty.

I attached a small screenshot of the basic table structure. I need to be able to add multiple SME's to the one tasking...

View 2 Replies View Related

Server Side Vs Client Side Queries

Nov 22, 2005

I have split my database, the data is in a DB on the server and the forms, reports, etc is on the client desktop. My question is "Is there an advantage to having all of my combo box queries (Lookups) on the server side (defined in the table as a combo lookup) or should I put the all on the form so that they reside in client side DB.

View 7 Replies View Related

Form With Three Sections To Show Side By Side

Jan 26, 2012

Form with three sections. I have three queries selecting different set of set in a table, I would like to show all three in a form side by side. How can I do this? I use form wizard bit it only uses one query as a source.

View 2 Replies View Related

No Record On Many Side

Nov 5, 2005

I have two queries, property info (one) and budgets v spend (many). I'm trying to work out what the gross price is, property cost plus budget or spend (which ever is higher). Got that working no problem.

However if there is no budget or spend I just need to show the property cost but I'm just getting Error#. I've tried using the nz function but that's not working.

Any ideas?

Thanks

View 4 Replies View Related

Tables :: Record(s) Cannot Be Added - No Corresponding Record On One Side

Feb 28, 2013

I've created two tables, one containing order data, the other additional order data. Not every order has additional order data.

First i've created them with no specific relationship and filling in data via form worked fine. If i added additional data, a new record in the additional order data table was created automatically.

Later i changed those tables to a "one to one" relationship by setting the long int field that links to the order data table to no duplicates. I just did it because i thought that's how it should bew. But since then i can't add additional order data via the form anymore, but get the error "Record(s) cannot be added; No corresponding record on the 'one' side" instead. I could just revert back to the one to many relationship, but it bothers me.

View 8 Replies View Related

Reports :: Cut Off Left-hand Side Of Text In Textbox And Show Only Right-hand Side

Mar 26, 2013

Access 2010

I have a report with some text boxes on it. Sometimes the text in these boxes can be a very long string of characters (maybe a path to some folder). In this case I do not want the text box to grow. I just want the report to show me the right-hand side of this path and cut off the left. How do I do this when still aligning my text to the left?

View 2 Replies View Related

Creating An Updatable Query Requiring Only One Record From Many Side Table

Sep 15, 2006

I have a form that stores information about attendees for a given class. In this particular instance, I only store one address, one contact information both which are optional. I've used subforms, but that has bought problems (one notable problem is that one field from tblAddress is required for *any* attendee, which is county they resides in, whether they give out an address or not. At this point my VBA codes to work around this is simply too buggy and a major hassle.

Therefore, I am now considering two possibilities: 1) make a unbound form and do everything manually without any subforms or 2) create an updatable query somehow that can pull together the needed information. The latter, I'm not sure if that is feasible, so I'm asking you about that.

Address and Contact Information are kept as a many side tables, and different queries I've made in past only is either non updatable or partially updatable, which does no good. If anyone can show how one can retrieve only one record from the many side and keep it as updatable, that'd be great.

TIA.

View 5 Replies View Related

Relational Database - Is It Possible?

Feb 18, 2008

Hi folks, I'm new here - pretty hot at Excel but a total newbie at Access, So..

OK, I've got my database up and running. It keeps records of about 500 staff - everything is working fine but I now want to do stuff beyond my capabilities.. I've got a master record (Master Staff List) and a main input form (Data entry/amandment form). One of the input controls (via a sub-form) is for recording the time staff have spent working at a higher grade. Now if this was a one-off occurrence I could handle it but any one person could have multiple occasions that need recorded and I don't want to 'overwrite' the previous record. I have tried researching the problem and I think I need a 'one-to-many' relational table and a way to add a new period from the subform when required. The subform would also need to show blank when I opened it.

Any help would be appreciated.

View 5 Replies View Related

Relational Database Help

Jan 19, 2008

Hi guys, I'm fairly new to databases and am just creating sample ones at the moment and following tutorials from books. I tried to create my own cinema/movie database yesterday and there's something wrong.

I really wanted to work this one out for myself but after trying many different combinations of various relationships I just can't seem to get it working.

I have four tables..

A member table: MemberID
A booking table: MovieID, Screening Number, MemberID
A movie table: MovieID
A screening table: Screening Number


So the booking table is a composite key that links the member tbale (via the memberid), the movie table (via the movieID) and the screening table (via the screening number)

The problem occurs when I try to make a booking form for members to book a movie.

I use the form wizard and take the following fields:

The MemberID from the member table, the Screening Number and MovieID from the booking table. Then I add other relevent information from the Movie and screening table (for example, screening time, date, price etc from the screenig table and movie name, information from the the Movie table)

The MemberID is displayed on the main form with the other information in a sub form.

The problem I am having is when I enter a booking on the form, I get an error telling me the Microsoft Jet Engine cannot find the screening number in the screening table or something like that.

It lets me enter the other information but not with the screening number.

Thanks for any help.

Benn.

View 2 Replies View Related

How To Use Macro To Go To Next Record In A Form With One To Many Relationship

Dec 6, 2012

I used the Control Wizard to create a macro on a command button to do record navigation i.e. go to First, Next, Previous and last record. The macro code, in the macro editor, is inserted on a command button on the form, asks whether it is a table, query form etc. I chose form and entered the form name but the error says the form needs to be open before I can run the macro. This happens when I open the form from a Navigation Control and Click the button containing the Macro code on the form.

View 1 Replies View Related

Relational Data Problem

Feb 16, 2008

Hi

Not sure if anyone can help. Its been about 10years since I've built a relational access database, but offered to build one for someone, but am now a little stuck.

Its a photo sales tracking db.

I have 3 main tables. Photos, Sales & Customers.

The main form is a photo form with embedded subform showing the list of the sales related to the photo. This works well!

The second form is a customer form with embedded subform showing the list of purchases (sales form) related to that customer. This again works, but I cant get it to show the photo title of that sale in that subform. I think I am missing some sort of link.

I have relationships between photos and sales, and customer and sales. But it always shows a blank photo title field in the customer purchases list

This is a very brief outline and I can explain in more detail if some thinks they can help. So far the database looks good and mostly works but it is all a bit relevant if I cant get the customer purchases to automatically show the name of the photo sold.

Any help most appreciated!

View 4 Replies View Related

Update Relational Databse

Mar 7, 2008

Hello everyone! I am completely new to MS ACCESS, so this problem might be fairly simple but yet it is eating my head.So if I get some directions it will be greatly helpful and appreciated.
Description:
The bakery buys ingredients from number of suppliers, makes a several kinds of cakes and sells to few shops. There are about seven ingredients in the first week with the possibility to add new in ingredients later on. There’s a catch though; on any day, only a single type of cake is made, in a single batch and all of them must be delivered before the next day’s cake are finished. It is necessary to be able to rack the ingredients used in a batch back to the suppliers.
I have made tables for suppliers, ingredients, orders and order line (with fields ordereId, ingredientId, quantity, unit price).
Forms that allowing users to allow new ingredients types, suppliers, and new order for ingredient are required which I have made. These forms are to be used to add the data for which ingredients are needed in the second week of baking.
So these are my questions on which I am stuck in

1. Do I need any more Forms or do I need to edit my form?
2. Is it okey to put table as the data source or would it be better to place a query?
3. How do I keep records of my earnings and expenditure, expenditure and keep them updated with my databases?
I hope you will understand that I am completely new to these topics and its really hard for me to grasp them all at once. Merely some guiding points will be of great help. Thank you.
6pandn21

View 2 Replies View Related

Relational Modelling Confusion !!??

Mar 9, 2005

Im trying to figure out how to model 'Printer' and 'Cartridge' information. The relationship between them is Many to Many, but to add to my confusion, more than one cartridge can be used in a printer (Black + different colour cartridges). Adding a new entity to resolve the Many to Many does not solve this issue of how a printer can have, for example, a black cartridge and 3 different coloured cartridges.

Any ideas how to model this please?? I have been toiling with it for some time and I keep going round in circles :confused:

I'm sure it shouldn't be as difficult as it seems, so I'm hopeful someone might be able to set me straight :o

View 1 Replies View Related

Split One Table Into 2 (relational) Ones

Feb 5, 2008

Hi there!

I'm currently working on a malformed Access database, not normalized at all.

It only consists of one table and is designed like this:

(table compartment)

#ID#Name#Member1#Member2#Member3#Member4#...#Membe r20#

So all the members are realized by single columns.
I created a new database, consisting of the main table, the "people" table and a relation table, connecting the people with the compartment (n:m relation).

It works great but I have to get the old data into the new tables. The old main table consists of more then 100 columns, and most of them are now hived off by using relations.

Now I'm looking for a smart solution to convert the table.

Do you have any idea? I tried to use a Query, but I couldn't merge two or more columns and put them in a single one on a new table.

Thank you in advance!

View 13 Replies View Related

Text Box In Relational Database

Mar 15, 2005

Hi all, i have a relational database. In a form, Transactions, i have a text box, CustomerID, which relates to the primary key of the customers table. At the moment, if i enter a number that has no related record in the customers table, it will alert me only when i try to close the form or move to another record. I am wondering if it is possible to check for a related record immediately after the text box loses the focus.

thanks
joe

View 2 Replies View Related

Relational Database Principles

Jan 22, 2008

The first time a person tries to use Microsoft Access, they are likely to miss the whole point of what a database is and how to use it. When they start having trouble making it work for them, they think in terms of what they may be doing wrong in their use of the obvious tools of Access. But often their problem is that they are unfamiliar with the principles that make databases so useful and they have started with a flawed database schema.

This tutorial is intended to explain the underlying principles of relational databases. It is, admittedly, rather theoretical and perhaps even boring. But without an understanding of these principles, a beginner cannot hope to use Access for any really useful purpose. If you are a beginner, I urge you to read through the entire tutorial.

There are several issues that arise time and time again, such as:

. Storing multiple values in a field.
. Storing calculated values in a table.
. Determining primary keys.
. Using Autonumbers.
. How many tables does this database need?
. Why don't my queries work?

Sometimes answers in the forum make it sound like these things are a matter of preference, or "which is the best?" But in most cases, they are not--there are well established rules that determine the one correct answer. It is only with an understanding of the principles of relational database theory that you can determine the answers.

So I hope you will find it worth your time to study this 7-page tutorial. If you think you've found something in it that's incorrect, incomplete or impossible to understand, please feel free to contact me. I have been known to make mistakes!

View 5 Replies View Related

Enforce Relationship Rules As If Deleting Record

May 25, 2006

Hello again,

Does anyone know if this is possible and is there an easy way to set it up?

My db has many relationships, hence if you attempt to delete a record from the table, it will not allow it if there are related records.

This is great, however.

My intention is to disable any record deletions. Instead, once a record is no longer live, the status of that record is set to disposed,exipired, etc.

I want to force the same rules as if attempting to delete this record but set to the status field.

Example message.
ie, Warning! You cannot dispose of this PC. There a related records in the Software License and Contracts Tables. Please reassign them and try again!

Any clues would be great.

Thanks All.

View 3 Replies View Related

Reports :: One Invoice Report For Each Record In One To Many Relationship

Jun 19, 2013

I have a report, based on two tables which I have combined into a query.

The first table is the invoice date & ref, and the second table is the invoice amounts & descriptions, which could be more than one.

I have based the report on the underlying query, and I want all the many invoice & amounts to appear on one invoice, but I get one invoice for each of the many sides of the relationship.

I have a related key field in each table, with a common ref and referential integrity and cascade update set, but it still produces one invoice rather than a combined.

View 3 Replies View Related

Unique Results Of Two Relational Query's

May 14, 2007

Hi, I have a query with approximately 100 criteria. That is a bit much, thus I have made a table of the criteria and in the query a relation laid to this table. The criteria are countries with an increased risk on tbc. I point these query on a column with parent 1 and afterwards on the column with parent 2. Then I want the results from both query's in a table, but without double rows. I have tried this with a merge query but these only give the results where the hits for parent 1 and parent 2 are the same. This is by far not always the case, so, I miss all hits where there would be a hit for one parent only. Cumbersome tale, but perhaps there is someone who understands what I mean?

View 4 Replies View Related

Add Single Record To A Table Using Append Query With One To Many Relationship

Nov 21, 2014

I have tables Account (PK acctnum) and Orders (linked via acctnum to Account). There are multiple orders per account that need to be billed individually. When creating an invoice I need to add the Account to the Invoice table and the Orders to the InvoiceDetail table. I use 2 queries to accomplish this. The first one though adds multiple records to the invoice table (because of the one to many relationship). I need to keep that so I don't bill anyone with no orders.

INSERT INTO Invoices ( InvoiceDate, AcctNum )
SELECT DISTINCT Date() AS InvoiceDate, Account.AcctNum
FROM Account INNER JOIN [Order] ON Account.AcctNum = Order.AcctNum
WHERE (((Order.OrderNum)=[Forms]![Account]![Order].[Form]![OrderNum]) AND ((Account.BillingCycle)="on discharge") AND ((Order.EndDate) Is Not Null) AND ((Order.Closed)=Yes) AND ((Order.PatientName)=[Forms]![Account]![Order].[Form]![PatientName]));

Is there any way to get it to only add one record? I've tried limiting it with several parameters to no avail.

View 2 Replies View Related

Data Access Pages Using A Relational Database

Aug 11, 2005

Im creating a data access page overtop of a relational database. the database is simple... it has customers and workorders. there may be more than one workorder for a customer.

When I create a new customer record through my data access page, I want it to automaticly create a new workorder (filled with 0's)

Does anyone know how to do that?

Thanks
--Ben

PS: I understand SQL... I just need to know where to enter it (if necessary)

View 1 Replies View Related

Relational Structure To Flat Excel File?

Jul 17, 2007

Hope someone can help with this.

Basically we have an Access database with a main table, and several linked tables, joined on a unique ID.

For example :

tblFeatures :

FeatureID
FeatureName
etc

tblCategories
FeatureID
Category

So each feature can have many categories in a one-to-many relationship.

What I have a requirement to do is to pull that out as a flat file in Excel, ie

FeatureID, FeatureName, Category1, Category2
etc

What's the best way to go about this?

Cheers,
Iain

View 2 Replies View Related







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