I have a business project I have been working on for several days. I am fairly new to VB so I need some good advice on how to approach the solution.

Our company is engaged in producing asphalt. Asphalt is produced and trucked to the job site. At job site the asphalt is consumed. We need to be able to compare tonnage that have been produced at the plant to tonnage that has been consumed at the job. We need to do this by date and job number.

1. Asphalt plants (8 of them) run on Advantage data base. Each plant has its own data base containing a SalesTicket table that contains asphalt produced.
2. Job site runs on sequel server and consumption is recorded daily wireless
3. User will be able to compare tonnage produced (at plant) and tonnage consumed at job. There could be the case that tonnage is produced and never consumed or tonnage been consumed with no record of being produced.
4. User will be able to view tonnage by date and job and selecting data from one or more plants.
5. If user selects more than one plant I need to connect remotely to multiple plants.
6. The finished display should be something like this:
Date | Job | Asphalt Produced | Job Consumed

So, how can I best consolidate all the information from plants and compare to what’s been consumed at the jobs? I tried to use temp table in a sp but that does not work since I call the sp multiple times. I need to be able, somehow, to build a table with all the plant information and then compare to job information.

Any good ideas????

Are you gathering all the data into a central database?

In #2, you say "Job site...". Presumably there is more than 1 job site. If so, are these job sites using the same system or does each site have its own system?

-Peter

Peter:
The Job site runs on sequel server and uses the same data base for all job sites ie a single table.
I am already bringing in data from all the plants into a single data set, but from here I'm not sure where to go go .... cal I call a sp and dump my data set into a temp table and then compare the temp table against the job site table?

It would make sense to me to bring all the plant data into a permanent table then you can just query the two tables to make your comparisons.

-Peter

Yes I could use a permanent table except for if several users run the prgram at same time I could get a conflict. Is there a way to lock a table for update from others users while is use?

If you are doing this all in SQL Server then you don't need to worry about that. SQL Server can handle lost of concurrent connections and actions against the same tables. The problem you are more likely to encounter would be when you are trying to update the same record simultaneously. These kinds of problems fall under the "concurrency management" category. However, you are dealing with what I would consider mostly static data in that your plants and job sites are adding data but not changing existing data. Then you are simply looking to query the existing data.

You'll need to devise a data scheme such that data coming from two sources (2 plants or 2 job sites) won't conflict. Without knowing your current schema I can't say whether this will ever actually happen. But consider this scenario: You have stated that your plants run separate systems. There is a SalesTicket table. Presumably there is some record ID that numbers records. Two plants will likely have records with the same ID but they are certainly different records. In your central system you'll need to further identify the combined records to eliminate ID conflict. This would be easily accomplished with an additional column: PlantID.

-Peter