Populate table using vba

[treva26]

I wonder if anyone can help me with this as I am new to ACCESS and VBA.

I need to populate my Jobs table using data from 2 other tables...I think it needs to be done in VBA but I am open to suggestions...

This is my idea of the subroutine structure:

repeat loop for each record in the ORDERS table

get the ID, PRODUCT and ORDER QTY from the ORDERS table

lookup the PRODUCT in the PROCESS table to get each process/job , time and cost (multiplied by qty)

Add a new line to the JOBS table for each process required (ie where STEP_X is not NULL)

The line would contain:

Job Number
Order ID
Job Seq
Process
Mins
Cost

Basically each Order of each Product requires a number of jobs/processes to complete it.
So I need a full list in the Jobs table of all the jobs/processes with thier times and costs.


Please see attached database.

 

[Oldsoftboss]

Sounds like you need a query, not another table

 

[treva26]

Sounds like you need a query, not another table

Yea you're probably right, but how can it give me mutiple rows for each product it finds? (one row for each process of making that product.)

I have restated the question in the Querys section here:

http://www.access-programmers.co.uk/forums/showthread.php?p=635109#post635109

 

[Oldsoftboss]

You need to normalise your tables. Have a look at the process table now. It has a relationship to a new "Process Step" table. If you click the little cross on the LHS of the process table, the process step will be displayed. I'm sure you can see already how this data can then be manipulated.

Post back with a bit more detail and we will take this further.

Dave

 

[treva26]

That is brilliant, thank you.

I had to make one query of orders then a jobs query based on that plus the steps.

I will try to keep my tables normalised in the future!

 

[treva26]

OK I thought I had a handle on it, but apparently not.

I realised there is nothing that is really needed in the original PROCESS table, and I would rather use the PRODUCT LIST table instead. But I cant seem to get the relationships right.

Can you talk me through how you did it?



PS- Love the avatar...mmmm

 

[Oldsoftboss]

Can you post a sample of where you are at ?

 

[treva26]

Yep here you go.
I have the table working correctly with the plusses on the LHS

But Im having trouble getting the relationships and Query to work.

So I need a query to tell me all the processes needed to complete every product that is on order.

Ideally it would also multiply the time and costs of each process by the quantity of the order too.


But like I said can you talk me through it too, since I cant download ZIP files here at work and I need to transfer the system to my full database too. This posted version is severley cut down.

 

[Oldsoftboss]

Will have a look when I get home tonight.

 

[treva26]

Its OK I got it, finally!

I think the problem was that I had lots of Fields called ID in different tables that had nothing to do with each other. Access kept deciding to link my Orders and Products by ID instead of Product Number as I told it.

So I renamed the ID fields to more specific names and its all working now.
I have also added the Cost and Time totals to the Query.

Thank you so much for your help