Advice for current DB (1 Viewer)

willsnake

Registered User.
Local time
Tomorrow, 03:56
Joined
Dec 3, 2018
Messages
52
Good day,

1st of all, sorry that my Subject is very broad. Attached is the DB I just created with the help from this wonderful forum's experts... I am now again seeking your expertise.

The DB is running smoothly, but then I encounter a problem... The way this DB works is that 1st to record the Purchase Request. Categorizing the Items through LotID or LotNumber. Then when it is been processed to being a Purchase Order, the form TblPurchaseOrder will do lookup for the PR and LotID, then in the subform the items will appear and will be edited by what the Purchase Order has like the quantity and cost.

Now my problem arise when in a single LotID the Items were not in a single Purchase Order, when it is for different supplier which will have another Purchase Order. I would like to seek advice on a workaround on this... I was thinking when in filling the Purchase Order form after the LotID, a new window will open and it will populate all the Items in a LotID then each item will have a checkbox where I can select the items only included in the Purchase Order before they will be recorded in the table...

I am very sorry for this, I just can't find any solution in the net regarding my concern... For now I am watching some video tutorials hoping I could find some solution from there...
 

Attachments

  • William - Copy.zip
    495.9 KB · Views: 70
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:56
Joined
Jul 9, 2003
Messages
16,245
a new window will open and it will populate all the Items in a LotID then each item will have a checkbox where I can select the items only included in the Purchase Order before they will be recorded in the table...

I'm not sure I grasp the essence of your question. However regarding a checklist, I wondered if this post blog might provide some inspiration.

http://www.niftyaccess.com/add-a-check-list-to-your-ms-access-database/

Sent from my SM-G925F using Tapatalk
 

plog

Banishment Pending
Local time
Today, 14:56
Joined
May 11, 2011
Messages
11,611
Can you explain what your database does in more simpler terms? Give us a big picture of what your organization does--no database or industry jargon. Pretend its career day at an elementary school and you are telling the kids what it is you do.

Without knowledge of that, I can only look at your database for general errors. And I see some:

It seems you have a spiderweb for your relationships among your tables. Every table is directly connected to every other table. In the relationship Tool its not that way, but looking at the fields in each table it is. For example:

PRID is the primary key of TblPurchaseRequest. PRID is in TblPurchaseOrder and directly connects it to TblPurchaseRequest. PRID is also in TblLot, but its not included in the relationship tool. PRID is also in TblLotParticular, but its not used to connect to TblPurchaseRequest in the relationship tool. Instead TblLotParticular connects to TblPurchaseOrder via LotID, even though LotID isn't a primary key in either of those tables.

I think you need to get your Relationship Tool fixed before you move on to forms and whatever it is you are trying to accomplish. You haven't properly set up your tables. Give it another shot, set up the Relationship Tool, then post back here what you have.
 

Cronk

Registered User.
Local time
Tomorrow, 06:56
Joined
Jul 4, 2013
Messages
2,770
I agree with Plog.

To fix relationships,
(1) in table TblLotParticular, delete fields LotParticularID and PRID. Then make LotID the Primary key.
(2) in table TblPurchaseOrder, delete field LotID and link the two tables on POID.

You also have redundand fields in TblLotParticular: PODate, ItemTotal (which is Quantity * Cost) and POItemtotal (which is the sum of individual lot costs and does not need to be stored).
 

willsnake

Registered User.
Local time
Tomorrow, 03:56
Joined
Dec 3, 2018
Messages
52
good day...

I am sorry for that my DB is a mess... its my 1st DB ever made and I only learned to make this tru tutorial videos and from this forum...

If its not to much trouble, may you help me... The table relationship is very messy...

Please... Thank you...

The step on my work is 1st create a Purchase request. then some other person will make a Purchase Order which will then sent to me and I will record it.. Sometimes a single category in Purchase request called Lot, the Items are into 2 or more Purchase Order. Also the reason I included the recording of Purchase Order because my boss told me that it may be passed into me the creation of Purchase Order... I am just preparing for the additional work load... The Inspection and Acceptance report is the last step before a certain order is processed for payment...

Sorry for the bother...
 

Cronk

Registered User.
Local time
Tomorrow, 06:56
Joined
Jul 4, 2013
Messages
2,770
Is there anything you do not understand about #5?
 

Cronk

Registered User.
Local time
Tomorrow, 06:56
Joined
Jul 4, 2013
Messages
2,770
No. You need to keep it to store the detail line information for each purchase order.
 

willsnake

Registered User.
Local time
Tomorrow, 03:56
Joined
Dec 3, 2018
Messages
52
What would be the PK for the TblLot if I make it the PK in TblLotParticular...?
 

Cronk

Registered User.
Local time
Tomorrow, 06:56
Joined
Jul 4, 2013
Messages
2,770
Did you read #5. Your answer is there.


I won't be responding to you anymore.
 

willsnake

Registered User.
Local time
Tomorrow, 03:56
Joined
Dec 3, 2018
Messages
52
sorry for being noob on my 1st DB...

Thank you...
 

willsnake

Registered User.
Local time
Tomorrow, 03:56
Joined
Dec 3, 2018
Messages
52
Good day,

Thank you very much to helping me masters...

This thread is now solved and closed...
 

Users who are viewing this thread

Top Bottom