I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 1 field in a table to determine the output for multiple fields in the flatfile.
I think the best way to convey what i am trying accomplish is by;
If TABLE.FIELD1 = XXX then
then OUTPUT1 = Y
OUTPUT2 = N
OUTPUT3 = TABLE.FIELD3
OUTPUT4 = 3
OUTPUT5 = ("WORKING CASE STATEMENT")
else OUTPUT1 = N
OUTPUT2 = null
OUTPUT3 = null
OUTPUT4 = null
OUTPUT5 = null
If TABLE.FIELD1 = RRR then
then OUTPUT6 = Y
OUTPUT7 = N
OUTPUT8 = TABLE.FIELD3
OUTPUT9 = 3
OUTPUT10 = ("WORKING CASE STATEMENT")
else OUTPUT6 = N
OUTPUT7 = null
OUTPUT8 = null
OUTPUT9 = null
OUTPUT10 = null
Sorry if this is confusing, but what would be the best means of accomplishing this? I know a single CASE can't produce output for more than one field.
Thanks in advance.
5 2121 ck9663 2,878
Recognized Expert Specialist
I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 1 field in a table to determine the output for multiple fields in the flatfile.
I think the best way to convey what i am trying accomplish is by;
If TABLE.FIELD1 = XXX then
then OUTPUT1 = Y
OUTPUT2 = N
OUTPUT3 = TABLE.FIELD3
OUTPUT4 = 3
OUTPUT5 = ("WORKING CASE STATEMENT")
else OUTPUT1 = N
OUTPUT2 = null
OUTPUT3 = null
OUTPUT4 = null
OUTPUT5 = null
If TABLE.FIELD1 = RRR then
then OUTPUT6 = Y
OUTPUT7 = N
OUTPUT8 = TABLE.FIELD3
OUTPUT9 = 3
OUTPUT10 = ("WORKING CASE STATEMENT")
else OUTPUT6 = N
OUTPUT7 = null
OUTPUT8 = null
OUTPUT9 = null
OUTPUT10 = null
Sorry if this is confusing, but what would be the best means of accomplishing this? I know a single CASE can't produce output for more than one field.
Thanks in advance.
I think you're looking for UNION
try: - select
-
OUTPUT1 = Y,OUTPUT2 = N, OUTPUT3 = TABLE.FIELD3, OUTPUT4 = 3, OUTPUT5 = 'WORKING CASE STATEMENT',
-
OUTPUT6 = 'N', OUTPUT7 = null, OUTPUT8 = null, OUTPUT9 = null, OUTPUT10 = null
-
from YourTable where TABLE.FIELD1 = XXX
-
union
-
select OUTPUT1 = 'N', OUTPUT2 = null, OUTPUT3 = null, OUTPUT4 = null, OUTPUT5 = null,
-
OUTPUT6 = Y,OUTPUT7 = N, OUTPUT8 = TABLE.FIELD3, OUTPUT9 = 3, OUTPUT10 = 'WORKING CASE STATEMENT'
-
from yourtable where TABLE.FIELD1 = RRR
-- CK
UNION is not what I'm looking for. the value of the one fieild literally dictates the value of 20 others in one line of the report. i can have up to 3 types of insurance. if i have XXX then it populates those output fields 1-5, if i dont then it prints N in the first and leave the remaining blank. if i have YYY or ZZZ then it poulates fields 6-10 and 11-15, respectively.
deepuv04 227
Recognized Expert New Member
UNION is not what I'm looking for. the value of the one fieild literally dictates the value of 20 others in one line of the report. i can have up to 3 types of insurance. if i have XXX then it populates those output fields 1-5, if i dont then it prints N in the first and leave the remaining blank. if i have YYY or ZZZ then it poulates fields 6-10 and 11-15, respectively.
try the following query hope will help you...
[code = sql]
SELECT Case TABLE.FIELD1
when 'xxx' or 'rrr' then 'y'
else 'n' end as output1 ,
Case TABLE.FIELD1
when 'xxx' or 'rrr' then 'n'
else null end as output2 ,
Case TABLE.FIELD1
when 'xxx' or 'rrr' then TABLE.FIELD3
else null end as output3 ,
Case TABLE.FIELD1
when 'xxx' or 'rrr' then 3
else null end as output4 ,
Case TABLE.FIELD1
when 'xxx' or 'rrr' then 'WORKING CASE STATEMENT'
else null end as output5
FROM TABLE_NAME
[/code]
Fantastic; crude but it gets the job done. thanks a bunch guys.
Update:
Ok, so it works, to an extent. While it does print the output in the correct fields, it only does so 1 type per line. If they have all 3 types of coverage, then they will have 3 seperate lines. It will correctly populate one of the 3 only once, while filling in the others as though they dont have that coverage. however, the remaining lines will follow suit; correctly state they have 1 type of covereage while claiming NO on the remaining, but i need all to appear on one line per person. i know this is confusing. if you need more clarification let me know.
thanks
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: ssb |
last post by:
Hello,
This may be very elementary, but, need help because I am new to access
programming.
(1) Say, I have a column EMPLOYEE_NAME. How do I fetch (maybe, cursor
?) the values one by one and populate a combo box with these names.
(this way, I can display all the EMPLOYEE_NAME values)
(2) In general, can I do additional processing on...
|
by: Richard Hollenbeck |
last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank
you. I try to ask relevant questions so that they don't just benefit me,
but also benefit the group. I'm currently overwhelmed by useless examples
across the web on how to make "dynamic crosstab reports" without myself
having a basic understanding about how to retrieve...
|
by: James Fortune |
last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't
understand why people who know how to write code to completely replace
a front end do not write something that will automate the code that
implements managing unbound controls on forms given the superior
performance of unbound controls in a client/server environment. I can...
|
by: Susan Bricker |
last post by:
Greetings.
I am having trouble populating text data that represents data in my
table. Here's the setup:
There is a People Table (name, address, phone, ...)
peopleID = autonumber key
There is a Judge Table (information about judges)
judgeID = autonumber key
|
by: SueB |
last post by:
I currently have a 'mail-merge' process in my Access db project. It
generates custom filled out Award Certificates based on an SQL SELECT
statement in a VBA routine invoked by clicking on a command button.
The "problem": I want to conditionally insert some text into the award
certificate based on a field selected by the SELECT statement. ...
| |
by: Flanman |
last post by:
I have an ASP web form that I want to populate fields based on the first
field choice. Example I have 4 fields item, price, delivery, availability. I
have all these items setup in an access table. When a user chooses an option
from the item drop down, the price, delivery, availability will then
populate. I have already made my connection to...
|
by: pic078 via AccessMonster.com |
last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files)
that remains stuck in task manager after exiting the application - you can't
reopen database after exiting as a result - I have read every post out there
and spent hours trying to figure out the problem with no success whatsoever -
I have constrained the problem to...
|
by: SimpDogg |
last post by:
Hey guys another Newbie here...
I have a combobox(JobName) on my form tied to a
table named (Jobs) with one field for all of the
jobs in the comboxbox. I want to auto populate the
Due Out Date on the form based off the job selection.
The only thing I could come up with is this
If .Value = "EZ0032" Then
+ 4
ElseIf .Value = "EZ0840"...
|
by: bpw22az |
last post by:
I am currently in the process of setting up an asp page that sends an inquiring student an email regarding his/her application status. The student enters his/her email address on a web page, the address is associated with the correspponding record in a sql database table, and an email is generated and sent to them based on app status. I am...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |