Query to create a flat file in Access 2003

  • Thread starter Thread starter susanmgarrett
  • Start date Start date
S

susanmgarrett

I have inherited the following database structure:

Track Table
Composer Table
Publisher Table

All are linked through indexed fields.

My issue is that I sometimes have multiple composers per track, as
well as multiple publishers per track.

I would like to create a query through which I can drop a flat file
per track, having the extra composers and publishers drop into fields
like Composer1, Composer2, Composer3 and Publisher1, Publisher2,
Publisher3.

How would I even go about setting this up?

Thanks.

Susan
 
a flat file is a text file that has no formatting and
contains ascii text only.(which is why they are call flat
files) notepad produces such files.
if i understand you, you are wanting to put a flat file
in .....where?!?!? what do you mean "per track"?
not possible.
create more fields in your table for this. perhaps a
comment field.
 
Hi Susan,

Stay Relational! No flat files, please.

I infer that Track is the main entity in your application. tblComposer and
tblPublisher are natural lookup tables.

I suggest the creation of two junction tables: tblTrackComposers and
tblTrackPublishers.

tblTrackComposers will have the primary keys of [thistrack] and
[thiscomposer] as foreign keys.

tblTrackPublishers will have the primary keys of [thistrack] and
[thispublisher] as foreign keys.

You can then create a form with two subforms. The main form is based on
Track and the subforms are each based on one of the junction tables. Access
Help will show you how to set up your form/subform.

If you do the above you'll also be able to search your database for every
appearance of a composer or publisher.

Given the above, you'll be able to accommodate any number of composers and
publishers for a given track. Notice that you can also have a field for
notes in each of the junction tables that would allow you to add notes about
this instance of this track and composer or this track and publisher. Date
field? ??

HTH
 
Can you add a new field to your table: composerNumber? Then walk
through all the records in your table (use a VBA loop, records sorted
by track title), and populate the new field with 1, 2, 3... as
appropriate. You will have to work out the details of keeping track of
the composer number for each track. Finally, create a cross-tab query
that uses the track title for row headings, 1, 2, 3 to create new
columns, and the composer name as the data item.

It's flakey... but should get the job done.

By the way -- you don't mention any linking tables between tracks and
composers or between tracks and publishers. I would have thought these
would be many-to-many relationships, thus requiring a linking table.


I have inherited the following database structure:

Track Table
Composer Table
Publisher Table

All are linked through indexed fields.

My issue is that I sometimes have multiple composers per track, as
well as multiple publishers per track.

I would like to create a query through which I can drop a flat file
per track, having the extra composers and publishers drop into fields
like Composer1, Composer2, Composer3 and Publisher1, Publisher2,
Publisher3.

How would I even go about setting this up?

Thanks.

Susan
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Larry
I agree completely with your database structure. However, I
interpretted her question as asking something different. I understood
her to mean that she had to create a flat structure in order to
satisfy some external requirement ie, and export to something that
required a flat structure -- but I may be mistaken.

Hi Susan,

Stay Relational! No flat files, please.

I infer that Track is the main entity in your application. tblComposer and
tblPublisher are natural lookup tables.

I suggest the creation of two junction tables: tblTrackComposers and
tblTrackPublishers.

tblTrackComposers will have the primary keys of [thistrack] and
[thiscomposer] as foreign keys.

tblTrackPublishers will have the primary keys of [thistrack] and
[thispublisher] as foreign keys.

You can then create a form with two subforms. The main form is based on
Track and the subforms are each based on one of the junction tables. Access
Help will show you how to set up your form/subform.

If you do the above you'll also be able to search your database for every
appearance of a composer or publisher.

Given the above, you'll be able to accommodate any number of composers and
publishers for a given track. Notice that you can also have a field for
notes in each of the junction tables that would allow you to add notes about
this instance of this track and composer or this track and publisher. Date
field? ??

HTH
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
I have inherited the following database structure:

Track Table
Composer Table
Publisher Table

All are linked through indexed fields.

My issue is that I sometimes have multiple composers per track, as
well as multiple publishers per track.

I would like to create a query through which I can drop a flat file
per track, having the extra composers and publishers drop into fields
like Composer1, Composer2, Composer3 and Publisher1, Publisher2,
Publisher3.
Click to expand...

