Hi all, I am new to the site and would appreciate any help.

I have set of data I am having trouble with. I have one table which contains a series of “Assignment IDs”, products and the dates for which those products were matched to those Assignment IDs. The second table is grouped by product along with dates for which revenue was earned. I want to match up the revenue with the assignment ID based on the date when that product earned revenue.

Any ideas?

Without knowing the structure of the tables, it's hard to say. I'm also not familiar with Access syntax, but I do extensive work on SQL databases, and the principle should be largely the same.

In general, you'll do a join on the two tables using the product ID as the common index. Select the data you want (date, assignment ID, and revenue) from that joined table, and group by the date... this will give you the total revenue for each Assignment ID on each date that it has activity.

Your relationships are unclear here. You have a table with products, and assignmentID and an assignmentDate. Then you have another table with ProductID, Revenue and RevenueDate, is that correct?

So the two tables would be related on ProductID. But I don't see how the Assignment comes into play.

You can easily get a listing of revenue by product but I don't see how you bring assignment into play.

Let me restate a little so it is more clear. The first table is grouped by Assignment ID. There can be many products that have the same assingment ID. That product is assigned to to that Assignment ID for certain period. It has an Assignment Start Date and a Assignment End Date.

The second table contains product ID information along revenue earned on a particular day. The relationship between the two tables is product ID.

What I want to do is create a third table which has revenue grouped by assignment ID. The problem is that I have to account for the date the revenue was earned in the order to choose the correct assignment ID.

The method I gave should give you that:

Join the two tables based on product ID (that will associate assignment IDs with revenues)
Group by revenue date (this will give you revenue for each assignment ID for each date)

Or, if you're simply looking for total revenue per assignment ID...
Join the tables as above
Group by assignment ID. This will give you the total revenue for that assignment ID.

You want to avoid creating anything other than a temporary table for this: Having the same data in multiple places is the most quick and most effective way to end up with invalid data, as updates have to be done in multiple places for a single piece of information

Table 1:

Assignment ID Prod ID Assignment Start date End Date
1111111111 Product 1 1/1/07 2/28/07
1111111111 Product 2 12/1/06 1/15/07
2222222222 Product 2 1/16/07 2/28/07


Table 2
Product Earn Date $
Product 1 1/16/07 300
Product 1 2/12/07 450
Product 2 1/5/07 200
Product 2 1/20/07 300

In the above example, the relationship between the two tables is Product ID. The problem in this example is Product 2. Depending on which date the money was earned, it could be either in either assignment ID. What I want to do is have some kind of if statement where if the "Earn Date" is between the Assignment Start Date and the End Date, I use the corresponding Assignment ID. I cannot just group by Assingment ID and Start date.

Ok, First, tables are not "grouped". Only reports and queries are grouped. Second, in a relational database, you don't duplicate data, so you don't want to make another table, just use a query.

So, what you do here is create a query between the 2 tables and join on ProductID. In the Earn Date column set the criteria to:

BETWEEN [Start Date] And [End Date]

That may restrict the records to those that match the assignment range.

If that doesn't work, Zip the file with some samplke data and attach it to a post here and I'll take a look at it.

I have attached a sample database that highlihts the issue I am facing. I am trying to group earnings by Assignment ID. One table contains Assignment ID, the products associated with that assignment ID and the relevant dates. The other table contains the product information and the date and amount earned. The issue I am having is that the product can be assigned to different Assignment IDs depending on the date. I appreciate any all help.

Thank you

Did you try what I suggested? It seems to have worked when I tried it.

All right it worked. I had a problem with some null values but I was able to take care of them. Thank you

Glad to assist