Is a multiple union select query possible in access

[Gaza]

Hi all,
I am working with an access DB from an application whos structure I do not want to change (its not mine).

I need to extract some info and arnt sure of the best way to go about it, sql, ADO, DAO.

Basicly i have a db that is created from info in a current db and an archive db. In the Mirror db the tables i want to work with are,

Current_Dockets "C_Dkt"
Archive_Dockets "A_Dkt"
(Both share the same structure, I will be returning 3 fields from this table)

Current_Docketline "C_DktLn"
Archive_Docketline "A_DktLn"
(Both share the same structure and have a relationship with their respective Dockets tbl through the DocketID, I will be returning 8 fields from this table)

Stock "Stk"
(Has a relationship to the StockID in the docket line table, I will be returning 3 fields from this table)

Supplier "Sup"
(Is related to the stock table through the SupplierID, I will be returning 1 field from this table)

I have been able to extract the info on one set of tbls (C_Dkts, C_DktsLn, Stk, Sup) with the following query but now need to do UNION SELECT to the A_Dkt And A_DktLn.

SELECT Current_Docket.docket_id, Current_Docket.docket_date, Current_Docket.transaction, Current_DocketLine.cost_ex, Current_DocketLine.cost_inc, Current_DocketLine.line_id, Current_DocketLine.sell_ex, Current_DocketLine.sell_inc, Current_DocketLine.quantity, Current_DocketLine.rrp, Current_DocketLine.stock_id, Stock.barcode, Stock.cat1, Stock.cat2, Stock.description, Stock.supplier_id, Supplier.supplier FROM Recent_Docket, Recent_DocketLine, Supplier INNER JOIN Stock ON Supplier.supplier_id = Stock.supplier_id WHERE (((Recent_Docket.docket_id) = `Recent_DocketLine`.`docket_id`) AND ((Recent_Docket.docket_date) BETWEEN mvarStartDate AND mvarEndDate) AND ((Recent_DocketLine.stock_id) = `Stock`.`stock_id`)) ORDER BY stock.supplier_id;

I can create the Union query for each the Dkt tbls and DktLn on there own and they are availiable in the Mirror table as stored access query's.

Is the Query I require possible, or will i have to use ADO or DAO to get at the stored union querys in access?

Gaza

 

[jhall156]

You can easily create union queries in access. Just type them in as SQL. The usual caveats apply, the column widths and types must be identical etc. Then you get to see the little union query symbol in your database window, Yea! JHall

 

[Gaza]

To do the UNION on say the Current_Docket and Archive_docket is easy, my prob is how to incorporate the union's in the above query>

 

[mymou]

Hi Gaza

You can still you a union query - but ensure that all comumns have the same naming convention. If a column doesn't apply for one table - then stick it in anyway with a null value.

eg

SELECT docket_id, docket_date, transaction FROM table0
UNION ALL SELECT docket_id, d_date as docket_date, transaction FROM table1
UNION ALL SELECT docket_id, docket_date, "" as transaction FROM table2

I cant check the syntax now - but you should get the gist.


Stew

 

[Gaza]

G'day Stew,

Ta for the advice I'll play with this an let you know how I get on.

Gaza