Selecting A Records Based on Multiple Requirements

[ks01]

Hello ... I hope someone can help me on this.

I have a table that lists customers and the books they have purchased tne the language they purchased that book in.

I have three requirements and what I thought was an easy task is turning out not to be so easy. Here is the way I originally thought to tackle this.

Create three columns: English, Spanish, Portuguese.

1. If the language specification is set to English or is null, then set column English to 1.

2. If language specification is set to Spanish, then set column Spanish to 1.

3. If lanuage specification is set to Portuguese or Spanish, then set column Portuguese to 1.

This is the code I used to handle this but this is not working the way I expected.

 SELECT ...,
        '1' AS ENGLISH, '0' AS SPANISH, '0' AS PORTUGUESE
   FROM TABLE 
  WHERE LANGUAGE IN ('EN','NULL')
UNION
 SELECT ...,
        '0' AS ENGLISH, '1' AS SPANISH, '0' AS PORTUGUESE
   FROM TABLE 
  WHERE LANGUAGE = 'SP'
UNION
 SELECT ...,
        '0' AS ENGLISH, '0' AS SPANISH, '1' AS PORTUGUESE
   FROM TABLE 
  WHERE LANGUAGE IN ('SP','PO');

Would anyone have any suggestions?

Thanks in advance!

 

[dhookom]

Each book has one language so use one field to store the language. IMO using 3 fields is just plain bad. What would happen if you add another language?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya ks01 . . .

You have whats called an [blue]un-normalized[/blue] table. Meaning customer names appear more than once. It would be better to split the table ... Customers, BooksPurchased. An example would look like:

TableName: [purple]tblCustomers[/purple]
CustID: as long ([blue]primarykey[/blue])
FName: as text
LName: as text
Adr : as text
.
.

TableName: [purple]tblBookspurchased[/purple]
BookID: as long ([blue]Primarykey[/blue])
CustID: as long ([blue]foreignkey[/blue])
Title : as text
Author: as text
Language as text ([purple]combobox[/purple])
.
.

With the above you could easily make a mainform (customers), with subform (books), using the wizard. Navigate the mainform and the books subform follows! ... synchronized!

[blue]The combobox for languages is the key to your problem[/blue]. Users simply select the proper language. Aditional languages could easily be added in this way. Instead of adding another language field to acount for (compounding your problem), you simply add to the list or a seperate table the combobox is based on.

The above is what you get when you thing like a [blue]relational database[/blue] ... setting up language fields as you propose is what you get when you think like a spreadsheet.

Rewferences:
Fundamentals of Relational Database Design

Defining Relationships Between Tables

Description of database normalization

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ks01]

Hi everyone ...

Thank you for the valuable feedback.

I completely agree, the data is in a less than desirable format and the requested output is no better.

However, the data is being passed to me by one application and the customer has given me strict requirements on how they want the result set to appear.

If I had the opportunity to redesign from ground up, I would have chosen to normalize data but unfortunately, this was a last minute request.

I appreciate the feedback. Thank you again!

 

[PHV]

Replace this:
WHERE LANGUAGE IN ('EN','NULL')
with this:
WHERE Nz(LANGUAGE,'EN') = 'EN'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886