Update data using form

N

NigelB

Hello, I have 2 tables Organization & Quantity (Org & Qty):
Org
OrgId (PK)
Orgname
OrgAddr
OrgCity
OrgState
OrgZip
....


Qty
OrgID (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

Relationship betwen Org.OrgId & Qty.OrgId. There are many quantities to each
organization.
One quantity per year per organization hence QtryYear is 2002, 2003 etc &
QtyAmount is total, QtyDate is date given & QtyLoc is location.

I have been building some forms. Typically one form I have is for data entry
for the 2005 year plus it displays the current information in the
organization table allowing the fields to be updated if necessary. So I
display the organization data, the 2004 QtyAmount (just for information) then
I would like to be able to enter the 2005 quantity data - QtyAmount, QtyDate
& QtyLoc.
Unfortunately it seems I show the 2004 QtyAmount but then when I enter
quantity data I overwrite 2004 rather than add 2005 ?
Thanks
 
A

Al Camp

Nigel,
Not sure if I understand what's happening or the cause, but...

I don't think you should use QtyYear as a PK. With multiple Orgs, you'll
have multiple Qty records with say.. 2005.
Just use a new unique record identifier in your Qty table... ex. [QtyID],
just to make each Qty record unique.

What you want is to just pull up a Org record on the form, and see all
the yearly transactions for that Org in your subform... and be able to add a
new record to the sub every year.
Qty table....
OrgID (PK) linked to OrgID table)
QtyID (PK) just for Qty uniqueness
Qty
QtyYear
QtyAmount
QtyDate
QtyLoc

hth
Al Camp

Link Org to Qty via the OrgID, and once a year you can add a new record.
 
N

NigelB

I was thinking that with OrgId from the Org table in a one to many
relationship with OrgId QtyYear in the quantity table there would be
uniqueness.
1 ... .... 1.2002
1.2003
1.2004
I display the QtyAmount based on a query of QtyYear > 2003 which gives me
the 2004 amount but I now want to add the 2005 data somehow. Thanks

Al Camp said:
Nigel,
Not sure if I understand what's happening or the cause, but...

I don't think you should use QtyYear as a PK. With multiple Orgs, you'll
have multiple Qty records with say.. 2005.
Just use a new unique record identifier in your Qty table... ex. [QtyID],
just to make each Qty record unique.

What you want is to just pull up a Org record on the form, and see all
the yearly transactions for that Org in your subform... and be able to add a
new record to the sub every year.
Qty table....
OrgID (PK) linked to OrgID table)
QtyID (PK) just for Qty uniqueness
Qty
QtyYear
QtyAmount
QtyDate
QtyLoc

hth
Al Camp

Link Org to Qty via the OrgID, and once a year you can add a new record.
NigelB said:
Hello, I have 2 tables Organization & Quantity (Org & Qty):
Org
OrgId (PK)
Orgname
OrgAddr
OrgCity
OrgState
OrgZip
...


Qty
OrgID (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

Relationship betwen Org.OrgId & Qty.OrgId. There are many quantities to
each
organization.
One quantity per year per organization hence QtryYear is 2002, 2003 etc &
QtyAmount is total, QtyDate is date given & QtyLoc is location.

I have been building some forms. Typically one form I have is for data
entry
for the 2005 year plus it displays the current information in the
organization table allowing the fields to be updated if necessary. So I
display the organization data, the 2004 QtyAmount (just for information)
then
I would like to be able to enter the 2005 quantity data - QtyAmount,
QtyDate
& QtyLoc.
Unfortunately it seems I show the 2004 QtyAmount but then when I enter
quantity data I overwrite 2004 rather than add 2005 ?
Thanks
Click to expand...
Click to expand...
 
A

Al Camp

The OrgID to OrgID is all you need for "uniqueness". The QtyID is just a
Qty transaction identifier. (more of a design issue, and not critical to
this problem)

While you're doing problem determination, why don't you remove the >2003
filtering, and let the sub records fall as they may.

In your subform, do you see a "new" record at the end of the recordset?

If not, then I'm assuming that you really editing the 2004 record rather
than addin a new 2005 record. Please explain a bit more...

Al Camp

NigelB said:
I was thinking that with OrgId from the Org table in a one to many
relationship with OrgId QtyYear in the quantity table there would be
uniqueness.
1 ... .... 1.2002
1.2003
1.2004
I display the QtyAmount based on a query of QtyYear > 2003 which gives me
the 2004 amount but I now want to add the 2005 data somehow. Thanks

Al Camp said:
Nigel,
Not sure if I understand what's happening or the cause, but...

I don't think you should use QtyYear as a PK. With multiple Orgs,
you'll
have multiple Qty records with say.. 2005.
Just use a new unique record identifier in your Qty table... ex.
[QtyID],
just to make each Qty record unique.

What you want is to just pull up a Org record on the form, and see all
the yearly transactions for that Org in your subform... and be able to
add a
new record to the sub every year.
Qty table....
OrgID (PK) linked to OrgID table)
QtyID (PK) just for Qty uniqueness
Qty
QtyYear
QtyAmount
QtyDate
QtyLoc

