Is there a better way to structure this...?

[Paul]

I've inherited a database that we're using to record stats. Basicly its got
customer details and a referance number and most importantly a few different
dates. Dates the work was recieved, date it was done, date it was sent to a
different department to be worked on, date it was recieved from them. The way
its set it seems to be ok. But when on those times it is sent to that
department the users have to go into another DB and enter basicly the same
stuff in a new record and when we get the work back they have to enter the
date in both DBs. Its redundent and I'm trying to find a way around it.
Other teams have to use the second DB and not ours, so thats the reason why
there has to be two DBs. Both tables have the same primary key, which is a
reference no.

I wanted there to be a why to update the fields on the first one and it
updates the other one. But that seems impossible.

Does anyone know a way around this, or a better way to structure it.

 

[Duane Hookom]

Do you understand how to link tables from other mdb files? This would allow
you to either update a single table or run updates from one table to
another.

 

[Paul]

Do you understand how to link tables from other mdb files? This would allow
you to either update a single table or run updates from one table to
another.

Yeah, I have that table linked to the backend on my db. Then what? I was
thinking an update query, but I want something that will update the other
table live.

 

[Duane Hookom]

A linked table is "live". We don't know your table or field names or what
you records might look like before and after updating.

 

[Paul]

When you say linked, do you mean file>get external data>link tables? Thats
what I've done. When I say live, I meant so that the data in the imported
one matches the data in my main table so soon as its entered.

Heres a quick list of the important fields in each table.

Imported table - [customer name] [ref number] [date sent for analysis]
[Date recieved] [closed]

Main table - [customer name] [ref number] [date open] [date first actioned]
[date sent for analysis] [Date recieved] [closed]

So if something they work on is sent off to the other department, then the
other db is updated and when it comes back they both have to be updated with
the same dates and again when its closed they both have to be changed again.

But theres gotta be a better way, right?

 

[Duane Hookom]

I don't think you understand the difference between "Linked" and "Imported".
A linked table "IS" the table from the other MDB.

I don't believe in keeping multiple copies of the same information however,
you should be able to create an update query based on both tables.

--
Duane Hookom
MS Access MVP

When you say linked, do you mean file>get external data>link tables? Thats
what I've done. When I say live, I meant so that the data in the imported
one matches the data in my main table so soon as its entered.

Heres a quick list of the important fields in each table.

Imported table - [customer name] [ref number] [date sent for analysis]
[Date recieved] [closed]

Main table - [customer name] [ref number] [date open] [date first
actioned]
[date sent for analysis] [Date recieved] [closed]

So if something they work on is sent off to the other department, then
the
other db is updated and when it comes back they both have to be updated
with
the same dates and again when its closed they both have to be changed
again.

But theres gotta be a better way, right?

A linked table is "live". We don't know your table or field names or what
you records might look like before and after updating.

 

[Paul]

I don't think you understand the difference between "Linked" and "Imported".
A linked table "IS" the table from the other MDB.

So could you explain it for me please?

I don't believe in keeping multiple copies of the same information however,
you should be able to create an update query based on both tables.

I know, I dont want there to be multiple copies of the same information, but
there has to be in this case. Are you saying that the only way to do this a
apdate query?

 

[Paul]

I don't think you understand the difference between "Linked" and "Imported".
A linked table "IS" the table from the other MDB.

Waitaminute! Of course I know this. I don't think you understand my
question.

Once the other table is linked to my databases can I have users update BOTH
tables at the same time? Yes or no. If yes then please explain how.

 

[Rick Brandt]

Waitaminute! Of course I know this. I don't think you understand my
question.

Once the other table is linked to my databases can I have users
update BOTH tables at the same time? Yes or no. If yes then please
explain how.

What is "both"? If you have multiple users linked to a common table they can
all perform updates at the same time (providing they are not trying to update
the same record).

If you mean you want to have a linked table and a local table and have one form
make changes to both tables at the same time then no, there is no automatic
mechanism for this. You would have to update one table the normal way and use
queries or code to copy the changes to the other table.