Solved

Output multiple records from one record using a query

Posted on 2003-11-17
6
301 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now