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

[R Tanner]

Hi,

I have a table with around 350,000 rows. I have 3 lookup columns to
parent tables. I imported another 30,000 records of data to a new
table. It is the same type of data. I want to append it to my table
with 350,000 rows and 3 lookup columns. How would I do this? I think
part of my confusion might be with how exactly how the relationship
between my parent and child tables work. I mean, I understand that
the foreign key in my child table ties it to the parent table, so how
do I duplicate that and easily integrate my new data into the old
table?

Thanks

 

[KARL DEWEY]

First you need to check the new data to make sure all records match the
lookup information. If not then you will need to add to the lookup tables.
Next in your append query add the tables and join fields to the lookup table
fields. Append the lookup field rather than the actual data.
Example of lookup table --
Taste Code
Sweet 1
Sour 2
Bitter 3
For Taste in new record table you append Code from lookup table.

 

[R Tanner]

First you need to check the new data to make sure all records match the
lookup information.  If not then you will need to add to the lookup tables.
Next in your append query add the tables and join fields to the lookup table
fields. Append the lookup field rather than the actual data.  
Example of lookup table --
Taste  Code
Sweet 1
Sour 2
Bitter 3
For Taste in new record table you append Code from lookup table.
--
KARL DEWEY
Build a little - Test a little







- Show quoted text -

I'm not sure I quite understand. I have one orders table. Then I
have 3 parent tables called currencies, order types, and banks. I
have these four tables in my query view as well as my new data I need
to append...

At the bottom in the 'Field' row where I specify the query, should I
do a sum or something to get all the data into one table?

 

[R Tanner]

I'm not sure I quite understand.  I have one orders table.  Then I
have 3 parent tables called currencies, order types, and banks.  I
have these four tables in my query view as well as my new data I need
to append...

At the bottom in the 'Field' row where I specify the query, should I
do a sum or something to get all the data into one table?- Hide quoted text -

- Show quoted text -

Okay I tried to append all of the fields together with an append query
and it didn't work...I have about 400,000 rows of data. Is that too
much for an access database? It told me that I either do not have
enough temporary memory or the size of the file is larger than 2 gb...

 

[KARL DEWEY]

Then I have 3 parent tables called currencies, order types, and banks
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.
Usually not.
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.

 

[nouveauricheinvestments]

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.


Usually not.


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...

 

[R Tanner]

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...

 

[KARL DEWEY]

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

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...

 

[nouveauricheinvestments]

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

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...

Sample data is as follows:

Table: Orders

Order Contract Size Price Date Time Lookup To
Banks Lookup To Currencies Lookup To Order Types

835921 345 1.93056 06/25/2008 1:05:47 AM
BOO GBP/USD Sell
589631 679 300.56 09/25/2008 1:06:48 AM
AVM EUR/USD Buy

Table: Jan 2008

Order Order Type Contract Size Currency Price Bank Date
Time

44698 Buy 678 USD/ZAR 1.6789 TID 09/22/2008
1:07:09 AM
23985 Sell 1000 CHK/USD 235.987 ZAM 09/30/2008
1:09/22 AM

Table: Currencies

ID Currency

1 USD/FSK
2 USD/CHF

Table: Order Types

ID Order Types

1 Buy
2 Sell

Table: Banks

ID Bank

1 BOM
2 GIF

 

[nouveauricheinvestments]

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

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?

 

[KARL DEWEY]

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

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.

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?

 

[R Tanner]

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

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 anaccess 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, andpaste 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 haveto
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...

 

[R Tanner]

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.
--
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 howthe 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 -

Okay I changed it so the actual foreign ID is appearing in my Orders
table. I am trying to create a relationship between the Jan 2008
table and my parent tables (Order Types, Banks, and Currencies) but
I'm not sure how to do it...

 

[KARL DEWEY]

I believe I got the field names correct as you used differnt names at
differnt posting.
Try this --
INSERT INTO Orders1 ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 2008].Order, [Order Types].ID, [Jan 2008].Contract_Size,
Currencies.ID, [Jan 2008].Price, Banks.ID, [Jan 2008].Date, [Jan 2008].Time
FROM (([Jan 2008] INNER JOIN Currencies ON [Jan 2008].Currency =
Currencies.Currency) INNER JOIN Banks ON [Jan 2008].Bank = Banks.Bank) INNER
JOIN [Order Types] ON [Jan 2008].Order_Type = [Order Types].[Order Types];

--
KARL DEWEY
Build a little - Test a little

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.

:
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...

 

[KARL DEWEY]

Remove the suffix '1' from Orders table name.
--
KARL DEWEY
Build a little - Test a little

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.

:
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...

 

[R Tanner]

Remove the suffix '1' from Orders table name.
--
KARL DEWEY
Build a little - Test a little

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 youwill 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

 

[R Tanner]

Remove the suffix '1' from Orders table name.
--
KARL DEWEY
Build a little - Test a little

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 youwill 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 -

I really gotta learn SQL so I can do this...

 

[KARL DEWEY]

There will be a problem with field name. Verify the field name and correct
the error.
--
KARL DEWEY
Build a little - Test a little

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

:
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

 

[nouveauricheinvestments]

There will be a problem with field name. Verify the field name and correct
the error.
--
KARL DEWEY
Build a little - Test a little

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

 

[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

Unless [Order Types].ID refers to the ID in the Order Types table...I
was thinking that since it was part of the select statement it must
have to be a field in the Jan 2008 table...