Ok'
I have 2 tables.
The first being my main table where each record has a 'FigureID' txt
box. (Indexed - No duplicates)
The second being my sub table where reporting is entered for these
Figures. Sometimes more than one at a time. I have 4 'FigureID' txt
boxes on this subtable.(All Indexed - Duplicates OK)
When I set the Master/Child link for the first FigureID on the
Subtable I have no problems.
The subtable shows all the records with the same FigureID.
Then when I add the 2nd,3rd & 4th relationships, it doesnt work.
The subtable shows records that dont exist (autonumber = autonumber).
& the 2nd,3rd & 4th FigureID's are auto-populated as the FigureID from
the main table?
The first FigureID on the subtable is blank?
Can I not link 1 Master Field to multiple Child Fields???
Any help would be greatly appreciated'
Ryan pa****@gmail.co m
Feb 6 '07
13  8297 
And as your table gets bigger, this query will get slower and slower
because of the iterative process.
Whereas with one record per ID you will hardly see a difference.
pa****@gmail.co m wrote:
The Subtable does have a primary key for each record - <ReportID>
You lost me on the single foreign key though...
Each SubRecord (Report) needs to have multiple FigureID values because
the reports usually cover more than one figure.
Here is what is in the 'Subform/Report: page after the Subform Field
Linker
Link Child Fields....... FigID;FigID2;Fi gID3;FigID4;Fig ID5
Link Master FIelds..... FigureID;Figure ID;FigureID;Fig ureID;FigureID
<same repeated>
Therefore there are many master records for each child record.
Thanks'
On Feb 6, 9:27 am, "Rick Brandt" <rickbran...@ho tmail.comwrote:
>paq...@gmail.c om wrote:
>>Ok'
I have 2 tables.
The first being my main table where each record has a 'FigureID' txt
box. (Indexed - No duplicates)
The second being my sub table where reporting is entered for these
Figures. Sometimes more than one at a time. I have 4 'FigureID' txt
boxes on this subtable.(All Indexed - Duplicates OK)
This is where you went wrong. Your second table should have its own Primary
Key and then a single foreign keyfieldto relate back to the parent table.
Then each sub-record has exactly one FigureID value. With this setup you
can have zero, one, ormultiplechild records in the second table that
relate to a single record in the main table.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
You need each FigureID to be a separate record, you can't put 4 fields
in one record and try to set a relationship. Look up database
normalization, you tables are not normalized correctly.
Ron
>
Ron- Hide quoted text -
- Show quoted text -
Hi'
The only way for me to separate each report so that it only has 1
FigureID is to duplicate each report for every figureID it relates
to...
Is that not producing duplicate & redundant data, therefore un-
normalizing it?
Ryan
On Feb 8, 9:08 am, paq...@gmail.co m wrote:
Ron- Hide quoted text -
- Show quoted text -
Hi'
The only way for me to separate each report so that it only has 1
FigureID is to duplicate each report for every figureID it relates
to...
Is that not producing duplicate & redundant data, therefore un-
normalizing it?
Ryan
The basic idea of normalization is that Unique data is maintained only
once, is so far as possible.
Your table is as follows:
Subtable
ID - Autonumber
Property- text
FigureID- text
FigureID2 - text
FigureID3- text
FigureID4 - text
FigureID5- text
FigureID6 - text
Report Date - Date
Reportee - text
ReportText - text
ReportTextCont1 - text
ReportTextCont2 - text
ReportDiagram1 - OLE
ReportDiagram2 - OLE
ReportDiagram3 - OLE
If you are saying that in that table
Property and Report Date (and all the fields below it are identical
for Figure ID 1 and 2 and 3 etc (if they are there) and therefore
would be duplicated if you created a singel record for each ID then
Table 2 should be broken down into 2 tables
Property-Report Table
ID - Autonumber
Property- text
Report Date - Date
Reportee - text
ReportText - text
ReportTextCont1 - text
ReportTextCont2 - text
ReportDiagram1 - OLE
ReportDiagram2 - OLE
ReportDiagram3 - OLE
And a new table - one record for every unique combination of
FigureID and Property Report ID.
Figure-Report Table
ID - Autonumber
FigureID
Property-reportID
This allows you to query either way
Show all reports for any figureID (and not have to search in 6
different fields to find them)
or
Show All Figures that are in any Report And on this one you are not
limited to a max of 6.
Ron This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Yannick Turgeon |
last post by:
Hello,
We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.
I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not...
|
by: Terry |
last post by:
Hello,
I wonder if anyone can shed light on this problem for me. I have an
Access 97 front end with an SQL 2000 database. There is a Business main
form with an Owner subform and corresponding tables of the same names.
A third table BusinessRel records the BusinessID (linked to Business
table) and OwnerID (linked to Owner table).
This is what happens when the Business main form is loaded. I enter the
BusinessID and other stuff, then as...
|
by: Terry |
last post by:
Hello,
Has anyone experienced the following problem following an Upsize from
Access 97 to SQL 2000 using the MS Upsize Wizard? Or can anyone see
what the problem might be.
Before Upsize (Access 97 front end and Access 97 tables): A MainForm
loads up correctly and then I enter a ReferenceNumber value in a
MainForm field control. I then click on the first field of a SubForm,
the ReferenceNumber then shows at the top of the SubForm and...
|
by: Frav |
last post by:
The Reps team have been experiencing that Access 2002 unexpectedly
quits
while working and also lots of Corruption Failures and "Record lock
can not
update" messages since the upgrade from Windows 2000 and Access 200 to
Windows XP and Access 2002 (XP) was done.
Now, they are working and at anytime in anywhere of the application
access
unexpectedly quits with no error messages and the info that was being
entered
|
by: Don Do |
last post by:
Help
I built a form/subform/subsubform setup using the access forms
wizard. I have a table1 = parent, table2 = child, table3 =
(grandchild?). There will be multiple records in table2 that tie to
table1, and multiple records in table 3 that tie to table2. Both on
"1 to many" joins.
Each of the forms are bound to the fields in the respective tables.
The subform & subsubform are datasheet view. When I enter any data, I
| | |
by: wwwords |
last post by:
I have a form in Access 2007 containing a subform. The control source
of the latter is
SELECT DISTINCTROW ., ., ., .,
. FROM ;
which is undoubtedly correct.
However, if I click on the down arrow on the subform header (the one
that should bring up the sort and filter menu) I get this error:
|
by: wendy184 |
last post by:
I'm used to using 2007 which allows multiple values in the lookup wizard, this helps hugely with my queries as the database i'm building has information on one parent who may have up to 5 kids.
Now i'm building up a similar database in 2000 and could really use some help, 2000 won't allow multiple values in a lookup field which means if I have a parent who has more than one kid I cant store the details, I have a seperate table for both parent...
|
by: Yubin |
last post by:
Hello, I'm new to the forums and I wasn't able to find anything in my searches so here we are.
I have a DB with a many-2-many relationship.
I have a form that has 3 comboboxes: Project #, Last Name, Active(Yes/No)
I have a subform that uses Project# as a Child and Master link.
I want to use all three exclusively to give the user the option to search using any ONE of the three, but when I add all three to the Child and Master link fields no...
|
by: banderson |
last post by:
Hello all,
I am hoping that you can help me with a problem that has occurred numerous times on various forms in the two databases that I am working on. When I first add a subform to a form linking child and master fields (primary key and foreign key), the subform will automatically update when the master field on the main form is changed (it is in a combo box). However, when I work more on the form - making it a bit more complicated, adding...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
