:confused:Table ATable C KeyA-DateA-QuantityEarlyOn TimeLateOut of ScheduleA1K12008-05-14280280A2K12008-05-14380380A3K12008-05-14240240A4K12008-05-14500200300A5K22008-05-145050A6K32008-05-1435030050A7K42008-05-14260260Table BTable D KeyB-DateB-QuantityA1A2A3A4A5A6B1K12008-04-13300-28020-20B2K12008-04-28300-300B3K12008-05-03300-60240-240B4K111/05/2008300-300B5K123/05/2008250-200B6K22008-04-20100-50B7K22008-05-13100B8K32008-05-18300-300

You can also see this tables in attachments.

There are two tables of records, both of them contain Key, Date and Quantity. Now I need to compare each record in table A with table B which have the same key, in order to judge how many A-Quantity is early, late, on time or out of schedule. The result is in Table C, the check process is in Table D. And we give ±3 days as a tolerance, which means if B-Date is 2008-05-07, as long as A-Date is between 2008-05-04 and 2008-05-10 it is considered as on time.

Recoreds in table A all have the same Date, and records in table B are sorted ascending by Date.

For record A1 in table A, the Key is K1, date is 2008-05-14 and quantity is 280. The first record of K1 in table B is record B1, whose quantity is 300. A1’s 280 comes on 2008-05-14, comparing to B1’s B-Date 2008-04-13, it is late, so put 280 under ”Late” in Table C. Obviously record A1’s 280 quantity can’t cover all of B1, there is still 20 left, we leave it to the next record to cover.

For record A2, A-Quantity is 380, first it should cover the 20 left from B1, and then cover 300 of B2. The rest 60 of 380 can give to record B3. Now compare A-Date with B-Date of B1, B2, B3, A2 is late for all of them, so we put all these 380 into “Late” in Table C.

The same story for A3, it covers B3, but A3’s A-Date is 2008-05-14, still late than B3’s B-Date 2008-05-03, so A3’s 240 fall into “Late” category.

Now check record A4 in Table A, according to Key in both table, it can cover record B4 and part of record B5. For B4’s B-Date, A4 is on time, so we put 300 to “On Time” category in Table C. But for B5’s B-Date 2008-05-23, A4’s A-Date 2008-05-14 is too early, so we put these 200 into “Early” category

For record A5, it only fills 50 to B6, and this 60 falls into “Late”. What we should notice is that there are totally two records of K2 in table B, since we can’t find any other K2 record besides A5 in table A, just leave B7 open.

For Record A6, after it covering B8’s 300, there is still 50 left, it has nothing to compare with, so we put it into “Out of Schedule”.

And Record A7, there is no record of K4 in table B, then the whole 260 quantity falls into “Out of Schedule” category.

There are around 2000 records in table A which needed to be checked one by one.

The whole process is so complex, and I’m just a beginner, really look forward and appreciate for your help!!

Your explanation of your problem is a complex problem. What is the database designed to do? What is your problem in 2 or three sentences?

don't get me wrong, i want to help, not piss you off.

Your data is incomplete... You cannot find out what belongs where...

You say A6 and B8 (from your spreadsheet) belong together but there is no means to link the 2 sepereate records to eachother. You need some kind of order number or something to be able to link the data.

Linking what is currently your "key" field will not do... This means you cannot do this in a query...

If there is any solution to this without addition(s)/change(s) to the dataset, the solution is going to be in coding...
This coding would create something that would look like your Table D. Then a query could build Table C for you....
But it is not THAT easy to do it.... but not THAT complex either...

The purpose of this database is to check received quantity&date with demand quantity&date. Table A is the data of receiving, Table B is the data of demand.



Your explanation of your problem is a complex problem. What is the database designed to do? What is your problem in 2 or three sentences?

don't get me wrong, i want to help, not piss you off.

I said it's complex because I can't make it without coding, and I've no idea about how to code....

