Refering to sepeate databases

[blower16]

Searching returned too many relationship questions so appols if the answer was staring in my face.

A = big company database (updated monthly by the big cheeses)
B = small "team" database (continually, manually updated by ground level staff)

on the first of the month A is updated

B contains various records from A, but doesn't link in any way (nothing changes when A changes)

I want to change this - I want a query whereby B looks in A, and updates:
1: the balance (how much the customer is in credit or debt)
2: the identifier (a number used to identify customer treatment - e.g 1 - customer is blind, send brail info, 2- customer always pays on time, 5% discount - this changes day in day out)

So lets say, for simplicities sake there are 3 fields in both databases, the 2 above and a number primary key.
How would I do it? bearing in mind both databases are seperate.

(note/edit)
also note I only want to run the query at the start of the month - as reflecting the info of A would result in the data in A+B being 31 days out of date at the end of the month (as opposed to just A)

(useless info )
The idea of this is to reinforce integrity in B at the start of the month - ensuring everything is correct, whilst still allowing workers to update the info during the month (allowing me to make sure the highest value debt is worked first)

 

[GabrielR]

In order to do that, there must be a clear relation between the two databases, like a field that will allow ACCESS to ID where the information is going to go.

After you have ID that specific relation between the 2 Dbs, set up a 3rd DB import tables from both Dbs and do Append queries between the tables from the Small Database into the Big Company one.

I have an application here that Open 4 different databases, 3 in the client's machine and One on a networked file server, Access is pretty good at that, and I combine info from one place to the other without any issues...

 

[blower16]

Hmm - Im a little confused - forgive my inexperiance but...

The "specific relation" between the databases is that Database A's "Balance" "Identifier" need to update DB B's "Balance" and "Identifier".

However - I would not know how to relate them? is that what your saying I need to do? form a relationship between A: Bal/ID and B: Bal/ID?.

Once these two database's two fields are linked (or not linked? Since i cant relate the fields? this is where im confused) I need to create a 3rd database?

I then need to "file -> get external data -> import" Database A (main table) Database B (main table) and create a query which appends A's Bal/ID to B's Bal/ID - THEN I need to ship the updated B - Main (table) back to my B database?

Seems very long winded.... Is their not a way to simply run a query which does not use a 3rd database, looks up A's Bal/ID and copies it to the same in B?

 

[The_Doc_Man]

You have to decide what happens during data conflicts (if any).

You description is not perfectly clear here, but it LOOKS like you might have a double-barrel problem here in that your process could potentially have to update TWO tables - the big-cheese table and the grunt table - with different subsets of records. Perhaps this is where GabrielR made his suggestion of an intermediate DB - where you could reconcile anything that needed reconciliation before having to do the updates. (If you have more than two DBs, you are in a real bind because you would have a three-way decision to make and a complex data re-distribution to manage.)

If the big-cheese DB says XYZ and, for the same exact record, the grunt DB says ABC, do you have a way to know who wins on a record-by-record basis? Without that knowledge, you will never succeed. Because without that knowledge, you cannot formulate hard-and-fast rules regarding the way that the updates have to occur. And Access won't work with sloppy rules.

Something you said hints that the big-cheese DB always wins, but that can't be entirely right unless the grunt DB doesn't retain ANY details of what you have done since the last reconciliation. In which case why does the grunt DB exist? That question is rhetorical for me - but NOT for you.

Theoretically, you can have any one of three situations per record. You could say "cheese wins" or "grunt wins" or "no difference." If you have any way to make a three-way match logic decision, you don't need the intermediate DB - but you might still need an intermediate "assessment" operation. The idea would be to add a working "reconciliation" marker per record in whatever DB needs it (see later). Mark the "no difference" cases. Then go back and for the ones that are different, decide which one supercedes or updates the other.

The mechanical way to do it is to build a linked reference from one DB to the other. From the way you describe it, the work will be done on the grunt side, so that is the side that must look at the big-cheese side. That is also the side that has to retain the special marker. Therefore, make a linked table (external table) from the grunt DB to the cheese DB. The cheese DB really never needs to know about the link, structurally speaking.

Now, in the grunt DB, run the scans to mark each GRUNT record with the required action. Once that is done, go back and run two queries - one to update the linked table (cheese table) and one to update the local table (grunt table). Finally, unmark the reconciliation markers until the next time you need them.

 

[blower16]

I think I've explained myself poorly - everything is way too advanced here (I often think ive been very clear and peeps dont understand on the net - so I know its my fault )

When you say "what data wins" - its the big cheese, when i click it (the update query) on 1/3/05 it wipes my records (only the mentioned fields) with its 100% accurate data.

The grunt database is accessed by ground level staff - the big database is where we import all our data from at the start of the month.

