HELP please!!!

[Winnipeger]

Im a NEWBIE here! Would appreciate your help

I do have two tables (Job Order and Production)

I would just like to keep track of the production quantity of each job orders based on the production table summing up according to sequence number. And at the end answers YES to Completed? if all meets the order quantity amount.

I have attached the tables and sample result(table or query) i would like to get.
Please do ask me if anything confuses you.

thank you in advance!

 

[vba_php]

Winni,

I think you can set your stuff up in more than one way, but this is what I would do:

TABLE 1 ("joborder") fields
id (pk) / joborder / component / description / qty

TABLE 2 (named "production") fields
id (pk) / joborder (fk to table1 field) / proddate / staffname

TABLE 3 (named "sequences") fields
id (pk) / productionID (fk to table2 field [id]) / sequenceNumber / pqty

all of this will allow you to query out all of the sequences, and whether or not they have matched the quantity listed in the job order. plus, you can also run aggregate queries (math operations) with this set up. take from it what you will...

 

[AccessBlaster]

You probably do not need the "Result of JobOrder" table, its not normalized and un-necessary. The updated results would be suited for display in a form or report, but probably not stored in a table.

Also I would rename the ID's to mean something like JobOrderID. The word Description is a Access reserved word

HTH: good luck with your project.

 

[Winnipeger]

thanks guys for the prompt reply!

However Adam, can you please elaborate more.
Im new to Access and needs more guidance .

thanks in advance

 

[vba_php]

However Adam, can you please elaborate more.
Im new to Access and needs more guidance .

if you want to go with me, be warned, I'm not a professional. but none-the-less I can hold my own. what exactly do you want me to elaborate on? I thought I was pretty darn clear with what I said. what do you want? me to give you a sample db file illustrating what I said previously?

 

[Winnipeger]

Sample db please.

Appreciate your assistance!

 

[vba_php]

Sample db please.

Appreciate your assistance!

LOL. that's what I figured. you're greedy. give me 10 minutes....

 

[vba_php]

winni,

I warned you. so this is almost the same thing as I mentioned earlier. However, there is a composite key in it. I've never used a composite key in my life, if I remember right. and I don't think you should need one in your case either. I believe you will need to tweak this a little bit, because it's a little bit unclear of how one would relate the concept of "production runs" to "sequences". I'm guessing you would have to explain to us a little more about what the purpose of a "sequence" is. But this should get you started...

 

[Cronk]

Only 2 tables (JobOrder and Production) are required. Use a cross-tab query

transform sum(PQty) AS TotalPQty select jobOrder.jobOrder from jobOrder inner join production on JobOrder.jobOrder = production.jobOrder GROUP BY JobOrder.JobOrder pivot seqNo;

 

[Winnipeger]

Thank you Adam and Cronk

I will try it out.

Have a great day!

 

[vba_php]

Only 2 tables (JobOrder and Production) are required. Use a cross-tab query

transform sum(PQty) AS TotalPQty select jobOrder.jobOrder from jobOrder inner join production on JobOrder.jobOrder = production.jobOrder GROUP BY JobOrder.JobOrder pivot seqNo;

that's a very good idea. I haven't used a cross tab query in access for probably 10 years.