Ok...I'll first describe briefly what I've done.
Working from a paper form with over 200 fields - broken into 10 areas, I
created a database with 10 tables. I then created a form using those 10
tables, created queries and then reports based on those queries. All works
well until.... you complete a form and DO NOT fill one or more sections (in
other words not filling in an underlying table, then everything falls apart.
Example tables:
Table Name: Names
Primary Key: NameID - autonumber
Table Name: Products
Primary Key: ProductID - autonumber
TableName: Suppliers
Primary Key: SupplierID - autonumber
The relationships are set as being one-to-one and the join type is set to
"1".
So...to be more clear..what is happening...let 's say I add a new record,
fill in data for the names section and the products section, BUT NOT the
suppliers section. I look at the tables later and see that there is a new
record (with autonumber) for each the name and the product...but nothing for
the supplier. When I go to query the database I have problems because only
two of the tables are relating (via autonumber). Of course when I add new
records and again leave a section blank..it only compounds the problem.
Let's say after trying a few records I again look at the underlying table.
I see table:Names has 4 records while table: Products has two records and
say TableSuppliers has 3 records. As I said...none of the reports work..and
even when I look at my form..nothing matches up. However, if I go back to
the tables...see that the table with the most records is the products table
with 4 records, I can then go to the other tables and manually add records
until all tables have 4 records. Then the reports and forms work again.
I'm seeing this meaning that this database somehow isn't relating properly
or the join is wrong? I always get confused at this area. Is there a way
to fix this easily? Or...is there a way to force the db to automatically
add a record when a form is open, even if no data is entered?
I appreciate any help you can provide.
Abe
3  1466 
Looks to me that you should make at least 1 field in each table
required, that way you can't leave the form without have a new record
in each table.
shawnews wrote:
Ok...I'll first describe briefly what I've done.
Working from a paper form with over 200 fields - broken into 10 areas, I
created a database with 10 tables. I then created a form using those 10
tables, created queries and then reports based on those queries. All works
well until.... you complete a form and DO NOT fill one or more sections (in
other words not filling in an underlying table, then everything falls apart.
Example tables:
Table Name: Names
Primary Key: NameID - autonumber
Table Name: Products
Primary Key: ProductID - autonumber
TableName: Suppliers
Primary Key: SupplierID - autonumber
The relationships are set as being one-to-one and the join type is set to
"1".
So...to be more clear..what is happening...let 's say I add a new record,
fill in data for the names section and the products section, BUT NOT the
suppliers section. I look at the tables later and see that there is a new
record (with autonumber) for each the name and the product...but nothing for
the supplier. When I go to query the database I have problems because only
two of the tables are relating (via autonumber). Of course when I add new
records and again leave a section blank..it only compounds the problem.
Let's say after trying a few records I again look at the underlying table.
I see table:Names has 4 records while table: Products has two records and
say TableSuppliers has 3 records. As I said...none of the reports work..and
even when I look at my form..nothing matches up. However, if I go back to
the tables...see that the table with the most records is the products table
with 4 records, I can then go to the other tables and manually add records
until all tables have 4 records. Then the reports and forms work again.
I'm seeing this meaning that this database somehow isn't relating properly
or the join is wrong? I always get confused at this area. Is there a way
to fix this easily? Or...is there a way to force the db to automatically
add a record when a form is open, even if no data is entered?
I appreciate any help you can provide.
Abe
On 16 Nov 2006 13:00:07 -0800, "Cyberwolf" <cy**********@g mail.com>
wrote:
Wouldn't that depend on the Business Rules? Can't make a field
required if it isn't.
-Tom.
>Looks to me that you should make at least 1 field in each table
required, that way you can't leave the form without have a new record
in each table.
<clip>
On Thu, 16 Nov 2006 19:25:04 GMT, "shawnews" <sa***@kalendar s.ca>
wrote:
Join problem.
When you have two tables with a relationship between them (let's call
them Parent and Child), and it is allowed for a Parent to have 0 or 1
Child records, then you'll have to use Outer Joins when creating a
query with these two tables. Create the query, right-click on the
relationship line, and choose "Select All from Parent and ..."
-Tom.
>Ok...I'll first describe briefly what I've done.
Working from a paper form with over 200 fields - broken into 10 areas, I
created a database with 10 tables. I then created a form using those 10
tables, created queries and then reports based on those queries. All works
well until.... you complete a form and DO NOT fill one or more sections (in
other words not filling in an underlying table, then everything falls apart.
Example tables:
Table Name: Names
Primary Key: NameID - autonumber
Table Name: Products
Primary Key: ProductID - autonumber
TableName: Suppliers
Primary Key: SupplierID - autonumber
The relationships are set as being one-to-one and the join type is set to
"1".
So...to be more clear..what is happening...let 's say I add a new record,
fill in data for the names section and the products section, BUT NOT the
suppliers section. I look at the tables later and see that there is a new
record (with autonumber) for each the name and the product...but nothing for
the supplier. When I go to query the database I have problems because only
two of the tables are relating (via autonumber). Of course when I add new
records and again leave a section blank..it only compounds the problem.
Let's say after trying a few records I again look at the underlying table.
I see table:Names has 4 records while table: Products has two records and
say TableSuppliers has 3 records. As I said...none of the reports work..and
even when I look at my form..nothing matches up. However, if I go back to
the tables...see that the table with the most records is the products table
with 4 records, I can then go to the other tables and manually add records
until all tables have 4 records. Then the reports and forms work again.
I'm seeing this meaning that this database somehow isn't relating properly
or the join is wrong? I always get confused at this area. Is there a way
to fix this easily? Or...is there a way to force the db to automatically
add a record when a form is open, even if no data is entered?
I appreciate any help you can provide.
Abe
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Rowan |
last post by:
Hello,
I am having a problem with a join. Either I am just not seeing the
obvious, it isn't possible, or I need to use a different approach. I
have an application with a vsflexgrid that needs to display the
following:
filenumber, BL, Container_BL, BL_HBL, HBL, Container_HBL
The tables look like:
|
by: Jack Smith |
last post by:
Hello,
I want to be able to view data from 3 tables using the JOIN statement, but
I'm not sure of how to do it. I think i don't know the syntax of the joins.I
imagine this is easy for the experienced - but Im not.
Allow me to explain:
I have 2 Tables: PERSON and SIGN
PERSON
|
by: Arnold |
last post by:
Hi there,
I have a form to organize bottles in mind, but am unsure if it will
work. Here's some background info:
Mainform = frmProduct, which contains fields for pricing, status, etc.
of complete bottles, as well as an image control for a thumbnail of a
complete product (bottle + cap).
The mainform has 2 subforms that must be visible and side-by-side:
|
by: Aaron |
last post by:
Hello,
I hope sombody would be kind enough to help me with this problem
I have 3 tables,
-Customer Details
-Customer Invoices
-Invoice Items
|
by: Jim |
last post by:
I have a form which is based on a join query. The join relationship is one
to many. When a new record is added and the user enters a value for one of
the fields in the "many" records, I attempt to lookup (using DLOOKUP) the
parent record and set the join field in the subordinate record to the key
for the parent record. However, when I do this I receive the error "3341 -
The current field must match the join key <field> in the table that...
| | |
by: Steve |
last post by:
One of our clients recently upgraded their Office version to 2003. When
they tried to run our program (written in Access 2000), they ended up
with the wrong data. My coworker and I have tested this problem and
found that it ONLY occurs on Windows XP machines running Office 2003.
The problem ends up being that ONE 1-to-1 join in ONE query within the
application does not perform as a 1-to-1 join. It acts as though there
is no join at all....
|
by: Shanimal |
last post by:
I would like to know how to join 2 queries so that the results of these
2 queries show up in the same query:
SELECT b.bios_serial_number FROM bios b:
SELECT s.system_name FROM system s;
Basically I want to create a report that includes both system name and
serial number. I'm new to this and none of the JOIn documentation was
clear to me.
|
by: polycom |
last post by:
Assistance needed to optimize this query
SELECT SD.content_id AS Id,
SD.title AS Title,
CT.name AS Contenttype,
PV.content_id AS SponsorId,
PV.display_name AS Sponsor,
CONCAT_WS("", NT.title, " - ", N.pubdate, " ") AS ListGroup,
|
by: BD |
last post by:
Hi, all.
I need to enforce a one-to-many relationship on 2 tables, with a join
table.
Say the join table contains account information. It has cust_no and
acct_no. Both cust_no and acct_no are child FKs to other tables (no
nulls will exist in these columns).
Logically, one customer can have several accounts, but one account can
|
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: 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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
