Solved

Output multiple records from one record using a query

Posted on 2003-11-17
6
303 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

763 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