Topic: Saving data to 2 tables

Hi,
I have a project that has a table that records the locations of a trip (Tripnum, Country, Location) and I want to have another table that is a summary of the first in recording Country, Location and the number of visits. Is this possible and if so can the summary table feed of the first. Meaning as the data is saved in the first table the second is  added at the same time ie before or after saving. Thanks for any help
Terry

2 (edited by derek 2019-11-05 14:29:38)

Re: Saving data to 2 tables

Hi Terry,
Not sure if you have a specific requirement for a summary table but if not, then I'd keep it simple and hold any summary information against country and location lookup tables as calculated fields (hope I've explained that correctly!).
Anyway, please have a look at the attached and hopefully it helps.
Derek.

Post's attachments

Attachment icon terrytrips.zip 339.04 kb, 289 downloads since 2019-11-05 

3 (edited by tcoton 2019-11-05 13:30:11)

Re: Saving data to 2 tables

You do not need to copy data to another table with a database, it is not like Excel. You just need to create a grid with a request to see the data already existing, data duplicate is never a good idea and certainly not a good use of a database.

Re: Saving data to 2 tables

Hi Derek and Tcoton
Thanks for the example and it answers a number of questions. The summary table was for a printed report listing only the countries and locations visited, not all countries or locations in the lookup tables for country or location. So is there a way to filiter out countries and locations not yet visited. I should have added that a trip can have many countries and locations. Thanks again for your input.
Terry

5 (edited by derek 2019-11-07 01:27:35)

Re: Saving data to 2 tables

Hi Terry,
If you don't want to see countries / locations that haven't been visited, it's probably better to use an sqlquery rather than calculated fields - have a look at the attachment as a possible alternative approach.
In this example, you can click on a trip no' (left hand grid) or a country or location (right hand grids) and the relevant details are retrieved.  I've not tested it (apart from checking it compiles!) so be warned - LOL!
I'm not sure what it will make of a trip that covers more than one country, but even if it falls down, the approach might still give you some ideas.
Regards,
Derek.

Post's attachments

Attachment icon trips4.zip 1.09 mb, 295 downloads since 2019-11-07