Hello,
Thank you for the information. I tried the Union all. Still dropping
records. Here is a sample of my statement:
SELECT "Rating",2, TakeOutExtractRating.RISKID
FROM TakeOutExtractRating
Union all
SELECT "Main", 1,TakeOutExtractMain.RISKID
FROM TakeOutExtractMain
ORDER BY Riskid, expr1001;
expr1001 is the number 1 or 2 I can sort to make the output records
show up in the sequence I desire.
But, I notice I have some syntax differences between yours and mine.
I have SELECT "Main", 1,TakeOutExtractMain.RISKID (table DOT field---
for all fields)
where as you have SELECT "B" AS
, [Field1], [Field2]
so would:
the As
make a difference?
Do I need the brackets?
Thank you again,
Lmnorms1
On Apr 17, 1:04 pm, KARL DEWEY <
[email protected]>
wrote:
What can I do to ensure that all records get into the file?
Use UNION ALL. Like UNION ALL SELECT "B" AS
, [Field1], [Field2] ...
can we sort by table b with the marker B apearing first in the file output?
This will put all of table B data first. but it is not sorting as such.
SELECT "B" AS
, [Field1], [Field2]
FROM [Table B]
UNION ALL SELECT "A" AS
, [Field1], [Field2]
FROM [Table A]
UNION ALL SELECT "C" AS
, [Field1], ""
FROM [Table C]
UNION ALL SELECT "D" AS
, "", [Field2]
FROM [Table D];
I know of no way to do what your example shows - first record from each
table followed by second record from each without a lot more work. You would
need to have a field in the tables indicating the record number.
Then use this --
SELECT 1 AS Sort1, "B" AS
, [Field1], [Field2]
FROM [Table B]
ORDER BY [Field1], Sort1
UNION ALL SELECT 2 AS Sort1, "A" AS
, [Field1], [Field2]
FROM [Table A]
UNION ALL SELECT 3 AS Sort1, "C" AS
, [Field1], ""
FROM [Table C]
UNION ALL SELECT 4 AS Sort1, "D" AS
, "", [Field2]
FROM [Table D];
--
KARL DEWEY
Build a little - Test a little
:
Also, I compared the input files. The input files add up to 57420
total records. The file complied into one as only 57403. What can I do
to ensure that all records get into the file? This looks like 17
records dropped off.
Thank you,
lmnorms1
Thank you. This worked.
Last question; can we sort by table b with the marker B apearingfirst
in the file output?
Table 5 (or File)
B Rec1
A Rec1
C Rec1
D Rec 1
B Rec2
A Rec2
C Empty
D Rec2
Thank you,
lmnorms1
On Apr 16, 11:47 pm, "Douglas J. Steele"
Introduce dummy columns to pad the subqueries out so that theyall have the
same number of fields, of the same data type:
SELECT "A" AS
, [Field1], [Field2]
FROM [Table A]
UNION SELECT "B" AS
, [Field1], [Field2]
FROM [Table B]
UNION SELECT "C" AS
, [Field1], ""
FROM [Table C]
UNION SELECT "D" AS
, "", [Field2]
FROM [Table D];
Note that it doesn't matter whether the field names are different: there
just has to be the same number of fields, and the type of eachfield must
match.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
Hello,
This worked except where there were tables that had a different number
of columns from table c and D.
What can I do?
On Apr 16, 4:52 pm, KARL DEWEY <
[email protected]>
wrote:
Use a union query ---
SELECT "A" AS
, [Field1], [Field2]
FROM [Table A]
UNION SELECT "B" AS
, [Field1], [Field2]
FROM [Table B]
UNION SELECT "C" AS
, [Field1], [Field2]
FROM [Table C]
UNION SELECT "D" AS
, [Field1], [Field2]
FROM [Table D];
--
KARL DEWEY
Build a little - Test a little
:
Hello,
I have an access db that as several tables. I want to create one table
(or file).
The tables have keys. Some tables might not contain information for
the keys.
For example:
Table a Table B Table c Table d
Rec 1 Rec 1 Rec 1 Rec 1
Rec 2 Rec 2 Rec 2
How do I create one table that contains all information (including
empty records for tables that don’t have corresponding info.)?
Table 5 (or File)
A Rec1
B Rec1
C Rec1
D Rec 1
A Rec2
B Rec2
C Empty
D Rec2
Marker for original table must be present on output.
Thank you,
(e-mail address removed)
