Merge Data Tables

[topcat10]

Hi all,



I have two tables of similar structure.

The first table is to be used by staff. Fields are; Transaction ID, Transaction Date, Food Item, Food Price, Quantity, Food Points, Total Points, Staff Member.

The second table is to be used by supervisors. Fields are exactly the same as above.

I need to merge the two tables together so that I can pull off a report for all transactions put through by staff and supervisors.

How do I go about this?

Many thanks in advance!

[June7]

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

Why are there two tables? Why not one? Could have another field to distinguish the two groups of records for filtering and sorting.

[ranman256]

If [Transaction ID] is an autonumber, then you will have to make some changes.
make a new field on both tables [OldTransID]
run update query to upd [OldTransID] from [Transaction ID]

run a make table query from STAFF tbl (but dont bring in [TransactionID]
run an append table to the new table from SUPERVISORS table. (again dont bring in [TransactionID]
create a new field call: [TransactionID] as autonumber

done

[topcat10]

Thanks ranman256. This is very helpful!

I am having some problems though when I am creating my make table query.

Some of the fields are calculated, and I am getting the error message "Calculated Columns are not allowed in SELECT INTO statements"

Any ideas on how to cross this bridge?

Many thanks!

[June7]

Don't select those fields for make table and will have to manually recreate the calculated fields after table is built.

[topcat10]

Thanks for all your help so far guys, it is much appreciated!

When I try and run my append query I am getting the error message 'Microsoft Access can't append all the records in the append query'.

[June7]

Post your query statement for analysis.

[topcat10]

INSERT INTO tblMakeTableqry ( [Transaction Date], DrinkItem, DrinkPrice, StaffMember, Quantity, DrinkPoints1, Promotion, CarryPoints )
SELECT tblDrinkUpsellRecordsSupervisor.[Transaction Date], tblDrinkUpsellRecordsSupervisor.DrinkItem, tblDrinkUpsellRecordsSupervisor.DrinkPrice, tblDrinkUpsellRecordsSupervisor.StaffMember, tblDrinkUpsellRecordsSupervisor.Quantity, tblDrinkUpsellRecordsSupervisor.DrinkPoints1, tblDrinkUpsellRecordsSupervisor.Promotion, tblDrinkUpsellRecordsSupervisor.CarryPoints
FROM tblDrinkUpsellRecordsSupervisor;

[June7]

Syntax looks good. Must be an issue with data.