Table Formatting

[chrisjack001]

I have 2 tables (AccountActivity) & (Reqlog) in my database. I want to create a code to run through the (AccountActivity) table and based on the following conditions below add new tables to the (Reqlog) table.
The (AccountActivity) has the following fields:
Account


RequisitionNbr
Prefix
Trans Date
Voucher Nbr
Ref Type
These are the conditions.
If the RequisitionNbr field & Prefix field are Not Null, then search the (Reqlog) table for the same RequisitionNbr & Prefix. If missing from the (Reqlog) table, add that record from the (AccountActivity) table with the Account #, RequisitionNbr, Prefix & Trans Date in the associated fields of the (Reqlog) table. I also do not want to add multiple records for the same Prefix & RequisitionNbr as they frequently occur multiple times in the Account Activity table.
These are the fields for the (Reqlog) table.
AutoID
Account
Prefix
RequisitionNbr
Date
Cost
Can you please help me come up with a code that I could run that can accomplish these conditions.
Thanks

[weekend00]

You don't really need codes, a query can do this.

insert into Reqlog (Account, Prefix, RequisitionNbr, [Date])
select first(a.Account), a.Prefix, a.RequisitionNbr, first(a.[trans date])
from AccountActivity as a left join Reqlog as b on a.Prefix=b.prefix and a.RequisitionNbr =b.RequisitionNbr where b.prefix is null group by a.prefix,a.RequisitionNbr

Please note that account and [trans date] may not be consistant in some record. And I don't suggest you have account and date in table Reqlog.

[chrisjack001]

When you say I dont need codes but a query are u saying a select statement. I am new with this Access and VBA stuff. Can you break it down into a laymans term what exactly you want me to try with answer you sent. I am using MS Access 2007. Thanks

[weekend00]

Maybe I was not right, I usually think of VBA program code when I see "code".

I am not familiar with Access 2007, but I know it's layout is totally different from Access 2003.

Do you know how to create query in SQL view mode? Copy the "code"(eh, I say "code" too) and paste it then save the query.

Maybe you someone else can help you with more details with Access 2007.

[NTC]

"create a code to run through "

That is the wrong thought process in essence. Tables are passive. They store the data.
Queries then look at the specific data in combinations you specify.

One doesn't then write the results of a query to another table - ideally - if it can be avoided; because the results of that query are always available to you to find so that there is no reason for the complexity of a write method.

Your 2 tables seem very similar. In general - there should be only the need for one cross referencing field between the two - and no need for any other duplicate fields.

Finally, Access is 'event driven' - meaning that it presumes a human is involved let's say entering data and at various points (events) one can trigger logic. So in your post where you explain that if something is null then etc etc etc - - typically these tasks are implemented at the form level when the human is entering data. But that is not to say there are not legitimate needs where one triggers a one time batch style query to update tables. But from your post it is not clear what you really require.

Hope this helps a little.