I am working on a project where I am tracking Jobs, products for the jobs, and the number of items shipped for the products. The shipments are not all done on the same day, so I had to create a table to store the shipment quantities in. This means that I have three tables. One called Jobs, one called Job Products, and the last called Job Product Shipments. I am able to display the nest inside the next with no problem, but what I need to do, and where I am having a problem, is to sum the product quantity with the sum of the total quantity shipped for that product.
The Query would have to be a server connect Query of Jobs, a repeat of job products, and inside the repeat of job products, another repeat of job product shipments.
Job Products are tied to Jobs using the JobID field, and Job Product Shipments are tied to the Job Products table using the JobProductsID.
I have tried setting values in a server connect and couldn’t get it to work.
Table One: Jobs
Field: JobID
Field: JobName
Table Two: JobProducts
Field: ProductName
Field: QuantityOrdered
Field: JobID (tied to Jobs table)
Table Three: ProductsShipped
Field: JobProductID (tied to JobProducts table)
Field: Quantity Shipped
So, I have a nest inside of a nest. The results would look like this:
My New Job…
Product One
Quantity Ordered: 10
Shipped: 2
Shipped: 4
Shipped: 3
Product two
Quantity Ordered: 5
Shipped: 1
Shipped: 4
New Job two…
more stuff…
and so on.
Any ideas will greatly be appreciated.