combining information to create report

[Versace77]

This may be answered already, but i may be searching using the wrong lingo.

Here's the situation:

I get a list of bills every couple of months. Everytime a new list comes, i
need to reconcile it with the previous list while also adding information.
The new list may have new bills while it can also contain bills that were
listed previously.

There are 5 columns of information, 'bill number' being unique to each bill.
The old list of bills has a column titled 'country' that has been annotated
(by previous individual who did the work), The new list of bills doesn't
have the country names. In past practice, the individual, would eyeball, and
compare the old stuff with the new stuff and copy and paste the country
information where appropriate. This took hours as there are hundreds of
bills to do this with.

I'm looking for help with having Access, compare the two bill lists, fill in
the country column where needed, to ultimately generate a report that shows
the combined, consolidated listing where each bill number appears once.

Hope this isn't too confusing.

I appreciate any help, please let me know if can be more specific with the
request.

thanks.

 

[Jake]

First create three tables
1. Bill - hold reconciled bill info - Make sure you assing your unique
number as the primary key
2. Bill Temp - hold current month bill info for manipulation and
reconciliation, unique number is primary key
3. Country - Key, Name Etc..

Second use the import function in Access to import you monthly bill
information into Bill Temp

Third create an update query joining Bill Temp and Country update all
records in Bill Temp where country is null and foreign key in bill temp is =
country key. I'm not sure what key info you are using or I'd be more specific

Fourth I'm not sure what you mean by reconcile, but you could probably build
a query for that as well. You could easily build a form for the temp table
to do it manually though.

Finally create an append query that joins Bill Temp and Bill, make sure you
have all records from Bill Temp where any bill field is null. This will move
over all records that don't exist in Bill

These steps will import you data, update country, and move all data to Bill
without duplicating. All this should take about 3 minutes to run once its
coded.

John

 

[Versace77]

John, thanks for the response. i'll try to clarify more. I like your idea
of 'bill' and 'bill temp'. however there is no known country listing. for
the few bills added during each cycle that don't match an already outstanding
bill, the country has to be manually looked up and added. However, for the
hundreds of bills from the new new 'bill temp' listing that match in bill
numbers to the bills in the 'bill' table, it would save a great amount of
time.

By reconcile, all i meant was combining into one area, the bills from the
new listing to the previous listing and weeding out duplicate info while
having the country added where applicable and making sure that each unique
bill is present.

here's a quick visual example (hope it appears fine after it's posted):

tblbill:

a bill c d country
xxx 95869 xxx xxx jamaica
xxx 57489 xxx xxx holland
xxx 584848 xxx xxx australia

tblbilltemp:

a bill c d country
xxx 95869 xxx xxx
xxx 584848 xxx xxx
xxx 7756 xxx xxx

i'd like the output or recordset to look like this:

a bill c d country
xxx 95869 xxx xxx jamaica
xxx 584848 xxx xxx australia
xxx 7756 xxx xxx

note how 95869 and 584848 are in the final output just once with country
name. also 57489 is not in the output because it had cleared and was not
present in the new listing. lastly, the new bill 7756 appears in the final
output with null country name as it did not match anything in tblbill.

thanks again.

 

[Jake]

For the manual process you can create a form that select all bills from the
Bill table that have a null country. In that form you could populate the
country combobox with all values from the Bill table for the field country.
That would give your user a populate drop down and they could type in if the
correct country needs to be added to the list. That would prevent users form
having to search for the nulls and make the manual part of this process most
efficient.

Bill number is unique, but I would imagine there is other data that could
synch the correct country to other key info, such as comapany name or id.

As far as reconciling I would create an append query joining the two table
Bill and BillTemp. Ensure your join is set up so all records from Bill Temp
are Present. In the query make sure the key field BillNum in selected from
the Bill Table and append where Bill.BillNum is null. This will join the two
tables and anytime there is a record in BillTemp that doesn't match key info
in Bill append all the field to Bill.

Hope this helps.

On a side note, most companies I work for have a customer list somewhere
with address info. If the one you work for doesn't they should and you
should be able ot link / dump the data to automate the country process. In
other words if they don't I would reccomend creating a system to input
clients and manage them. Then the country info is inputted on the front end
and you can create a little CRM. Fun Fun.