hth
Al Camp

Link Org to Qty via the OrgID, and once a year you can add a new
record.
NigelB said:
Hello, I have 2 tables Organization & Quantity (Org & Qty):
Org
OrgId (PK)
Orgname
OrgAddr
OrgCity
OrgState
OrgZip
...


Qty
OrgID (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

Relationship betwen Org.OrgId & Qty.OrgId. There are many quantities to
each
organization.
One quantity per year per organization hence QtryYear is 2002, 2003 etc
&
QtyAmount is total, QtyDate is date given & QtyLoc is location.

I have been building some forms. Typically one form I have is for data
entry
for the 2005 year plus it displays the current information in the
organization table allowing the fields to be updated if necessary. So I
display the organization data, the 2004 QtyAmount (just for
information)
then
I would like to be able to enter the 2005 quantity data - QtyAmount,
QtyDate
& QtyLoc.
Unfortunately it seems I show the 2004 QtyAmount but then when I enter
quantity data I overwrite 2004 rather than add 2005 ?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
N

NigelB

Hello. I removed the >2003. I think the mistake I was making was I was not
putting the quantity data out in a subform. I had it with the organization
data in one form. Anyway with a subform for the quantity data, I do see a new
record at the end. I tried entering data for 2005 & then it said "You cannot
add or change a record because a related record is required in table
'TblOrg'" I was trying to add the 2005 quantity entry for organization 1.

Al Camp said:
The OrgID to OrgID is all you need for "uniqueness". The QtyID is just a
Qty transaction identifier. (more of a design issue, and not critical to
this problem)

While you're doing problem determination, why don't you remove the >2003
filtering, and let the sub records fall as they may.

In your subform, do you see a "new" record at the end of the recordset?

If not, then I'm assuming that you really editing the 2004 record rather
than addin a new 2005 record. Please explain a bit more...

Al Camp

NigelB said:
I was thinking that with OrgId from the Org table in a one to many
relationship with OrgId QtyYear in the quantity table there would be
uniqueness.
1 ... .... 1.2002
1.2003
1.2004
I display the QtyAmount based on a query of QtyYear > 2003 which gives me
the 2004 amount but I now want to add the 2005 data somehow. Thanks

Al Camp said:
Nigel,
Not sure if I understand what's happening or the cause, but...

I don't think you should use QtyYear as a PK. With multiple Orgs,
you'll
have multiple Qty records with say.. 2005.
Just use a new unique record identifier in your Qty table... ex.
[QtyID],
just to make each Qty record unique.

What you want is to just pull up a Org record on the form, and see all
the yearly transactions for that Org in your subform... and be able to
add a
new record to the sub every year.
Qty table....
OrgID (PK) linked to OrgID table)
QtyID (PK) just for Qty uniqueness
Qty
QtyYear
QtyAmount
QtyDate
QtyLoc

hth
Al Camp

Link Org to Qty via the OrgID, and once a year you can add a new
record.
Hello, I have 2 tables Organization & Quantity (Org & Qty):
Org
OrgId (PK)
Orgname
OrgAddr
OrgCity
OrgState
OrgZip
...


