Create records

[Guest]

Hi,

I have a main table with several child tables. I need to create a record in
each child table for the 11,000 records I have in the Main table. Can I do
this through an update or append query? The child tables only contain
records that match some of the main records, for example not all contacts in
the main table have a phone number record in the phone table.

The reason I need this, is that I'm having trouble with a report I created
using individual queries and subreports, so I am using one big query, this
way the report's format doesn't result in big chunks of white space.

Any suggestions are appreciated. Thanks

 

[Guest]

It can be done, but almost certainly its neither necessary not desirable. I
assume the problem is that rows from the referenced (main) table are not
returned where there is no match in one or more of the referencing (child)
tables. The way to avoid this is to LEFT OUTER JOIN the referenced table to
the referencing tables. In query design view this is done by right clicking
on the join line between the two tables and then selecting the second option
in the dialogue.

Ken Sheridan
Stafford, England

 

[Guest]

Back up the database first then use this query. Substitute your table names
for Main and CHILD. Also edit it for your field name instead of PROPID.

INSERT INTO CHILD ( PROPID )
SELECT Main.PROPID
FROM Main LEFT JOIN CHILD ON Main.PROPID = CHILD.PROPID
WHERE (((CHILD.PROPID) Is Null));

 

[Guest]

Thank you. My main table is called "tblMain", my child table is called
"tblLawCodes". I get a syntax error when I use the following:


INSERT INTO tblLawCodes (Main_ID_FK)
SELECT tblMain.Main_ID
FROM tblMain LEFT JOIN tbllawcodes ON Main.Main_ID = tbllawcodes.Main_ID_FK
WHERE (((tbllawcodes.Main_ID_FK) Is Null));

what am I doing wrong?? Thanks again

 

[John Vinson]

The reason I need this, is that I'm having trouble with a report I created
using individual queries and subreports, so I am using one big query, this
way the report's format doesn't result in big chunks of white space.

MUCH better than putting empty "placeholder" records in the child
table might be to use a Left Outer Join in the report's query. This
will give you NULL values for those records where there is no matching
child table record - exactly the same effect as creating 11000
redundant, unneeded empty records!

John W. Vinson[MVP]

 

[Guest]

I just tried this according to Ken's instructions, I have the Main table and
2 child tables in the query. But now I'm getting duplicates in the query,
instead of 13,000 records I'm getting around 16,000. What am I doing wrong?

 

[Guest]

You left one "Main" unedited in it. Try again. Take the others comment to
heart when they say do not do this.

INSERT INTO tblLawCodes (Main_ID_FK)
SELECT tblMain.Main_ID
FROM tblMain LEFT JOIN tbllawcodes ON tblMain.Main_ID = tbllawcodes.Main_ID_FK
WHERE (((tbllawcodes.Main_ID_FK) Is Null));

PS. In your report resize the controls by draging the bottom edge upward
until they are a hairline (this includes the subreport) and set the Can Grow
& Can Shrink property to YES. This should stop the white space.

 

[Guest]

Help. I've tried to group in the query and I also tried to group in the
Report Wizard on the Main ID from the main table, I'm still getting duplicate
records in my report. This must be something simple...but I'm not getting
it. Thanks

 

[John Vinson]

Help. I've tried to group in the query and I also tried to group in the
Report Wizard on the Main ID from the main table, I'm still getting duplicate
records in my report. This must be something simple...but I'm not getting
it. Thanks

Please post your SQL.

John W. Vinson[MVP]

 

[Guest]

Here it is:

SELECT tblMain.Main_ID, tblMain.[Client Name], tblMain.[Client?],
tblMain.Salutation, tblMain.Last_Name, tblMain.First_Name, tblMain.MI,
tblMain.Spouse, tblMain.Organization, tblMain.Title, tblMain.Address,
tblMain.Secondary_Address, tblMain.City, tblMain.State, tblMain.Zip,
tblMain.Plus_4, tblMain.Country, tblMain.Country_Code, tblMain.[E-Mail],
tblMain.Billing_Atty, tblMain.Orig_Atty, tblMain.Working_Attys,
tblMain.Date_Created, tblMain.Date_Modified, tblMain.Remarks,
tblMain.Archive, tblMain.Inactive, tblMain.[Web Site], tblPhone.Main_ID_FK,
tblPhone.Phone_Rec_ID, tblPhone.[Type of #], tblPhone.[Phone #],
tblPhone.Extension, tblPhone.[Intl Phone #], tblMailing.Main_Rec_FK_ID,
tblMailing.Accountant, tblMailing.Alumni, tblMailing.Broker,
tblMailing.Business_Organization, tblMailing.Conservation,
tblMailing.Conservation_Party, tblMailing.Corp_Bus_Pension,
tblMailing.Election_Law, tblMailing.Employment, tblMailing.Environmental,
tblMailing.Estate_Tax, tblMailing.Gaming, tblMailing.[Hi-Tech/IP],
tblMailing.Holiday, tblMailing.Homebuilder, tblMailing.[In-House Counsel],
tblMailing.Land_Use, tblMailing.Law_Firm, tblMailing.Lobbyist,
tblMailing.Public_Policy, tblMailing.Real_Estate, tblMailing.No_Mailings
FROM (tblMain LEFT JOIN tblPhone ON tblMain.Main_ID = tblPhone.Main_ID_FK)
LEFT JOIN tblMailing ON tblMain.Main_ID = tblMailing.Main_Rec_FK_ID;

 

[John Vinson]

FROM (tblMain LEFT JOIN tblPhone ON tblMain.Main_ID = tblPhone.Main_ID_FK)
LEFT JOIN tblMailing ON tblMain.Main_ID = tblMailing.Main_Rec_FK_ID;

If there are any records in tblMain with multiple records in tblPhone
*and* multiple records in tblMailing, you'll get all possible
combinations of records in those two tables.

If this is for a Report, you can use the Group By feature of the
report to get one block of results per address, or per user, or per
phone as you wish. If you have multiple phone numbers, do you need to
see them all? or could you use a criterion on Phone to limit it to
just one phone record?

John W. Vinson[MVP]

 

[Guest]

No, I need a report that shows all the info from the Main table plus all
phone numbers and mailing items. I originally created a main report with
just the Main table, then created subreports out of the Mailing and Phone
tables. The problem with that was I was getting big chunks of random white
space (all fields appeared however, just moved down) throughout the report
which didn't have anything to do with null values.

I tried the grouping, the mailing table seems to be working fine on this
report but I'm getting a separate page for each phone number, so I guess the
phone numbers are creating the problem.

Is there anything here that is obvious?


FROM (tblMain LEFT JOIN tblPhone ON tblMain.Main_ID = tblPhone.Main_ID_FK)
LEFT JOIN tblMailing ON tblMain.Main_ID = tblMailing.Main_Rec_FK_ID;

Thanks for helping

 

[John Vinson]

I tried the grouping, the mailing table seems to be working fine on this
report but I'm getting a separate page for each phone number, so I guess the
phone numbers are creating the problem.

Check to make sure that you don't have New Page in the section header
or footer. What I've done for this situation is to have all the
address information in the Address header, and nothing but the phone
numbers in the Detail section.

John W. Vinson[MVP]