This would be a *very bad* Table design. You can export data to a .txt
file in this format, once you have a correct table structure - but
please *don't* create a Table with these fields!

Instead, you need to model the many-to-many relationships using a
"junction" table. You'll need five tables in all:

Tracks
TrackID
Title
<info about the track but NOT its composer or publisher>

Composers
ComposerID
<bio information>

Publishers
PublisherID
<information about this publisher>

TrackComposers
TrackID << link to Tracks
ComposerID << link to Composers; 2-field joint Primary Key
<any info about how this composer was involved with this
composition, if needed>

TrackPublishers
TrackID
PublisherID << same logic
<etc>

You can create a Crosstab query on the five tables if you need to see
the "wide-flat" view of the data.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thank you to everyone for the suggestions - they're most helpful.

I need the query to format a file which will be exported as a flat
file (i.e., selected track records in a csv format) for import into
another system. If I could keep them in Access, so much the better,
but it t'ain't my call unfortunately.

That's why I'm trying to do this in a query, as opposed to a report or
a form (although I agree that a subform would probably get me to where
I need to go if it was a visual or lookup type of situation).

I was wondering if there were some sort of vba coding I could use with
If...Then statements (granted, I'm only a beginner as far as that sort
of thing goes), in either a module or macro that I could use to
automate the population of subsequent fields.

For example -

If the track number exists and if composer field 1 is not null, insert
the composer from the duplicate track into composer field 2.

Followed by - If the track number exists and if composer field 1 and
composer field 2 are not null, then insert the composer from the next
duplicate track into composer field 3

Until we've run through all duplicate tracks.

I have a junction table that links the composer database to the track
database - so technically I could populate a query that created the
extra fields from the composer and trackcomposer junction tables and
then simply link to the track file?

The publisher field is set up the same way.

It's a stop-gap measure for two or three months until our datalink is
forged between two incompatible programs. Being able to get this down
to a semi-automated process that could be run on a daily basis would
be ideal.

Any idea as to how I could get this accomplished?

Thanks!

Susan M. Garrett
 
You can use VBA to open a recordset, step through it searching for matching
records and selecting relevant items and then write the output to a file as
you go.

If you've never done that kind of thing before, look in VBA Help for the
Open Statement to get started. You'll have a bit of a learning curve. Be
patient with yourself (and with Help in the newer versions). Once you get
going you'll find that writing CSV files is a snap. If you got your hands
on an old BASIC text (say 20+ years old) you'll find all of the vile
management commands in it but not the database content.

Post back if you have problems.

HTH
 
on an old BASIC text (say 20+ years old) you'll find all of the vile
management commands in it but not the database content.
Click to expand...

"vile management commands"? Oh c'mon. They're not THAT bad! 8-)

Tom Lake
 
Hi Susan and all,

PMFJI. If I understand the question right, I'd start by thinking in
terms of creating a crosstab query to generate records containing
TrackID, Composer1, Composer2, Composer3

Then a second crosstab
TrackID, Publisher1, Pubisher2, Publisher3

Finally a third query would join the two crosstabs and the Tracks table
itself on TrackID, to retrieve the other Track fields you need.
 
Hi Tom.

I'm the embodiment of "Blind in one eye and can't see (well) out of the
other". Couple that with diminishing typing skills and some of the
documents I create are truly creative. The compiler helps keep us honest
with our code. We have spell checkers and grammar and syntax checkers but
haven't yet gotten to the level of content and "Oops" checkers.

Given the high level tools we have available it is funny to be using some of
the old tools exactly as we did several decades ago. You're right, it isn't
quite VILE. Thank be that we don't have to drop to the level of Assembler!
 
I'm not that familiar with crosstab queries - never had a reason to use
them. The results sound exactly what I'm looking for. How would I go
about setting one up with the fields I'd mentioned (namely because I've
spent an hour with the wizard and haven't a clue about what's
happening).

Thanks!

Susan
 
I'm not that familiar with crosstab queries - never had a reason to use
them. The results sound exactly what I'm looking for. How would I go
about setting one up with the fields I'd mentioned (namely because I've
spent an hour with the wizard and haven't a clue about what's
happening).

Thanks!

Susan
 
Back
Top