Qty
OrgID (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

Relationship betwen Org.OrgId & Qty.OrgId. There are many quantities to
each
organization.
One quantity per year per organization hence QtryYear is 2002, 2003 etc
&
QtyAmount is total, QtyDate is date given & QtyLoc is location.

I have been building some forms. Typically one form I have is for data
entry
for the 2005 year plus it displays the current information in the
organization table allowing the fields to be updated if necessary. So I
display the organization data, the 2004 QtyAmount (just for
information)
then
I would like to be able to enter the 2005 quantity data - QtyAmount,
QtyDate
& QtyLoc.
Unfortunately it seems I show the 2004 QtyAmount but then when I enter
quantity data I overwrite 2004 rather than add 2005 ?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
N

NigelB

Hello again. I got around the error msg when adding the 2005 quantity data in
the subform by including OrgId from the quantity table in the form & entering
the appropriate OrgId.
However, I would rather the user not have to do that & have the OrgId pre
filled based on the record we are on at the time. Also be good to fill
QtyYear with the current year. In the quantity subform, do I have to show all
data for an organization, or is their a way to just show last years & allow
entry of this years data ? Thanks

NigelB said:
Hello. I removed the >2003. I think the mistake I was making was I was not
putting the quantity data out in a subform. I had it with the organization
data in one form. Anyway with a subform for the quantity data, I do see a new
record at the end. I tried entering data for 2005 & then it said "You cannot
add or change a record because a related record is required in table
'TblOrg'" I was trying to add the 2005 quantity entry for organization 1.

Al Camp said:
The OrgID to OrgID is all you need for "uniqueness". The QtyID is just a
Qty transaction identifier. (more of a design issue, and not critical to
this problem)

While you're doing problem determination, why don't you remove the >2003
filtering, and let the sub records fall as they may.
Unfortunately it seems I show the 2004 QtyAmount but then when I enter
quantity data I overwrite 2004 rather than add 2005 ?
Click to expand...

In your subform, do you see a "new" record at the end of the recordset?

If not, then I'm assuming that you really editing the 2004 record rather
than addin a new 2005 record. Please explain a bit more...

Al Camp

NigelB said:
I was thinking that with OrgId from the Org table in a one to many
relationship with OrgId QtyYear in the quantity table there would be
uniqueness.
1 ... .... 1.2002
1.2003
1.2004
I display the QtyAmount based on a query of QtyYear > 2003 which gives me
the 2004 amount but I now want to add the 2005 data somehow. Thanks

:

Nigel,
Not sure if I understand what's happening or the cause, but...

I don't think you should use QtyYear as a PK. With multiple Orgs,
you'll
have multiple Qty records with say.. 2005.
Just use a new unique record identifier in your Qty table... ex.
[QtyID],
just to make each Qty record unique.

What you want is to just pull up a Org record on the form, and see all
the yearly transactions for that Org in your subform... and be able to
add a
new record to the sub every year.
Qty table....
OrgID (PK) linked to OrgID table)
QtyID (PK) just for Qty uniqueness
Qty
QtyYear
QtyAmount
QtyDate
QtyLoc

hth
Al Camp

Link Org to Qty via the OrgID, and once a year you can add a new
record.
Hello, I have 2 tables Organization & Quantity (Org & Qty):
Org
OrgId (PK)
Orgname
OrgAddr
OrgCity
OrgState
OrgZip
...


Qty
OrgID (PK)
QtyYear (PK)
QtyAmount
QtyDate
QtyLoc

Relationship betwen Org.OrgId & Qty.OrgId. There are many quantities to
each
organization.
One quantity per year per organization hence QtryYear is 2002, 2003 etc
&
QtyAmount is total, QtyDate is date given & QtyLoc is location.

I have been building some forms. Typically one form I have is for data
entry
for the 2005 year plus it displays the current information in the
organization table allowing the fields to be updated if necessary. So I
display the organization data, the 2004 QtyAmount (just for
information)
then
I would like to be able to enter the 2005 quantity data - QtyAmount,
QtyDate
& QtyLoc.
Unfortunately it seems I show the 2004 QtyAmount but then when I enter
quantity data I overwrite 2004 rather than add 2005 ?
Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help building relationships 10

Top