Hi,

I am half way there for a new script that I am doing. This part is where I am not figuring out how to do it. I have 2 tables that share a relationship 'by date". The main table has all the data in it and the other table I am needing to create a summary of all the data 'by date" and by item. The script for creating the new record and populating the data from one table to the other works well (with portals and such). However, in the main table, there are multiple records with the same item. What I was trying to figure out is how to automate is only create a record 'by date' and 'by item." That means if I have 25 pens, and 5 rulers, only 2 records would be created in the new table with the new date, one record saying 'pen' and the other saying 'pencil'. The summary table works really well once the word 'pen, and 'pencil' is typed in manually. I am wondering if I should first do a script to omit records that are in the same field "item' info, and then continue with the script to create new records in the other table, or if better to type incorporating the find unique item into a loop.

And advance would be great. I am pretty excited since the data sums up really well and creating records for each item per day in a new table, allows me to manipulate the data in other ways.

Any advance is greatly appreciated.

Relationships can be based on multiple fields, and in your example you should have DATE and ITEM as a combined key. The rest of it should look after itself.

Hi,

Thank you for your response. I do actually have that relationship set up already, so the field in the 2nd (sum) table fill out really well (but I have to type in the item and date manually, checking against the main database to see if I got all the right items for that date). In my scripting, I think I will 1st try in the main table to 'show only 1 record for each unique item" and then continue with the script to create a record per item / per date in the other table. I think it might be more simple than creating a loop that goes through the data in the main table searching for unique items that then triggers creating a new record in the (sum) table. I will write an update post on how it all turns out.

Bridge

I don't understand why you need two tables. It seems as if all you need is your "Main" table, and to create a subsummary report, by Date, by Item and perhaps with a found set.

I don't see why you need the second table - it holds no additional information beyond what's already in the "main" table. A report summarized by date and item (with no body part) should be all that's needed.

EDIT:

LOL, I should learn to type faster.

Edited September 4, 200916 yr by Guest