The big cheese does not update until the 1st - in this gap we need to constantley try to keep the records up to date by "working" them. Therefor - the grunt database has a 30 day advantage on the big cheese on the 30th of the month.

However - say we only work 90% of the work, 10% are now 30 days out of date. This is the 10% that need to say to the big cheese on the 1st "Ive not been kept up to date - tell me what the customers balance is".

I may be making a complex situation out of something you guys consider a simple import of a field - but I wouldnt know how to make sure that, when I import an entire field from the big cheese, the data corresponds to the customers name.

 

[neileg]

Let's see if I understand this. Your terminology is too loose.

At the start of each month you have a table (not a database) that comes from some central source and you use this to base your month's work on.

You want to maintain a second table that reflects changes during the month so every thing is more up to date.

Then when you get the next month's data you go back to zero in your second table.

So, the areas I think you need to look at are:
1) You need to make sure that entries in your 'grunt' table pick up an ID from your main table so there is a relationship between the two
2) You should not be updating the main table. You should create records (not fields) in the 'grunts' table that shows the change from the main table. You then extract the data from the two tables with a query that calculates the combined effect.

 

[blower16]

" you should create records (not fields) in the 'grunts' table that shows the change from the main table. You then extract the data from the two tables with a query that calculates the combined effect"

Why make new records!

Look -

1st of the month - Database A:

Account Number/Table A data/ Balance / ID
10000000--------------x-------£100----5
10000001--------------y-------£200----6
10000002--------------z-------£300----7

1st of the month - Database B:

Account Number / Balance / ID / Table B data
10000000----------£100---5----Richard
10000001----------£400---5----Andrew
10000002----------£150---5----Steve

Database/table B is wrong - how do I make database/table A update fields "Balance" + "ID" in Table B (Bear in mind there are different fields in A and B that means I cant just replace the record set)

Where I get confused:
"you might have a double-barrel problem here in that your process could potentially have to update TWO tables - the big-cheese table and the grunt table "

No - it does not update 2 tables, It updates one. Table A updates table B.

"Then when you get the next month's data you go back to zero in your second table."

Only "back to zero" in fields "Balance" and "ID"

------

If i was to do this all manually - id print out Table/DB A - cut out the balance + ID field - and paste it on top of table B's balance and ID field.

I can't be clearer unless sit and point to table A's balance - then point to table B's balance and say "make these the same today, but dont do it all the time - just when I say"

I need you to tell me how to setup an update query - and I need you to tell me how to relate the fields which need updating, even though they are in two different tables/DBs.

In my eyes (I could be wrong) this would not need anything more than an update query set up in the grunt database and a relationship making between both tables balance + ID. I just dont know how to do it.

I may seem frustrated/rude if I do im sorry - im just trying to make myself very clear.

 

[neileg]

You are thinking about this problem in terms of a spreadsheet, not a database. You want the data stored in the table to be exactly the data which is displayed. That's not how a relational database works.

Think about a bank statement. This shows your current balance as a series of transactions, starting from the opening balance and accounting for bankings and withdrawals. That's model you should be thinking of for your data. You need to store the transactions and then add them up to achieve your current balance.

 

[blower16]

"This shows your current balance as a series of transactions, starting from the opening balance and accounting for bankings and withdrawals. That's model you should be thinking of for your data"

What? no - just assume balance is a number. Your making it too complex. I am thinking of it as a spread sheet because it should be that simple - nothing is added up or calculated (at least in the fields I need to deal with). Its just like putting column A + B in one spreadsheet and pasting it into another spreadsheets column A + B. It doesnt matter if its addresses, a rocket science sum or a shopping list - i just want the contents shifting to the same field in a different database.

 

[The_Doc_Man]

Your making it too complex.

Other than the (entirely possible) case that some pointy-haired boss has said, "Thou shalt have an Access database to do this", you are describing a situation in which you don't need a database. Perhaps that is why we have the problem of communication. From "our" viewpoint (at least mine and, from the looks of it, Neileg's), your "B" data has no reason to exist unless it has meaning at some point during its life cycle. Yet you stay that A always wins. Why does B exist? Why not just use A directly?

In other words, if "B" doesn't exist to update "A" at some interval then "B" is a fifth wheel on a family car. Teats on a bull. Screen door on a submarine. That level of item. THAT is why we talk about "who wins?"

Your design as you describe it betrays elements of flat-file conceptualization better suited to a spreadsheet that periodically imports a new sheet from a master sheet. This isn't why you would use Access. Most of us have a lot of trouble "ratcheting back" our normalized, relational DB ways of thinking to this level. Please don't take this as a criticism of your business process. It is just that to us, this looks like you are going really far out of your way to do something - perhaps akin to killing a cockroach with an 8 kilogram sledge hammer. >splat<

If you don't take a transactional approach, you lose history. Now, if that is your business model, well and good. Lose it. But then perhaps you should rethink whether Access has any value to add to your situation.

 

