If statement in access query

[chrsab]

Hello

I have recently come across a database which is not showing data the way it should. In a nut shell it shows clients details, who out of these clients could have a policy and an addon but the information is in this row to show this. What l need is in a query to show if a client has this addon for a new row to appear underneath which is duplicating the clients details. So it would display two rows of the same data.

I have thought about a vb macro to clone the details and then a expression to start the macro off but there must be a simplier way as not too keen on expression building!!!

Any help???

Thank you for your time..

 

[Hap007]

Dear chrsab,

I think that you can handle this issue by defining the relationships between the tables.

So, while in query design view, right click your mouse on the line linking the tables.

Now select the correct relationship.

You probably want show main table and all records (not sure of the wording here.

Experiment until the query gives you your correct results.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[chrsab]

Good suggestion but i think this would not work. I will below draw an example of a query running and show what i mean:

Column A Column B
Yes No
No Yes
No No
Yes Yes

I would like if Yes appears in both column A and column B, this row to then duplicate onto the next row, so in affect would have two rows of yes in both column a and column b.

Hope this makes sense

 

[Mike555]

Would a 3rd column work?

IIF([ColumnA]<>"Yes","",IIF([ColumnB]="Yes","Both Yes","")

 

[chrsab]

Thanx for the contribution but that wouldn't work to our advantage.

See each day, a report would be run to add together the amount of policies and addons which have been sold that day, so this is why i need it to show in a new row.