Solved

Output multiple records from one record using a query

Posted on 2003-11-17
6
304 Views
Last Modified: 2009-07-29
Is it possible to output more than one record from each record read into a query?

ie) if I have an append query that reads tableA, I want to append 4 records to tableB from each record in tableA using different fields from table A

If not in a query is there another way....keeping in mind I am pretty new to all this?

This is the SQL view of my query so far....

INSERT INTO [Tee Time Analysis Table] ( [Date], [Tee Time], [Member No], [Member Name], Comments, [Member Type] )
SELECT [Tee Times].[Todays date], [Tee Times].[600], [Tee Times].[600m1no], [Tee Times].[600m1], [Tee Times].[600comments], Mid([600m1no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m1no]) Is Not Null));

I would like the same info for 600m2no, 600m3no and 600m4no
0
Comment
Question by:m4moi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
gajender_99 earned 50 total points
ID: 9769764
hi try this

INSERT INTO [Tee Time Analysis Table] ( [Date], [Tee Time], [Member No], [Member Name], Comments, [Member Type] )
(
SELECT [Tee Times].[Todays date], [Tee Times].[600], [Tee Times].[600m1no], [Tee Times].[600m1], [Tee Times].[600comments], Mid([600m1no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m1no]) Is Not Null))
union
SELECT [Tee Times].[Todays date], [Tee Times].[600], [Tee Times].[600m2no], [Tee Times].[600m1], [Tee Times].[600comments], Mid([600m1no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m2no]) Is Not Null))
union
SELECT [Tee Times].[Todays date], [Tee Times].[600], [Tee Times].[600m3no], [Tee Times].[600m1], [Tee Times].[600comments], Mid([600m3no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m3no]) Is Not Null))
union
SELECT [Tee Times].[Todays date], [Tee Times].[600], [Tee Times].[600m4no], [Tee Times].[600m1], [Tee Times].[600comments], Mid([600m4no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m4no]) Is Not Null))
):

try this
0
 

Author Comment

by:m4moi
ID: 9769851
gajender_99

i get a "syntax error in INSERT INTO error" and the cursor is put at the ( just below INSERT INTO
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9769909
I would execute 4 different queries, much simpler and as quick as unions.

Cheers, Andrew
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 8

Expert Comment

by:gajender_99
ID: 9770393
hi sorry for that i missed 1 line now try this

INSERT INTO [Tee Time Analysis Table] ( [Date], [Tee Time], [Member No], [Member Name], Comments, [Member Type] )

Select todays_date,600,mno,mno1,mcomment,expr1
From (
SELECT [Tee Times].[Todays date] as todays_date, [Tee Times].[600] as 600, [Tee Times].[600m1no] as mno, [Tee Times].[600m1] as mno1, [Tee Times].[600comments] as mcomment, Mid([600m1no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m1no]) Is Not Null))
Union
SELECT [Tee Times].[Todays date] as todays_date, [Tee Times].[600] as 600, [Tee Times].[600m2no] as mno, [Tee Times].[600m1] as mno1, [Tee Times].[600comments] as mcomment, Mid([600m2no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m2no]) Is Not Null))
Union
SELECT [Tee Times].[Todays date] as todays_date, [Tee Times].[600] as 600, [Tee Times].[600m3no] as mno, [Tee Times].[600m1] as mno1, [Tee Times].[600comments] as mcomment, Mid([600m3no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m3no]) Is Not Null))
Union
SELECT [Tee Times].[Todays date] as todays_date, [Tee Times].[600] as 600, [Tee Times].[600m4no] as mno, [Tee Times].[600m1] as mno1, [Tee Times].[600comments] as mcomment, Mid([600m4no],2,1) AS Expr1
FROM [Tee Times]
WHERE ((([Tee Times].[600m4no]) Is Not Null))
);
0
 

Author Comment

by:m4moi
ID: 9772327
gajender_99

this gives me a "syntax error in FROM clause"
0
 

Author Comment

by:m4moi
ID: 9806410
gajender_99

i'll give you the points anyways but I had to take the INSERT INTO part out and make the query a select query and then append the results of that into my table....that saved me some work but would have been nice to make the Insert part work too
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question