[blower16]

As im inexperianced I will always assume you guys are right - whether I understand it or not is a different story

"unless it has meaning at some point during its life cycle" Yes it does! bingo - We are constantly printing accounts with highest balance to get worked first - the ID tells us which team is dealing with the account. We have queries in place that say "show me all accounts with ID 14, balance over £1000 and havent been worked in 7 days" we couldnt do that on the main database - because the balance on the 31st is way out - our staff check the balance day in day out - i remind you we cant edit the main DB so we have to use ours.

I do however give up.... if this doesn't clear it up then im going to assume its either not possible or I have a disease that makes me unclear.

If an update query cant do something so simple then I give up - if it cant copy a fields content and put it in another, I give up. Maybe I need a macro to delete my field "balance" go to main database - get that balance field, and paste it into my database - every month. Which I dont know how to do either.

The reason we dont use the main database is this is considered holy ground - we cant make our queries there, we cant manipulate data - its just a big pile of 100% accurate data used to supply every database. Each team has a database, with a form for staff to enter/modify data. These smaller databases contain extra fields specific to that team (for example customer care database has fields to describe if/how the customer is disabled, finanace DB has fields which describe how good a payer the customer is).

Therefor each small database is required for data input, data manipulation and extra specific fields. These small ones need to look to the large one for info at the begining of the month - for example BALANCE AND ID.

Do you now understand why we use small databases ? if we didnt, the main database would be cluttered with useless info (why would finance team need to know how disabled a customer is?), people would put in incorrect details, people would not be able to make queries to print work off in last worked order. it basically doesnt belong to us as a team - we can only look at it - and retrieve data.

A spreadsheet wouldnt be as presentable as a form, or as easy to view and navigate, or as multi-user friendly - a spreadsheet wouldnt be able to have a query run to show all accounts which have not been worked in a month.

 

[neileg]

What? no - just assume balance is a number. Your making it too complex. I am thinking of it as a spread sheet because it should be that simple - nothing is added up or calculated (at least in the fields I need to deal with). Its just like putting column A + B in one spreadsheet and pasting it into another spreadsheets column A + B. It doesnt matter if its addresses, a rocket science sum or a shopping list - i just want the contents shifting to the same field in a different database.

Ok, you are insiting that you want help to do this the wrong way. So be it.

You have to clear up some confusion.

You keep referring to A as a separate database. What is it exactly? Is it truly a separate database (I suspect not)? Is it an extract from the main database that you have imported as a table in your local database?

If the latter is the case, this is how I would tackle the problem (bearing in mind the design constraints you are imposing).

Forget the table B that you currently have and look at actually changing the data in table A. Create a new table B that contains the extra info that you want, customer disability, etc. Make sure that this has a field that will contain the customer ID from table A. Also add in some date fields that will hold the dates that correspond to you updating the data in table A.

Use a query to join A and B. Create a form based on that query that shows all the data you are interested in. Use some simple code that writes the current date to the relevant field, eg the balance, when you update it.

When you get the new table A data next month, overwrite table A and set all the date fields in B to null. Table B should still contain the special data you need and so long as the customer IDs haven't changed will link to the new data in table A without any further input from you.

At any time, you can look at the date in table B to see when the data was last changed. If the date is null, you know it hasn't changed since the last table A data was imported.

If my assumptions are correct, I'll knock up a simple sample database to show you what I mean.

 

[blower16]

"You keep referring to A as a separate database. What is it exactly? Is it truly a separate database (I suspect not)? "

"A = big company database (updated monthly by the big cheeses)
B = small "team" database (continually, manually updated by ground level staff)"

Yes it is

"Forget the table B that you currently have"

So your saying replace a database (essentially a database since this is the only table) with all our records in?

"and look at actually changing the data in table A"

"The reason we dont use the main database is this is considered holy ground - we cant make our queries there, we cant manipulate data"

And you say change the data in A?

"Create a new table B that contains the extra info that you want, customer disability, etc."

So replace my working table B with an identical one?

"Also add in some date fields that will hold the dates that correspond to you updating the data in table A"

Why?


Sorry - I don't mean to be ungreatful, its clear your trying to help me and i'm not getting something simple to you guys, the pain of being a newbie .

Thanks for your time - but im going to do it manually now by putting all accounts in alphabetical order in table A, all accounts in B, then pasting A into B.

 

[neileg]

It's a little clearer, now.

I think you need to get the data from A into B. You can either link A or copy and paste it into your database. You would then have a table that holds the data from A. You then need another table in the same databse that holds your amendments as I described before as table B. When you have the data from the main database and the data you have entered locally, then you can join the two data sets together.

I had assumed (wrongly) that you already had the two sets of data in the same database. You seemed to use the terms database and table interchangably but perhaps I was mistaken there, too.