HELP please!!! (1 Viewer)

Winnipeger · Jan 8, 2020

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 · Jan 8, 2020

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 · Jan 8, 2020

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 · Jan 8, 2020

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 · Jan 8, 2020

Winnipeger said:
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 · Jan 8, 2020

Sample db please.

Appreciate your assistance!

vba_php · Jan 8, 2020

Winnipeger said:
Sample db please.

Appreciate your assistance!

LOL. that's what I figured. you're greedy.

give me 10 minutes....

vba_php · Jan 8, 2020

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 · Jan 8, 2020

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

Code:

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 · Jan 8, 2020

Thank you Adam and Cronk

I will try it out.

Have a great day!

vba_php · Jan 8, 2020

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

Code:

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.

HELP please!!! (1 Viewer)

Winnipeger

Registered User.

Attachments

vba_php

Forum Troll

AccessBlaster

Registered User.

Winnipeger

Registered User.

vba_php

Forum Troll

Winnipeger

Registered User.

vba_php

Forum Troll

vba_php

Forum Troll

Attachments

Cronk

Registered User.

Winnipeger

Registered User.

vba_php

Forum Troll

Similar threads

Users who are viewing this thread