By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Adding multiple Lines for data input

P: 6
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
Mar 24 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,409
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.
Mar 24 '12 #2

P: 6
Thanks NeoPa

You got me onto the right track

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

Thanks muchly
Ivan
Mar 25 '12 #3

NeoPa
Expert Mod 15k+
P: 31,409
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.
Mar 25 '12 #4

P: 6
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...
Mar 26 '12 #5

P: 6
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
Mar 26 '12 #6

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

Ivan
Mar 26 '12 #7

NeoPa
Expert Mod 15k+
P: 31,409
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.
Mar 26 '12 #8

P: 6
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
Mar 26 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.