How can I create* a new table,
tableC, with field
id, populated with both
idB, (I'll be using the other fields for calculations on the combined data of both tables)?
Can you give an example of what you have in mind with some real data? Your tables can be set up with this kind of relationship:
TableA::IdA = TableC::IdA
TableB::IDB = TableC::IdA
as long as the value in all three fields is exactly the same value. If Table's A and B store different values, you really should use two fields in TableC, one for each relationship. You can get away with one field, but only if the values in TableA::IDA never duplicate a value in TableB::IdB and you DON'T need a link from A to B to C.
*and have this update when tableA and tableB have new id numbers
If the values change, then they should not be used to link related records, you should use auto-entered serial numbers that are always unique and that never change.
Of course there are exceptions to that, but you need to make every effort to avoid that if possible as setting up an update of primary key and matching Foreign key fields requires careful scripting and can result in major delays while large numbers of records are updated.
The database tables I have are (essentially) tableA has an ID for each sale of an apple as well as lots of other things specific to apple sales, wheras tableB has an ID for each sales of a banana as well as lots of other things specific to banana sales. I want tableC to have both apples and bananas and analyse their sales data. Apples have ID a1-a500 and bananas ID b1-b500, so I want tableC to have 1000 records with sale IDs running from a1-a500 and b1-b500 and then for it to pull sales data from the relevent related table (either tableA and tableB). Note there is also a table of all fruit (sometimes an apple is sold multiple times).
The ID values in tableA and tableB never change, but new sale (IDs) are frequently added.
I realise this could have been set up with tableC first and then relating to specific apple/banana sales data, but this isn't the situation I have inherited!
Note that using letters and not giving a detailed example has complicated not helped me in producing a suggested solution...
Yes, a single table that lists ALL sales for all fruit in a single table would seem to make more sense here. I recommend that you move in that direction if at all possible.
Sounds like you need a table with one record for each sale transaction with links to the fruit specific sales tables so that you can get combined sales data in a single report. Would that be what you want to set up here?
"Sounds like you need a table with one record for each sale transaction with links to the fruit specific sales tables so that you can get combined sales data in a single report. Would that be what you want to set up here?" That's exactly right.
I want to move in the direction of one central table in the future, but at the moment there are two seperate tables so I want to extract/copy with a script the IDs to relate to the sales data. I used letters as the detailed example is even more confusing...
You'll have to decide how to populate this unified table. For existing records, you can use import records to import data from the two separate tables in to the unified table. For new records, you can set up a script trigger that creates the related record in your unified table. Either method needs only to copy over the ID field of the fruit specific table.
Set up these relationships:
UnifiedSales::AppleSalesID = AppleSales::AppleSalesID
UnifiedSales::BanannaSalesID = BannanaSales::BannanaSalesID
Calculation fields can be written such as:
Case ( AppleSalesID ; AppleSales::SalesQty ;
BanannaSalesID ; BannanaSales::SalesQty )
To produce a field that you can total up with a summary field to give you both the total qty sold and also (if used in a sub summary layout part), the total sold of each fruit.
The above calculation assumes that you have ID fields of type number. If they are of type text, you may need to use Not IsEmpty ( AppleSalesID ) to determine which ID field in UnifiedSales contains data on a given record.