Very Computer

I have a large CD collection with the following fields: artist, title,
label, number, year, genre & comments.  If, after I run individual queries
i.e.. B.B. King, The Drifters, Elvis Presley could I then
incorporate these queries into 1 table which I could then alphabetize by
artist and print out the results?

Basically what I want to do is extract artists
(i.e. Elvis) that have more then 5 entries and then delete them from the
original CD Table so I won't have to print this same material when I update
my CD table and make a new printout.

Thank,
Emmy

Quote:> I have a large CD collection with the following fields: artist, title,
> label, number, year, genre & comments.  If, after I run individual queries
> i.e.. B.B. King, The Drifters, Elvis Presley could I then
> incorporate these queries into 1 table which I could then alphabetize by
> artist and print out the results?

> Basically what I want to do is extract artists
> (i.e. Elvis) that have more then 5 entries and then delete them from the
> original CD Table so I won't have to print this same material when I
update
> my CD table and make a new printout.

> Thank,
> Emmy

Hello and good morning,

If you dont mind me asking, how did you get all your cd collection info into
a paradox table ?

With paradox, you will find that there are many ways to accomplish what you
need to do.
That being said, I can tell you some of the steps to do what I think you
asked,
even though I might not do it that way myself.

How do you determine if an artist has more than 5 queries ?
Do you do a query and check the artist name and calc count unique on title ?

Even though you could do delete queries, I am not sure that is how I would
do it.
I might create an extra field on the main table, called useYN, an alpha
field with size 1,
and then create a series of queries to flag the ones I wanted.

Query 1 would change all records in master table so that useYN to "N "
Query 2 would do a query to create a summary table that shows how many
albums each artist has.
Query 3 would use summary table to link back to master table, an change
useYN to Y when number of albums was below a certain threshold, less than 5
in your example.
Query 4 would extract all master records with useYN that equal Y - and
create an answer table.
Then I would run my report, with the report being based on the answer table.

Deleting records doesnt necessarily scare me, but it usually is not my first
choice.
What if you delete records, and did not save them somewhere ?
Inputting the information again would be upsetting.

On certain types of paradox queries , paradox creates temporary auxillary
tables.
The delete query can do this.
But if you are not careful, and you dont have the settings correct, a delete
query may just remove the records from the original table, and not place the
records into an auxillary table.
Be careful.

To do what you asked,
first use pdox and table copy your master table to another name, so that you
have a full backup.
Make sure aux tables are being generated for queries.
Create a delete query and run it for artist you said.
This should create a deleted.db in your private subdirectory,
( exiting paradox at this point will delete this table automatically , be
careful )
use tools, utilities, rename  to rename the ":priv:deleted.db" to
":work:artistsBig.db".
I am assuming you know about priv and work aliases.
Some will suggest to create a new alias, but that isnt always necessary for
some quick adhoc thing.

Now you have 2 tables, master table and artistsBig.
If you want to run another delete query, run it.
You will now have another deleted query.
This time - dont rename it, because you dont want to crush the existing
artistsBig.db table.
Use Tools,utilities, add - to add records from deleted to artistsBig.
Repeat delete query and add records for each artist you want to move from
master to artistsBig.db.

Paradox gives you the power to do many things, including shooting yourself
in the foot.
Please be careful with delete queries, and consider use of flagging and
tagging.

Other suggestions to control limited printing of artists might be :
Create an artists.db that contains 2 fields, artist Name and printYN.
This could be a query from your master table, check the artist name, and in
another field , put calc "N" as printYN, and run the query.
This will get you an answer table.
Rename answer to luArtistsPrint.db.
Manually go through lookup table and flick N to Y as you want.
Then create a 2 table query that links the lookup table to the master table,
and extracts records from master table when the printYN = Y, run the query
and the records you want will be in the answer table.
Run your report , with report based on answer table.

Hope this helps more than confuses, it got kind of long.

Robert Wiltshire

