Moving data from one DB to another

[SteveF]

Hi all. I have my database working nicely, but my solution to to the multiple-job aspect was to simply use a seperate database for each job. This works well and I have no issues regarding file size etc.

What I need to do now is compile consolidated reports from several of these seperate databases. I'm assuming another database will be needed and that one would simply be a reporting tool.

My question is how can I get data from a table within one database out to another where I can compile it into consolidated reports?

Many thanks in advance for help.

 

[pbaldy]

Personally I think it was probably a mistake to create separate databases, for this reason among others. You can create a new db and link to each of the others. You'd probably have to create a UNION query to pull the various tables into one for reporting. Each time you add a new database for a new job, you'll have to modify this database accordingly. I'd rethink the separate database decision.

 

[HiTechCoach]

I total agree that all the data needs to be in a single back end. Not only will reporting across jobs be difficult, you will find maintaining lookup table also very time consuming and very difficult to keep updated and accurate.

Keeping each job in a separate databases is like having separate spreadsheets. This also does not take full advantage of the power of a good relational database.

 

[SteveF]

I agree totally and can see that a single database would have had some advantages, but the multiple site / multiple client aspect was beyond me when I was working on it ( still is come to that ). I'm hoping to find the time to build a V2 and if I do then I would clearly design it a little better.

To be fair to me, it works very well as it is and does everything that I need it to do. It is a bit fiddly and high maintenance though.

Thanks for the tip abouth the union query, I will start there.

 

[HiTechCoach]

it works very well as it is and does everything that I need it to do.

From your original post, you have already found out that it is not really true that it works very well anymore. If it really does everything that you need it to do, would you have to be posting this thread?

 

[Simon_MT]

To be multi-job, try starting with a Dialog Form with one unbound control [Job] and opening the Job using the Dialog Form's control.

Simon

 

[SteveF]

From your original post, you have already found out that it is not really true that it works very well anymore. If it really does everything that you need it to do, would you have to be posting this thread?

As a stand alone, single site single job database it works very well as is. It is a little high maintenance, changing the VAT rate was a pain for instance, but consolidated reports is just the next step in its evolution and not beccessarily a major flaw in the original; it's not soemthing I need to do every day.