Form based on Query

  • Thread starter Thread starter Marco Simone
  • Start date Start date
M

Marco Simone

I have made Form for entering data based on Query. Query is based on 3
tables in relationships.
I can see data on form, but why I can't change data in text boxes.
Your help is appreciated.

Thanks, Marco
 
Chances are if you open the query directly, you can't update the data there,
either -
you've created a non-updatable query.

There are lots of things that can make a query non-updatable -
basically anything that makes it ambiguous exactly what data is to be
updated.

Without seeing the SQL for the query, it's hard to offer more detail...

- Turtle
 
Thanks,

This is query:

SELECT tblA.*, tblB.*
FROM (tblCompanies INNER JOIN tblA ON tblCompanies.CompanyID =
tblA.CompanyID) INNER JOIN tblB ON tblCompanies.CompanyID = tblB.CompanyID;

tblCompanies and tblA are in relationship 'one to many'
tblCompanies and tblB are in relationship 'one to many'

I have tables: tblCompanies, tblA and tblB.
Data in tblA and tblB should be in one table, but since there is over 400
fields I have to split it on 2 tables (tblA and tblB).
This fields contains number fields, and I have to make calculations based on
fields.
Fields are of same entity, they are just number fields.

Should I put tblA and tbl B in relationship 'One to one', as they could be
in one table?
I don't have experience, so your help is really needed.

Thanks, Marco
 
If you have over 400 fields, chances are quite strong that there is a better
way to divide them than simply putting them into 2 tables. I'd strongly
suggest that you look at possibilities for normalization. The Table
Analyzer in Access is one possible place to start.

That said - consider tables like this:

tblCompanies - 2 records
CompanyID="ABC"
CompanyID="XYZ"

tblA - 4 records
CompanyID="ABC", FieldA=1
CompanyID="ABC", FieldA=2
CompanyID="XYZ", FieldA=10
CompanyID="ABC", FieldA=20

tblB - 4 records
CompanyID="ABC", FieldB=3
CompanyID="ABC", FieldB=4
CompanyID="XYZ", FieldB=30
CompanyID="ABC", FieldB=40

Your query will return these 8 records:
FieldA=1 FieldB=3
FieldA=1 FieldB=4
FieldA=2 FieldB=3
FieldA=2 FieldB=4
FieldA=10 FieldB=30
FieldA=10 FieldB=40
FieldA=20 FieldB=30
FieldA=20 FieldB=40

Is this even what you want?
- Turtle
 
Thanks,

tblA and tblB contains numeric data on certain date. It can be for example
15.01. or 30.06. or end of year. I could split tables into 4 tables or even
more. What is important is period (date). Every row in tables should contain
numeric data for one period (date). Another row in tables is numeric data
for another date. Than when I enter data in tables, I want to do
calculations for example:
1. (value of field1 in tblA)/(value of field55 in tblA) on same date (row)
2. (value of field10 in tblA)-(value of field20 in tblB) on same date
(row)
3. OR different date (value of field 15 in tblA) on 15.01.2003 / (value of
field 15 in tblA) on 15.01.2002
I don't how to relate tblA and tblB (or more tables if needed for
normalisation). And the data will be connected. I don't want that data of
different periods come into same row of tables.

It would be easiest for me to enter data in one table, but table limit is
255 fields.

Thanks for your help, Marco
 
It looks to me as if you need at least a one-to-one relationship between
tblA and tblB, using both the CompanyID and DataDate fields.
You should also include both fields in the join of your query.
Ideally, you would always create a record in one of these tables first, then
in the second table.
That way you could enforce referential integrity, with a cascade update.

I still suspect, though, that you could normalize your data further, and
then work with it in smaller groups...

HTH
- Turtle
 
Thanks,
It looks to me as if you need at least a one-to-one relationship between
tblA and tblB, using both the CompanyID and DataDate fields.
You should also include both fields in the join of your query.
Click to expand...

I cant make relationship between tblA and tblB. I got message when tried to
include Enforce referential integrity : "Invalid field definition in tblA in
definition of index or relationship."

I have tables with fields:
tblCompany
CompanyID (primary key autonumber)
CompanyName, Address, ...

tblA
ADataID (primary key autonumber)
CompanyID
DateA
Field1, Field2, ...

tblB
BDataID (primary key autonumber)
CompanyID
DateB
Field100, Field101, ,,,

Relationships are:
tblCompany and tblA (one to many, CompanyID is link)
tblCompany and tblB (one to many, CompanyID is link)
tblA and tblB ONE TO ONE, but I dont know what fields to link between
these tables.
I have tried to include primary key fields from tblA and tblB in
relationship, but cant enforce referential integrity.
I can link tblA and tblB without enforce referential integrity (whether I
put only primary keys from tblA or tblB or include CompanyID and field DateA
or DateB).

Do you know where is mistake?

Thanks for your time, Marco
 
I don't think you can enforce referential integrity between two
autonumbers -
certainly not if you're using cascading updates.
This would imply that a change to one causes a change to the other -
but autonumbers can't be changed...

IF you're OK with enforcing that data goes into tblA first, then tblB, you
can make tblA's primary key an autonumber, and tblB's a Long Integer. Then
define referential integrity with cascade updates.
That will define a unique relationship between the tables, so you won't need
the other fields in the primary key.

Of course, if the two tables are at all out of sync now, it will take a
certain amount of fancy footwork to get that resolved before you can
establish your relationship.

HTH
- Turtle
 
Thanks again for your help and time,

I was thinking that tblA and tblB have other primary key instead of
autonumber (I can get enforce referential integrity with this):
These are now tables:
tblCompany
CompanyID (primary key autonumber)
CompanyName, Address, ...

tblA
CompanyID => primary key are both combination of these fields (CompanyID
is from tblCompany and Date)
Date => same name in tblA, tblB
Field1, Field2, ...

tblB
CompanyID => primary key are both combination of these fields (CompanyID is
from tblCompany and Date)
Date =>
Field100, Field101, ,,,

Now I dont have autonumber primary key in tblA, tblB and can make
relationship 'One to one' with enforce referential integrity with cascade
update and cascade delete.

What you think of this relationship? I am newbee. I could maybe pass
CompanyID and Date to tblA and tblB, so that user dont enter and make
mistake.

Thanks, Marco
 
Now I dont have autonumber primary key in tblA, tblB and can make
relationship 'One to one' with enforce referential integrity with cascade
update and cascade delete.

What you think of this relationship? I am newbee. I could maybe pass
CompanyID and Date to tblA and tblB, so that user dont enter and make
mistake.

Thanks, Marco
Click to expand...



Sounds OK to me -
why don't you give it a try and see how it works?

- Turtle
 
Back
Top