need help please:joined tables,add new entries based on one table columns 
Author Message
 need help please:joined tables,add new entries based on one table columns

Hi,
I have two tables: Departments and Sells
the following is the structure and the data in the tables

Departments:

Store_Id           Department_Id , Department_Name
        1                      1                      A1
        1                      2                      B1
        1                      3                      C1
        2                      1                      A2
        2                      2                      B2
        2                      3                      C2

Sells:(This table is initially empty )

Date,Store_Id,Department_Id,Total_Sells

i need to let user enter the total sells for ALL departments in each store
at the end of the day

The Sells table should look like this after user enter data for store "1"

Date,Store_Id,Department_Id,Total_Sells

29/7       1                1                 100
29/7       1                2                 200
29/7       1                3                 300

what is the best way to achieve this?
is there away to join the table together and get the store_id and the
deprtment_id columns values
for the empty sells table?
what i am doing now is opening two recordstes
one contains the Departments table
and  the other the Sells table
than i am adding new records to the Sells table with the values from the
Departments table
(store_id and the deprtment_id columns)

i am using ADO,ACCESS and VB

Thanks in advance

Tomer



Fri, 14 Jan 2005 16:03:34 GMT  
 need help please:joined tables,add new entries based on one table columns
You'll make your life easier if you normalize your database. Give each
department a unique ID (you could use the Department_Name field, but a
numeric ID is better); then have a single foreign key field in the Sells
table that points to the department..

Then you can create a single, updatable query on the two tables.


Quote:
> Hi,
> I have two tables: Departments and Sells
> the following is the structure and the data in the tables

> Departments:

> Store_Id           Department_Id , Department_Name
>         1                      1                      A1
>         1                      2                      B1
>         1                      3                      C1
>         2                      1                      A2
>         2                      2                      B2
>         2                      3                      C2

> Sells:(This table is initially empty )

> Date,Store_Id,Department_Id,Total_Sells

> i need to let user enter the total sells for ALL departments in each store
> at the end of the day

> The Sells table should look like this after user enter data for store "1"

> Date,Store_Id,Department_Id,Total_Sells

> 29/7       1                1                 100
> 29/7       1                2                 200
> 29/7       1                3                 300

> what is the best way to achieve this?
> is there away to join the table together and get the store_id and the
> deprtment_id columns values
> for the empty sells table?
> what i am doing now is opening two recordstes
> one contains the Departments table
> and  the other the Sells table
> than i am adding new records to the Sells table with the values from the
> Departments table
> (store_id and the deprtment_id columns)

> i am using ADO,ACCESS and VB

> Thanks in advance

> Tomer



Fri, 14 Jan 2005 07:28:44 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. need help please:joined tables,add new entries based on one table columns

2. SQL - Join one table in one destination to another table in another destination (DBF)

3. help i need to join two or more tables to one

4. Help Help Need to copy a field from one table into another table

5. Join several tables to ONE table

6. Eliminate records of one table based on another table record count

7. Adding a new entry to a table from a form's combobox

8. Create new fields in a table based off of fields in another table

9. ADO Add New Column to a Table

10. Adding new column to existing table

11. Adding new column to existing table

12. Adding new column to existing table

 

 
Powered by phpBB® Forum Software