1. Rename your existing Quantity table as QuantityOld. Rename the fields to
remove the spaces, since this is a littel more transparent (and this is how
my query below is structured; e.g. QtyDate, not Qty Date).
2. Manually create your new Quantity table, using these fields and data
types (you may need to adjust the data types to match the data to be moved
from QuantityOld - for example, if 2002Qty & 2003Qty are Double instead of
Integer, make the QtyAmount field Integer).
OrgID: Number, Long Integer, no default (and create a relationship between
this field and Organization.OrgID & enforce referential integrity to ensure
that only valid OrgID's are entered in the Quantity table)
QtyYear: Number, Integer, no default value
QtyAmount: Number, <same type as 2002 Qty, 2003 Qty, etc.>
QtyDate: Date/Time, Short Date (I assumed that this actually refers to a
Date and not the day of the week, etc.)
QtyLoc: Text
Make OrgID/QtyYear the multi-key (in table design view, select both and
click the Primary Key button)
3. Create a new query but cancel the table selector dialogue. Switch to SQL
view (View -> SQL View), and paste in the SQL statement below. You can then
switch to Design view to look at the structure. It takes the 2002 entries
from QuantityOld and creates entries in Quantity, copying the QtyID to OrgID
and inserting QtyYear2002. After you run it, just open it in design view
again and change all the 2002 references to 2003. Run it again, then do the
same for 2004. Once this is all done to your satisfaction, you can remove the
QuantityOld table. This will do all Org's at once for each year, so you will
only need to run it once for each year.
Again, I removed all the spaces from the field names.
INSERT INTO Quantity ( OrgID, QtyAmount, QtyDate, QtyLoc, QtyYear )
SELECT QuantityOld.QtyID, QuantityOld.[2002Qty], QuantityOld.[2002Date],
QuantityOld.[2002Loc], 2002 AS QtyYear
FROM QuantityOld;
NigelB said:
Thanks for helping. You understand perfectly. I have existing data in my
quantity table that I would like to try & use rather than retype. My quantity
table has:
QtyId
2002 Qty
2002 Date
2002 Loc
2003 Qty
2003 Date
2003 Loc
2004 Qty
2004 Date
2004 Loc
New quantity table:
Org Id
Qty Year
Qty Amount
Qty Day
Qty Loc
My existing Qty Id matches exactly the Org Id - hence how I relate quantity
to organization. So somehow I need to read the old quantity table & create
the new quantity table.
So for Org 1:
Put "1" or copy Qty Id to Org Id
Put "2002" in Qty Year
Copy 2002 Qty to Qty Amount
Copy 2002 Date to Qty Day
Copy 2002 Loc to Qty Loc
then repeat for 2003 & 2004. Then move to Org 2 & do the same.
Note both Org Id & Qty Id are 1 - 514 with no 293 row. Note also if it
matters, both Date & Loc (e.g. 2002 Date & 2002 Loc) are text blank at
present to be filled in later.
I think that covers it. You are a big help. Many thanks.
:
First of all, let me make sure I understand correctly, since I have been
steering you down a path here. Each organization will need a Quantity entry
for each Year, and it could be different each year, and it could be different
from the Quantity entry for another organization for the same year. If this
is not correct, correct my understanding and ignore the rest of this post. If
it is correct, continue to read.
You don't really "copy" the OrgID. Simply create the field in the Quantity
table. If you have existing data (and this begs the question of how you
identified which quantity/year combination went with which organization), you
will need to enter the OrgID for the correct organization before you make it
part of the primary key, since a primary key cannot be blank. If you are
starting with an empty table, this is not an issue. Make it and QtyYear
together the primary key. OrgID will show up multiple times - once for each
QtyYear. Here is one way.
Use a continuous form whose RecordSource is the Quantity table. Have a combo
box called OrgID on that form. Make its ControlSource OrgID, and make its
RowSource the OrgID & OrgName fields from the Organization table with OrgID
as the first column. Set the bound column to 1 and set column widths to 0;2.
That way, the combo box will hold the OrgID but will display OrgName to the
user.
In addition, have text boxes for QtyYear and QtyAmount (put all three boxes
on one line, with OrgID at the left, then QtyYear, then QtyAmount) where the
user can enter the year and amount. Once the user goes to a new record (blank
line at the bottom of the continuous form), he can pick the organization in
the first box, then enter the year & amount. When the user goes to a new
record by pressing Tab after the amount, the record will be saved. If there
is already an entry having that OrgID and Year, it will generate an error
indicating such (assuming you used OrgID and Year together as the primary
key).
I hope I am not going around in circles too much here.
:
Thanks so much, this is a great help. How do I copy the Org Id from Org. to
the quantity table ? I thought I could figure it out but it seems I can't ...
I need to learn more. Thanks again.
:
OrgID is still the primary key of the Organization table and is still
necessary in the Quantity table to establish the relationship between the
two. However, the combination of OrgID and QtyYear is the primary key for the
Quantity table, not OrgID by itself. This way, the table will allow only only
a single OrgID/QtyYear combination; that is, each organization will have only
one entry for each year.
:
Ok I think I have how to move the data from Contact to Org.
You said "If you want only one entry per OrgID per year, then make the OrgID
& QtyYear together the primary key." So would this be a second primary key
(Org Id in the Org table is primary now) or would this be the new PK, so Org
Id (in Org table) is no longer PK ? Thanks
:
Thankyou for your assistance, its a big help.
How do I move the contact information into the org. table ?
I remember once using a query to do a similar thing but now I can't seem to
make it happen. Thanks
:
If you have a one-to-one (i.e if each organizaton has only one Contact), you
could just include the contact information as additional fields in the
Organization table.
You will need to add OrgID as Long Integer to your Quantities field and set
its relationship to Organization.OrgID. Otherwise, you have no link between
the quantity and the organization.
A better structure for Quantity table is this:
OrgID (relationship to Organization.OrgID)
QtyAmount (Currency?/Double?/Integer?)
QtyYear (Integer)
This way, the 2002, 2003, and 2004 entries become records, not fields, and
you do not need to add new fields each subsequent year. If you want only one
entry per OrgID per year, then make the OrgID & QtyYear together the primary
key. If you will have multiple entries per year for ech organization, then
you could add a separate QtyID autonumber as the primary key.
:
Hello, I have 3 tables Organization, Contact & Quantity (Org,Con & Qty):
Org
OrgId (PK) Autonumber
Orgname
OrgAddr
OrgCity
OrgState
OrgZip
Con
ConId Number dont know if i need it ?
ConLast
ConFirst
ConEmail
Qty
QtyId Number dont know if i need it ?
2002 Qty
2002 Date
2003 Qty
2003 Date
2004 Qty
2004 Date
& so on
There is one contact for one organization & there are multiple quantities
(one for each year) for each contact/organization. So it seems I have a one
to one & a one to many ? But what would be my relationship fields ? Does
ConId need to change to AutoNumber & be a PK as well ? If I need to copy a
field into another table please help by explaining how as I'm fairly new.
Thanks
