help with union query-how to add column the describe the union result RRS feed

  • Question

  • so i have a table with three columns representing payments schedule (for example first istallment ,second installment,third installment)

    each column is associated with its brother representing date for the payments

    like this

    product --------- first isntallment--------date-------------second installment-----------date-------------third installment----------date

    now i want to do a sql select command that bring to me this details

    product--------------payment type--------------date---------------amount

    is there away to do such thing

    in my case i created 3 additional column

    each time the program loads i update those column so each column get this value (first installment,second installment,third installment)

    so when i want to produce the data i do the union code in select command where i select the same table 3 times and at each time i select different payment and date but i name them the same in each select command in addition to one of the column representing them to be a payment type

    like this

    select conTents,INVOICEDATE,invoice,customs as payment,due as dateS,custype AS TYPES from eta

    union select conTents,INVOICEDATE,invoice,[2nDpayment] as payment,due2nd as dateS,paytype AS TYPES from eta

    union select conTents,INVOICEDATE,invoice,advance as payment,DUE AS DATES,advtype AS TYPES from eta

    is there better way to do this especially for the column payment type instead of creating its own column for the three payments

    to look something like this in my table

    this is the net result

    I am A Medical Doctor

    Wednesday, September 6, 2017 8:06 PM

All replies

  • Hi MedicalDoctorProgrammer,

    your description is little bit confusing.

    first you said you had 3 columns in table.

    then you said each column have date also. so now there is a total 6 columns.

    then when you select the data in query, the columns you mentioned in the query are totally different then your description above.

    so I am not able to understand properly that how your table actually looks like and what output you want.

    can please again try to show us how your table and data looks and what exact output you want. it is better you post the picture of it. so that we can see it and try to understand it properly.

    if possible for you then you can also try post your database with dummy data in it.

    let us know about the details I asked above and we will try to provide you further suggestions.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 7, 2017 1:29 AM
  • The real issue here is the poor design of the table.  By having three separate columns for the payments, two for the dates, and three for the types, data is being encoded as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    Your UNION operation appears to be an attempt to return a result table of a structure closer to a correct one, but you would then have to decompose it to create a set of correctly structured related tables.

    Without knowing in more detail what is being modelled it is difficult to say much more than that, but having created a set of correctly related tables you would need to create a number of 'append' queries to insert data from the eta base table into these.  

    The first would insert distinct rows into a table which models the referenced entity type, which I think is probably Invoices.  The columns inserted should be those which are functionally determined solely by the table's primary key.

    If the corrected model requires only one referencing table I think you would then need three append queries, corresponding to the three parts of your UNION operations, each of which inserts the primary key of the referenced table  into a foreign key in the referencing table and values from a different subset of columns in the eta table.

    Once you are sure that the new tables correctly model the data, you would create an interface for data entry.  This would comprise an invoices form, in single form view, within which would be a subform, usually in continuous forms view, based on the referencing table.

    You might like to take a look at Decomposer.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to decompose a non-normalized table, containing data imported from Excel, into a set of correctly normalized related tables, with a brief explanation of the 'append' query being executed at each step in the process.  While the format of the data in my demo does not match that of your table, it does illustrate the basic methodologies for this sort of task.  In your case you would firstly need to insert data into a non-normalized table, using a UNION ALL operation similar to that which you posted as the basis for this.  This table could then be decomposed in the way that my demo illustrates.

    Ken Sheridan, Stafford, England

    Friday, September 8, 2017 6:07 PM
  • Hi MedicalDoctorProgrammer,

    I can see that you did not follow up this thread , after creating this thread.

    is your issue solved?

    if you got the solution by yourself then I suggest you to post the solution and mark it as an answer.

    if your issue is still exist then I suggest you to refer the suggestions given by the community members.

    if still you have any further question regarding same issue then I suggest you to let us know about that.

    we will try to provide you further suggestions to solve the issue.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 12, 2017 5:59 AM