Adding multiple Lines for data input

[Ivan Kartun]

Hi all

Please help

I need to generate multiple lines (per employee per day)

3 tables
Employee - Empl No 001 John, empl No 002 Mary
Day - Day ID 12001 Jan 1 2012

The next table needs to be generated as follows:

Daily input Table
Record 1
Day No - 1201
Empl No - 001
Hrs in Office - 0
Hrs at client - 0
Hrs pers - 0

Record 2
Day No - 1201
Empl No - 002
Hrs in Office - 0
Hrs at client - 0
Hrs pers - 0

etc for each employee

These records would then be updated individually

Any ideas

Thanks

[NeoPa]

Use a simple APPEND query based on both other tables without any JOIN. This produces what you need, which is a Cartesian Product of the two tables. See SQL JOINs for more.

[Ivan Kartun]

Thanks NeoPa

You got me onto the right track

I eventually used (without coding) the union option in the query (append)

Thanks muchly
Ivan

[NeoPa]

When you say "the union option", are you referring to what I suggested or the SQL UNION statement. The latter would not do as you asked about. It would have a very different effect.

[Ivan Kartun]

Yep the union after much playing is not correct

In the Select i have got the employee and day info up but not if i do an append to the daily input table - it says i need at least one destination field

I tried putting in field from there (joins (relationships)) then were included when i showed the table - but nothing happens - i cannot get all the records selected into the target database

I've tried a few things but have been unsuccessful

help...

[Ivan Kartun]

as soon as i include the input table and sopecify the fields i want to append to - the append run will do nothing - whereas in select mode without the append (or the input table which i would like to append to - it brings up the correct data

2 issues

1 i think the problem is when i bring in a table that has joins in it (i have tried removing the relationships
2 it comes up with an error saying it is disabled

[Ivan Kartun]

I have unblocked the location and it works now - thanks for all your help

Ivan

[NeoPa]

I don't really understand the process you've been through, but it doesn't sound very much like what was suggested. If you post your SQL I'll tell you if you have got the right idea or not. If not I'll try to clarify for you what you need.

[Ivan Kartun]

The problem that i had related to the blocked table - in the trust centre - i enabled it and exactly what you suggested worked - so thank you