Relational Database to Flat File Query

[susanmgarrett]

I have inherited a relational database. I need to automate the creation of a flat file from this database in excel format for automated input into another database.

The problem is, I can't seem to figure out how to create a query that will combine the relational data into a flat file format.

Tables include:

Track (including track number)
Composer (including Track number)
Publisher (including track number)

Plus there are linking tables that link both composer and publisher to track.

It's easy enough to display the data using subforms and subreports based on grouping (there are sometimes multiple compuers per track and multiple publishers per composer). What I can't figure out is how to set up the query to result in a flat file that will fill in multiple information, as follows:

Track Composer1 Composer 2 Composer 3

I'm not a VBA programmer and am a moderate Access 2003 user.

Any assistance would be very much appreciated.

Thanks!

Susan

 

[KenHigg]

Sounds like a lot extra work. If you don't mind me asking, what kind of db is this going to ultimately be in?

kh

 

[susanmgarrett]

Exporting as a flat file to database

This is coming from a new database and being transferred into an old DOS database that's part of an ancient proprietary program that's written in a language so old it may as well be sanskrit.

It's going to be deprecated in in a few months, but until that happens I need to be able to drop the new database data into the old database for certain reports to be produced.

Thanks!

Susan

 

[KenHigg]

Seems you could just do this in a query and export the query results.

kh

 

[susanmgarrett]

Query construction

Yeah, that's what I thought. But I can't figure out how to break that composer field automatically into the requisite fields for a single record.

My table has the following records:

Track, Composer

Right now my query results in three records:

Track 1, Composer 1
Track 1, Composer 2
Track 1, Composer 3

When what I need is:

Track 1, Composer 1, Composer 2, Composer 3

Any suggestions?

 

[Pat Hartman]

Now is as good a time as any to start your journey to proficiency with VBA
Concatenate many-side list

 

[KenHigg]

Or you may be able to do something like this:

Sample db

kh

 

[Jon K]

..... I can't figure out how to break that composer field automatically into the requisite fields for a single record.

If you need to put Composer 1, Composer 2, etc into separate fields in a query, try the attached sample database. The database contains a series of two queries. You can run the second one.


In the table, [Track] is a numeric field. If it is a text field, its values must be surrounded by single quotes in the DCount() function in the first query i.e.

SELECT tblTrackComposers.Track, tblTrackComposers.Composer,
"Composer " & DCount("*","tblTrackComposers","[Track]='" & [Track] & "' and [Composer]<='" & [Composer] & "'") AS Num
FROM tblTrackComposers;

The second query is a crosstab query based on the frist query:-
TRANSFORM First(qryOne.Composer) AS FirstOfComposer
SELECT qryOne.Track
FROM qryOne
GROUP BY qryOne.Track
PIVOT qryOne.Num;


Note these limitations:-
1) As DCount() is used in the first query, running the second query will take time if the table is large.

If the table is large, then I think you can achieve what you wanted only in VBA.


2) The composer names for each Track # are sorted in ascending order in the query results.

If you don't want the composer names sorted, you can add an AutoNumber field to the table and, in the DCount() function in the first query, replace the [Composer] field with this AutoNumber field. Since it's a numeric field, you must also remove the single-quotes surrounding its value in the DCount(),
.

 

[susanmgarrett]

Beautiful!

Or you may be able to do something like this:

Sample db

kh

Thanks very, very much!

I imported the queries and they work perfectly for the Composer section of the database, as it can import into a text field. I'm exchanged a non-delimiting character for the comma and it seems to work just fine.

That's exactly what I needed for the composers.

Thank you again.

Susan

 

[susanmgarrett]

Perfect answer for Publisher section!

Jon - believe it or not, this is THE perfect answer for the publisher section of my flat file, because I need that information split into two fields.

Query1 runs just fine.

You're right about Query2 running slow because Query1 results in just under 400,000 records. Would it make more sense for me to turn Query1 into a MakeTable and run Query2 from that created table?

Thanks for all of your help! This is certainly putting me on the right track.

Regards

Susan

If you need to put Composer 1, Composer 2, etc into separate fields in a query, try the attached sample database. The database contains a series of two queries. You can run the second one.


In the table, [Track] is a numeric field. If it is a text field, its values must be surrounded by single quotes in the DCount() function in the first query i.e.

SELECT tblTrackComposers.Track, tblTrackComposers.Composer,
"Composer " & DCount("*","tblTrackComposers","[Track]='" & [Track] & "' and [Composer]<='" & [Composer] & "'") AS Num
FROM tblTrackComposers;

The second query is a crosstab query based on the frist query:-
TRANSFORM First(qryOne.Composer) AS FirstOfComposer
SELECT qryOne.Track
FROM qryOne
GROUP BY qryOne.Track
PIVOT qryOne.Num;


Note these limitations:-
1) As DCount() is used in the first query, running the second query will take time if the table is large.

If the table is large, then I think you can achieve what you wanted only in VBA.


2) The composer names for each Track # are sorted in ascending order in the query results.

If you don't want the composer names sorted, you can add an AutoNumber field to the table and, in the DCount() function in the first query, replace the [Composer] field with this AutoNumber field. Since it's a numeric field, you must also remove the single-quotes surrounding its value in the DCount(),
.

 

[Jon K]

Susan,
I don't have a large table for testing right now, but I think creating a temporary table with 400,000 records will also take time.

Jon