Appending Data to a Table with 3 lookup Columns (Foreign Keys)

[nouveauricheinvestments]

There will be a problem with field name. Verify the field name and correct
the error.

On Oct 20, 2:36 pm, KARL DEWEY <[email protected]>
wrote:
Remove the suffix '1' from Orders table name.
--
KARL DEWEY
Build a little - Test a little
:
On Oct 20, 12:42 pm, KARL DEWEY <[email protected]>
wrote:
In you Orders table what are these field names -
Lookup To Banks Lookup To Currencies Lookup To Order Types
If you open the table what is the data stored in these field?
It should look something like this --
1 2 2
2 1 1
2 2 1
and not as you posted --
BOO GBP/USD Sell
AVM EUR/USD Buy
--
KARL DEWEY
Build a little - Test a little
:
On Oct 20, 9:29 am, KARL DEWEY <[email protected]>
wrote:
Your memory problem stems from your query is not relating tables --
FROM [Jan 2008], Currencies INNER JOIN (Banks INNER JOIN ([Order....
[Jan 2008] is not related and therefore give a Cartesian effect. That is
where all unrelated table record quanity is multiplited by all other table
records.
Post sample data from each table.
--
KARL DEWEY
Build a little - Test a little
:
On Oct 17, 9:24 pm, (e-mail address removed) wrote:
On Oct 17, 3:35 pm, KARL DEWEY <[email protected]>
wrote:
Then I have 3 parent tables called currencies, order types, and banks
I think part of my confusion might be with how exactly how the relationship between my parent and child tables work.
Let us clear some of the confusion.
If you open the Banks table it will probably look like this --
xxID Bank
1 Some National Bank of Somewhere
2 CitiCorp Bank
3 SomeOther Bank
If you open the Orders table and look at the Bank field you will see number
instead of names. The same for the other 'Parent' tables.
I have about 400,000 rows of data. Is that too much for an access database?
Usually not.
It told me that I either do not have enough temporary memory or the size of the file is larger than 2 gb...
Did you backup the database and then do Compact and Repair?
Post the SQL of the append query you tried to run. First open the query in
design view, click on VIEW - SQL View, highlight all, copy, and paste in a
post.
--
KARL DEWEY
Build a little - Test a little
I will post monday morning at 7:00 AM Mountain Time with the SQL
statement I am using. I do not have the data at home so I have to
wait until I am at work again...- Hide quoted text -
- Show quoted text -
Okay here you go...This is my SQL statement I am trying to use to
append the two database tables...
INSERT INTO Orders ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 2008].Order, [Jan 2008].Order_Type, [Jan
2008].Contract_Size, [Jan 2008].Currency, [Jan 2008].Price, [Jan
2008].Bank, [Jan 2008].Date, [Jan 2008].Time
FROM [Jan 2008], Currencies INNER JOIN (Banks INNER JOIN ([Order
Types] INNER JOIN Orders ON [Order Types].ID = Orders.[Order
Types_ID]) ON Banks.ID = Orders.Banks_ID) ON Currencies.ID =
Orders.Currencies_ID;
Access built that for me...
I have not defined any relationships with the new data I imported.
All I did was import it and then try to run an append query. Should I
define relationships between the new data and the parent tables and
then try to run an append query?- Hide quoted text -
- Show quoted text -
I can change it to a number field...Right now it is the actual value
but the data type is a number...When I use the table analyzer it
automatically uses the actual data in these lookup columns rather than
the forien key like you suggested...- Hide quoted text -
- Show quoted text -
It is telling me:
"Enter a parameter value" Order Types.Order Types

In the following syntax, why do you have [Order Types].ID? Wouldn't I
want to just use [Jan 2008].[Order Types]?

SELECT [Jan 2008].Order, [Order Types].ID, [Jan 2008].Contract_Size,
Currencies.ID, [Jan 2008].Price, Banks.ID, [Jan 2008].Date, [Jan
2008].Time

Okay it worked perfectly...I found the problem...I am really studying
your SQL statement and I think I understand it...Amazing...Thank you
Karl...I really appreciate it...

 

[KARL DEWEY]

Because you are using lookup fields the incomming data must be translated to
the numerical value representing the lookup field.
--
KARL DEWEY
Build a little - Test a little

There will be a problem with field name. Verify the field name and correct
the error.

:
On Oct 20, 2:36 pm, KARL DEWEY <[email protected]>
wrote:
Remove the suffix '1' from Orders table name.

:
On Oct 20, 12:42 pm, KARL DEWEY <[email protected]>
wrote:
In you Orders table what are these field names -
Lookup To Banks Lookup To Currencies Lookup To Order Types

If you open the table what is the data stored in these field?
It should look something like this --
1 2 2
2 1 1
2 2 1
and not as you posted --
BOO GBP/USD Sell
AVM EUR/USD Buy

:
On Oct 20, 9:29 am, KARL DEWEY <[email protected]>
wrote:
Your memory problem stems from your query is not relating tables --
FROM [Jan 2008], Currencies INNER JOIN (Banks INNER JOIN ([Order....

[Jan 2008] is not related and therefore give a Cartesian effect. That is
where all unrelated table record quanity is multiplited by all other table
records.

Post sample data from each table.

:
On Oct 17, 9:24 pm, (e-mail address removed) wrote:
On Oct 17, 3:35 pm, KARL DEWEY <[email protected]>
wrote:

Then I have 3 parent tables called currencies, order types, and banks
I think part of my confusion might be with how exactly how the relationship between my parent and child tables work.

Let us clear some of the confusion.
If you open the Banks table it will probably look like this --
xxID Bank
1 Some National Bank of Somewhere
2 CitiCorp Bank
3 SomeOther Bank

If you open the Orders table and look at the Bank field you will see number
instead of names. The same for the other 'Parent' tables.

I have about 400,000 rows of data. Is that too much for an access database?

Usually not.

It told me that I either do not have enough temporary memory or the size of the file is larger than 2 gb...

Did you backup the database and then do Compact and Repair?

Post the SQL of the append query you tried to run. First open the query in
design view, click on VIEW - SQL View, highlight all, copy, and paste in a
post.

I will post monday morning at 7:00 AM Mountain Time with the SQL
statement I am using. I do not have the data at home so I have to
wait until I am at work again...- Hide quoted text -

- Show quoted text -

Okay here you go...This is my SQL statement I am trying to use to
append the two database tables...

INSERT INTO Orders ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 2008].Order, [Jan 2008].Order_Type, [Jan
2008].Contract_Size, [Jan 2008].Currency, [Jan 2008].Price, [Jan
2008].Bank, [Jan 2008].Date, [Jan 2008].Time
FROM [Jan 2008], Currencies INNER JOIN (Banks INNER JOIN ([Order
Types] INNER JOIN Orders ON [Order Types].ID = Orders.[Order
Types_ID]) ON Banks.ID = Orders.Banks_ID) ON Currencies.ID =
Orders.Currencies_ID;

Access built that for me...

I have not defined any relationships with the new data I imported.
All I did was import it and then try to run an append query. Should I
define relationships between the new data and the parent tables and
then try to run an append query?- Hide quoted text -

- Show quoted text -

I can change it to a number field...Right now it is the actual value
but the data type is a number...When I use the table analyzer it
automatically uses the actual data in these lookup columns rather than
the forien key like you suggested...- Hide quoted text -

- Show quoted text -

It is telling me:

"Enter a parameter value" Order Types.Order Types

In the following syntax, why do you have [Order Types].ID? Wouldn't I
want to just use [Jan 2008].[Order Types]?

SELECT [Jan 2008].Order, [Order Types].ID, [Jan 2008].Contract_Size,
Currencies.ID, [Jan 2008].Price, Banks.ID, [Jan 2008].Date, [Jan
2008].Time

Okay it worked perfectly...I found the problem...I am really studying
your SQL statement and I think I understand it...Amazing...Thank you
Karl...I really appreciate it...

 

[R Tanner]

Because you are using lookup fields the incomming data must be translatedto
the numerical value representing the lookup field.
--
KARL DEWEY
Build a little - Test a little

On Oct 20, 3:48 pm, KARL DEWEY <[email protected]>
wrote:
There will be a problem with field name.  Verify the field name and correct
the error.
--
KARL DEWEY
Build a little - Test a little
:
On Oct 20, 2:36 pm, KARL DEWEY <[email protected]>
wrote:
Remove the suffix '1' from Orders table name.
--
KARL DEWEY
Build a little - Test a little
:
On Oct 20, 12:42 pm, KARL DEWEY <[email protected]>
wrote:
In you Orders table what are these field names -
 Lookup To Banks  Lookup To Currencies Lookup To Order Types
If you open the table what is the data stored in these field?
It should look something like this --
1  2   2
2   1  1
2   2  1
    and not as you posted --
BOO              GBP/USD              Sell
AVM              EUR/USD              Buy
--
KARL DEWEY
Build a little - Test a little
:
On Oct 20, 9:29 am, KARL DEWEY <[email protected]>
wrote:
Your memory problem stems from your query is not relating tables --
FROM [Jan 2008], Currencies INNER JOIN (Banks INNER JOIN ([Order....
[Jan 2008] is not related and therefore give a Cartesian effect.  That is
where all unrelated table record quanity is multiplitedby all other table
records.
Post sample data from each table.
--
KARL DEWEY
Build a little - Test a little
:
On Oct 17, 9:24 pm, (e-mail address removed):
On Oct 17, 3:35 pm, KARL DEWEY <[email protected]>
wrote:
Then I have 3 parent tables called currencies, order types, and banks
I think part of my confusion might be with howexactly how the relationship between my parent and child tables work.
Let us clear some of the confusion.
If you open the Banks table it will probably looklike this --
xxID   Bank
1       Some National Bank of Somewhere
2       CitiCorp Bank
3       SomeOther Bank
If you open the Orders table and look at the Bankfield you will see number
instead of names.   The same for the other 'Parent' tables.
I have about 400,000 rows of data.  Is that too much for an access database?
Usually not.
It told me that I either do not have enough temporary memory or the size of the file is larger than 2 gb...
Did you backup the database and then do Compact and Repair?
Post the SQL of the append query you tried to run..   First open the query in
design view, click on VIEW - SQL View, highlight all, copy, and paste in a
post.
--
KARL DEWEY
Build a little - Test a little
I will post monday morning at 7:00 AM Mountain Timewith the SQL
statement I am using.  I do not have the data at home so I have to
wait until I am at work again...- Hide quoted text -
- Show quoted text -
Okay here you go...This is my SQL statement I am trying to use to
append the two database tables...
INSERT INTO Orders ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 2008].Order, [Jan 2008].Order_Type, [Jan
2008].Contract_Size, [Jan 2008].Currency, [Jan 2008].Price, [Jan
2008].Bank, [Jan 2008].Date, [Jan 2008].Time
FROM [Jan 2008], Currencies INNER JOIN (Banks INNER JOIN ([Order
Types] INNER JOIN Orders ON [Order Types].ID = Orders.[Order
Types_ID]) ON Banks.ID = Orders.Banks_ID) ON Currencies.ID =
Orders.Currencies_ID;
Access built that for me...
I have not defined any relationships with the new data I imported.
All I did was import it and then try to run an append query.  Should I
define relationships between the new data and the parent tables and
then try to run an append query?- Hide quoted text -
- Show quoted text -
I can change it to a number field...Right now it is the actual value
but the data type is a number...When I use the table analyzerit
automatically uses the actual data in these lookup columns rather than
the forien key like you suggested...- Hide quoted text -
- Show quoted text -
It is telling me:
"Enter a parameter value" Order Types.Order Types
In the following syntax, why do you have [Order Types].ID?  Wouldn't I
want to just use [Jan 2008].[Order Types]?
 SELECT [Jan 2008].Order, [Order Types].ID, [Jan 2008].Contract_Size,
Currencies.ID, [Jan 2008].Price, Banks.ID, [Jan 2008].Date, [Jan
2008].Time

Okay it worked perfectly...I found the problem...I am really studying
your SQL statement and I think I understand it...Amazing...Thank you
Karl...I really appreciate it...- Hide quoted text -

- Show quoted text -

Hey Karl hopefully you will check out this thread at least once more
and be able to help me understand this problem I am having with a
different database as well...

I have a table with about 17 fields of unnormalized data I imported
from about 30 different excel spreadsheets. I want to split it up
into about 8 different tables and then split the database into a front/
back end for further data entry. I tried using the table analyzer but
it didn't work, so I am trying to manually separate the table and then
create the one to many relationships with the foreign ID in the Child
table. (In the end, I should have about 6-7 parent tables and one
child table with 6-7 foreign ID's).

So, focusing on one field. I ran a totals query to extract this data
from my [soon to be child] table, then ran a make table query, and
then I added a field to my child table to contain my foreign ID for
this parent table. I then related this newly added foreign ID field
to my parent table. So there is a relationship. I just can't figure
out how to populate this field for all of my records with the foreign
ID key...Any suggestions? Or am I going about this completely wrong?