I want add two fields from differnet tables into one?

[offie]

The 2 main tables that I am working with are ChangeOrder and FieldOrder. I want to make a table (Orders) that has both.

They are set up like this:

ChangeOrder:
ChangeOrderNumber (ID)
ProjectCode (Can be multiple)
ActualAmount ($)
IssueDate

FieldOrder:
FieldOrderNumber (ID, but somehow there are repeats. Ex: One project can have FOCodes 1-6)
ProjectCode
ApprovedAmount
DateAssigned

I want to have a table that has
Orders (FieldOrders and ChangeOrders)
ProjectCode
Amount
Dates

I literally just want to put one underneath another, combining the respective fields, how can I do this?

Thanks!

[June7]

Maybe you want a UNION query. There is no wizard or designer for UNION. Must type in SQL View of query builder.

SELECT ChangeOrderNumber AS ID, ProjectCode, ActualAmount AS Amount, IssueDate AS Dates, "Change" AS Source FROM ChangeOrders
UNION ALL SELECT FieldOrderNumber, ProjectCode, ApprovedAmount, DateAssigned, "Field" FROM FieldOrders;

[offie]

The issue I came up with while trying this is that the ChangeOrderNumber is text and the FieldOrderNumber is a number, I can't change the field types in the tables because they are linked tables, do you know how to change the type?
Thanks for the union code!

[June7]

I tested that scenario and the query works. The ID field is treated as text. Why is this an issue?

[offie]

Wow thanks, sorry I must have been doing something wrong, I was using the tables that had the relationships present and it was adding code

Thanks!