Hello Robert,

Thank you for the information.  When I feel confident enough I will attempt
your suggestions.

I have between 4,500 and 5,000 CDs (we won't mention the LPs and 45's).  It
took many months of dedicated work when I decided to enter the
information into Paradox.  I have set up a new table entitled New-CD to list
recent purchases.  I add this information maybe twice a year and do a
complete printout.

Thanks again,
Emmy

A simpler approach would be to query all the data you want to a single table
(you can use various conditions so probably can get all the ones you want in
one query anyway). Then use a report grouped on the Artist to get the sort you
want and place the artist in that band so it only prints when the artist
changes. If you want you can subgroup on Album as well and print the artist
once, each album sorted alpha, and then the cuts within that. You still avoid
duplication that way.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since
1982

Sounds like it would also help if you started learning a bit about normalization
so you could have a better handle on designing your tables, systems and your
approach to this. I suggest you go to our Paradox resources page and get the
Normalization paper (and the Database Basics one too) and get a more solid
grounding in designing as well as more basics on using Paradox (link to the site
in my signature). In the long run you'll be glad you did. Anyone dedicated
enough to enter that much info into the system can learn this stuff (and you may
kick yourself a bit later when you realize ways you could have saved yourself
time and effort but they will be useful as the data grows.)

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since
1982

Hi Emmy!

Paradox has four basic types of stuff:
    -- Tables, for storing data;
    -- Forms, for stuffing data into tables;
    -- Queries, which find the data you want and copy it into a new table;
and
    -- Reports, for printing the data in the tables.
There's other stuff as well, but this is all you need.

The thing that Paradox does so well is have all these four types of stuff
work together. However, you have to use the right type of stuff for the
right task to get this advantage.

So -- you use a form to shove data into a table, queries to copy selected
data out, and reports to print the data:

+-------+       +=======+          +-------+       +--------+
| FORM  |------>| TABLE |---<=>--->| QUERY |------>| REPORT |
+-------+       +=======+    |     +-------+       +--------+
                             |
                             |     +--------+
                             +---->| REPORT |
                                   +--------+

Reports may seem the odd man out, but they are well worth the trouble. They
are more than just pretty printouts. They organize your data: sort, search,
combine results from different tables, etc. They bring order from chaos, and
*then* make it look pretty. You can define a report to run off a query as
well as a named table. Then whenever you update your table, you run a report
which automatically queries out what you want, organizes it, and makes it
easy to read.

This is why you may want to concentrate on creating a query/report combo you
can run over and over, instead of creating and modifying tables every time
you want a new look at your data.

Hope this helps.

Jim

PS. If the flow chart looks scrambled, and you want to see it, switch your
font to Courier or some such. The <=> is supposed to be a diamond with the
word "or" in it!

Quote:> Thank you for the information.  When I feel confident enough I will
attempt
> your suggestions.

You are welcome, please be careful if you delete.

If you create an extra field to flag and tag, you can be more confident you
wont undo some of the work you did already.

I am assuming you have some kind of report made up, would that be correct ?
Do you know what table it is based on ?
Do you know how to open a report based on a different table ?

I assume you could add a field, alreadyPrintedYN Alpha size 1.
Can you do a changeto N query on that field to set them all to N ?

Do you know how to view a table, then use zoom to find an item quickly in
the table ?

What do you think of adding a field to your database called dateAdded.
And fill it in when you input new items.
Then you can query/filter/isolate/extract based on that field.

Quote:> I have between 4,500 and 5,000 CDs (we won't mention the LPs and 45's).
It
> took many months of dedicated work when I decided to enter the
> information into Paradox.  I have set up a new table entitled New-CD to
list
> recent purchases.  I add this information maybe twice a year and do a
> complete printout.

Wow - that is impressive. Dedicated work indeed.
Do you ever have an issue with inputting a duplicate ?

Are you doing digital music or just creating a library list of the disks you
have ?

Robert Wiltshire