Appending Tables in query

[grenee]

Good Day All.

Can anyone tell me if is possible to combine two tables with similar fields, in a query, without actually combining the tables. The tables would appear like if they are union, but in actually fact the tables remains separated. I don't want to create a third table from the combined tables.

 

[arnelgp]

use union query:

select field1 from table1
union
select fieldx from table2

Edit: using Union Query will result of not updateable recordset.

 

[Galaxiom]

It begs the question why there are two tables.
It would be more normal to have one table with an extra field to designate the attribute that caused them to be in two separate tables.

Then you would not have the potential problem with a non-updateable query.

 

[grenee]

Thanks for your response:

"It begs the question why there are two tables."

Only last night I asked myself this question too; but here is the reason why: long ago when I started this database as a rooky these 2 tables were created. One showed customer deposits on orders and the other subsequent payment on invoices. In the end I would like to combine these 2 tables to get the sum of the total payments including Deposits and payments amounts.

It is really difficult to convert them to 1 table now, because the fields in these table have different names, and the other problem is that the Deposit table is linked with referential Integrity to the Orders Table while the Payments table is linked to the Invoices Table via Referential Integrity also. I guest the key fields in these tables would make it a night mare to combine them at this stage.

 

[grenee]

use union query:

select field1 from table1
union
select fieldx from table2

Edit: using Union Query will result of not updateable recordset.

Thanks.
Do you mean that the resulting query would not be editable? if so that's no problem, cause I am only trying to generate a report of the combined data - no editing needed. But I do not want to join the table at all.

 

[arnelgp]

do you record the Reference (invoice/orders) number when a deposit/payment is made.
if you have then you are good, you can reconcile your transaction.

 

[arnelgp]

you can also use (union query):

select customer, transDate, customerDeposit, null as payment from tblDeposits order by customer, transdate
union
select customer, transDate, null, payment from tblPayment order by customer, transdate.

 

[grenee]

do you record the Reference (invoice/orders) number when a deposit/payment is made.
if you have then you are good, you can reconcile your transaction.

Yes I do record them.

 

[grenee]

you can also use (union query):

select customer, transDate, customerDeposit, null as payment from tblDeposits order by customer, transdate
union
select customer, transDate, null, payment from tblPayment order by customer, transdate.

Thanks. This working, but how can I add a restriction of the data. E.g. Suppose I only want to return transType: Checks and exclude Credit Cards. I am asking this because the query window does not show the table lists to select from.

 

[MajP]

Is TransType in one or both tables? If in the tables then

select customer, transDate, customerDeposit, null as payment from tblDeposits
WHERE transType = "Checks" Order by customer, transdate
union
select customer, transDate, null, payment from tblPayment WHERE transType = "Checks" order by customer, transdate.

 

[grenee]

Thanks. This working, but how can I add a restriction of the data. E.g. Suppose I only want to return transType: Checks and exclude Credit Cards. I am asking this because the query window does not show the table lists to select from.

Please don't bother with this concern. It worked out as normal

 

[grenee]

Is TransType in one or both tables? If in the tables then

select customer, transDate, customerDeposit, null as payment from tblDeposits
WHERE transType = "Checks" Order by customer, transdate
union
select customer, transDate, null, payment from tblPayment WHERE transType = "Checks" order by customer, transdate.

Thanks for that quick response. Yes it works as you prescribed.

 

[grenee]

Thanks for that quick response. Yes it works as you prescribed.

Question: why did you use NULL?

 

[MajP]

That was @arnelgp, but he used a null as a empty field so that the final solution had the correct amount of fields.
When doing a query (especially a union) you can create fields with literals as well

Select FieldOne, "Some Text" as AddedField1, #1/1/2021#, as AddedDateField

That would create columns with the value "Some Text" and 1/1/2021 in every record

I actually would have done it different and put the values in the same column and a label in the next column

select customer, transDate, customerDeposit, "Deposit" as PaymentType from tblDeposits
WHERE transType = "Checks" Order by customer, transdate
union
select customer, transDate, payment from tblPayment, "Payment" as Payment Type WHERE transType = "Checks" order by customer, transdate.

This solution would look like

Cust1  1/1/2021, $100.00, Deposit
Cust1  1/2/2021, $200.00 Payment

 

[grenee]

Thanks for that quick response. Yes it works as you prescribed.

That was @arnelgp, but he used a null as a empty field so that the final solution had the correct amount of fields.
When doing a query (especially a union) you can create fields with literals as well

Select FieldOne, "Some Text" as AddedField1, #1/1/2021#, as AddedDateField

That would create columns with the value "Some Text" and 1/1/2021 in every record

I actually would have done it different and put the values in the same column and a label in the next column

select customer, transDate, customerDeposit, "Deposit" as PaymentType from tblDeposits
WHERE transType = "Checks" Order by customer, transdate
union
select customer, transDate, payment from tblPayment, "Payment" as Payment Type WHERE transType = "Checks" order by customer, transdate.

This solution would look like

Cust1  1/1/2021, $100.00, Deposit
Cust1  1/2/2021, $200.00 Payment

Perfect. Works fine. Thanks