Your data is incomplete... You cannot find out what belongs where...

You say A6 and B8 (from your spreadsheet) belong together but there is no means to link the 2 sepereate records to eachother. You need some kind of order number or something to be able to link the data.

Linking what is currently your "key" field will not do... This means you cannot do this in a query...

If there is any solution to this without addition(s)/change(s) to the dataset, the solution is going to be in coding...
This coding would create something that would look like your Table D. Then a query could build Table C for you....
But it is not THAT easy to do it.... but not THAT complex either...

I said it's complex because I can't make it without coding, and I've no idea about how to code....

I can help you along but you will have to code your self...

Lets start by opening a recordset...
You can open a recordset like this:
Dim rs as dao.recordset
set rs = currentdb.openrecordset ("Select * from tableA")

You will need to open both your "input" tables and your "output" table.

You end with rs.close to close the recordset and set RS = nothing to clean everything up.

I've drawn a logic flow chart of this whole process (see attachment), which cuts this whole task into pieces. Now the next step is to use code to actualize these pieces, which is hard to me :( .

I'll report my further steps here, meanwhile really looking forwad for your help, about the logic, about coding, whatever will be fully appreciated!

Yeah, thanks!
Just like this, then I can learn it step by step!


I can help you along but you will have to code your self...

Lets start by opening a recordset...
You can open a recordset like this:
Dim rs as dao.recordset
set rs = currentdb.openrecordset ("Select * from tableA")

You will need to open both your "input" tables and your "output" table.

You end with rs.close to close the recordset and set RS = nothing to clean everything up.

I have glanced at your flowchard and it looks more or less OK, making a flow like this is often a good idea if you thing a problem is complex to break it down into smaller bite size chunks...
Tho your thought process is wrong... To "excel like" not (enough) "relational database like"

You dont put quantities into a column "Early" this is doing things "de-normalized". Putting it into early category is something you do later on in a query after you have "fixed" your problem.

Your problem is in your data, no way to relate tableA to TableB, once this problem is fixed (via coding) you can run queries to get your reports to find out how much is early/late/on-time.

First lets get those recordsets open and closed properly, like I said you need 3.
2 for reading: A and B
1 for writing to fix your problem: D

The problem is exactly like this, how can I "fix" these two tables?? Is there some code can make it "find the first match record" or something??


I have glanced at your flowchard and it looks more or less OK, making a flow like this is often a good idea if you thing a problem is complex to break it down into smaller bite size chunks...
Tho your thought process is wrong... To "excel like" not (enough) "relational database like"

You dont put quantities into a column "Early" this is doing things "de-normalized". Putting it into early category is something you do later on in a query after you have "fixed" your problem.

Your problem is in your data, no way to relate tableA to TableB, once this problem is fixed (via coding) you can run queries to get your reports to find out how much is early/late/on-time.

First lets get those recordsets open and closed properly, like I said you need 3.
2 for reading: A and B
1 for writing to fix your problem: D

I can help you along but you will have to code your self...

Lets start by opening a recordset...
You can open a recordset like this:
Dim rs as dao.recordset
set rs = currentdb.openrecordset ("Select * from tableA")

You will need to open both your "input" tables and your "output" table.

You end with rs.close to close the recordset and set RS = nothing to clean everything up.

Have you been able to get the recordsets for the tables opened and closed?

yeah, I think so, you want to see the code?

The next step would be to add an auto number Primary key to both your A and B table.
Then loop thru your table A using the movenext method of the recordset.

Thank you very much, the problem is already solved, basically follow the flow chart and what you are right is that I create a new table C to store the results, which makes whole process much easier.

The next step would be to add an auto number Primary key to both your A and B table.
Then loop thru your table A using the movenext method of the recordset.

Wow that went quick, glad you got it working :)

yeah, thanks to an expert and you, I can't wait too long for the time limit, though still need to learn a lot, I can see that I'll come back to ask some other questions.