Groups

    MS Access Help!!!!

    Afternoon All,

    I wonder if any MS Access experts could help me out please.

    I have a table which has a list of customers, and in that table there are 3 columns...Customer ID, Customer Group and Customer Name....Now what i am trying to do is a query to is to pull all the customer groups and if thats null then pull the customer name...

    But i want is the data to sit in one column...no two sperate ones when running the queries!

    7 Comments

    Hi,

    I assumed your table was called [Customers] & the heading of the resultant column was [Customer]

    SELECT
    IIf(IsNull([Customer Group]),[Customer Name],[Customer Group]) AS [Customer]
    FROM
    [Customers]


    BFN,

    fp.

    You will need 2 queries and join them as a union, something like this:

    select customer_group as customer
    from table
    where customer_group is not null
    UNION
    select customer_name as customer
    from table
    where customer_group is null

    fanpages;8931686

    Hi,I assumed your table was called [Customers] & the heading of the … Hi,I assumed your table was called [Customers] & the heading of the resultant column was [Customer]SELECTIIf(IsNull([Customer Group]),[Customer Name],[Customer Group]) AS [Customer]FROM[Customers]BFN,fp.



    Op this is probably the best method so I would go for this :thumbsup:

    Original Poster

    Thanks people....i just figured it out...

    I used the following:

    IIF([Customer Name] Is Null, [Customer Group],[Customer Name]) As Customer

    that worked a treat.....Thanks for you help!

    Abz;8931810

    Thanks people....i just figured it out...I used the … Thanks people....i just figured it out...I used the following:IIF([Customer Name] Is Null, [Customer Group],[Customer Name]) As Customerthat worked a treat.....Thanks for you help!



    But that is the converse of what you asked for.

    You wished to "...pull all the customer groups and if thats null then pull the customer name...".

    Using your suggestion what you have is something that provides the [Customer Name] in preference, & the [Customer Group] if the Name is 'Null'.

    Thanks for the "rep" in any case.

    BFN,

    fp.

    Original Poster

    fanpages;8931890

    But that is the converse of what you asked for.You wished to "...pull all … But that is the converse of what you asked for.You wished to "...pull all the customer groups and if thats null then pull the customer name...".Using your suggestion what you have is something that provides the [Customer Name] in preference, & the [Customer Group] if the Name is 'Null'.Thanks for the "rep" in any case.BFN,fp.




    Correct....But Im not using a customer table...i was just giving the example of a customer table as it was easier to get point accross of what i was trying to do....

    I guess in my reply/opening post i got the fields the wrong way round...but the main thing is I got what i needed...

    so thanks :thumbsup:

    Banned

    Spriggan;8931712

    You will need 2 queries and join them as a union, something like … You will need 2 queries and join them as a union, something like this:select customer_group as customerfrom tablewhere customer_group is not nullUNIONselect customer_name as customerfrom tablewhere customer_group is null


    quite possibly the worst SQL query I have ever seen to retrieve data from a single table! :w00t:
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Magazine competitions - Issue 39 @ tvchoicemagazine.co.uk44
      2. Win a Karcher Xpert Pressure Washer88
      3. Win 1 of 3 x £1000 Selfridges vouchers with Virgin Trains98
      4. Win a safari adventure & claim a free Chicago Town pizza coupon33

      See more discussions