Counting from two tables.

[Sporks]

Hello,

I am trying to compare two tables with different dates in them. One has the date where a product broke the other has a date where the product was sold.
What I want to do is count the dates where the product was sold in between when the products broke.

ex: Products Broke
1/01/01
1/01/02
1/01/03

Products Bought
1/02/02
1/03/02
1/04/02
1/04/03

So I would like to create a new table that looks something like

Product Broke Number Bought
1/01/01 0
1/01/02 0
1/01/03 3
Present 1


I tried doing something like this

DoCmd.RunSQL ("SELECT Count(Date.Broke)INTO ALTA FROM MathingEQN WHERE (Date.Broke) BETWEEN MathingEQN.[datebroke(0)] AND MathingEQN.[datepbroke(1)]")

Any help would be appreciated

 

[Atomic Shrimp]

Do the tables include an identifier by which you can ascertain which product was sold and when that specific product broke? (something like a transaction number, or in the case of multiple line transactions, a transaction number plus item ID?)

You need some way of identifying which breakage event corresponds with the item from which purchase event.

 

[Sporks]

I do have some other fields such as a Serial Number and a ID Number. What would I do with that?

 

[Atomic Shrimp]

You'll need to join the two tables (in your query) by something common such as serial number - then you can calculate the difference between the bought date in one table and the broken date in the other, for corresponding pairs of records.

 

[Sporks]

The thing is I need to count the date of the products bought because they could be the same date.

ex:

Broke
1/1/01
1/3/01

Bought

1/2/01
1/2/01
1/2/01
1/2/05

so the output table will look like

Date
1/1/01 - 1/2/01 3
1/3/01 - Present 1