Using Field contents to select which field to populate

[urielsmemory]

Greetings,
I am building a forecasting tool and due to the input restrictions imposed by the users have the following problem.

I have a table (tblA) with the fields: ProductID, Qty shipped, Ship date. (Example row: 1, 50, Jul07)

I have another table (tblB) which contains information on the same product, but with the following fields: Product ID, Jun07, Jul07, etc (Example Row: 1, 5, 10, etc).

I need to write a query that draws of both these tables and aggregates. I would like to build table C, with fields: Product ID, sumJun07, sumJul07, etc. (Example Row: 1, 5, 60, etc)

I am not very familiar with SQL or VBA. Is there a way to make access do this without customer writing something? If not, is there a quick clean piece of code that could help me do what I need to?

WJW

 

[lespaul]

Is it still possible to change the table structure? Your tblB isn't normalized and is part of why this is not an easy aggregate crosstab query...

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[urielsmemory]

The issue is with the entry forms. tblB is pushed into a form that is displayed on the intranet for data entry. We explored trying to use a crosstab query to create the same form but the problem was that it was not editable in the form since it drew from two tables. (one that houses the product information and one that houses the date information) Any other data structures that would support an effective input form and yet be manageable on the backend?

 

[lespaul]

What information are you trying to edit?