Prefix + AutoNumber as Item Code (1 Viewer)

[johannaellamay]

Oops, I see I missed an answer, Post#78...

When an Item is put on a PO it will be indicated by the Check Box in t_SuppliesRequestDetail. When the item is received it will be indicated in another Check Box in that table. I think I said to add two Check Boxes. Depending on which Box is checked what will depend on which Report it shows or doesn't show on.

Ah I see. Okay that's clear. Thanks!

 

[johannaellamay]

1. Okay. So I reviewed the recent model you made. And I see that you have deleted a few relationships. I understand why and how. But my question is, what do I do with those fields that still need lookups? Don't they also need relationships?

Here is the relationships that I am talking about:

t_Employee eEmployeeID - srReceivedBy : I still need that relationship. "ReceivedBy" indicates that the requester, or anyone from his/her department, or even just anyone, has received the items that were requested. So if I delete that relationship, how will I be able to make a lookup?



2. Are the queries I have made useless? That means I won't be able to use them as lookup in my fields?

 

[GinaWhipp]

Not every field that *can* be related *should* be related. You do so with the top priority ones. Also, you do not need (require) a relationship to create a look-up or Combo Box. You can create a Combo Box using any table related or not.

Those queries are not good and not correctly done, so I guess useless is the right word. Once you add your Acknowledgement table and upload your review database I will create a couple of queries you will using for look-ups so you can see how it's done. How's that?

 

[johannaellamay]

Not every field that *can* be related *should* be related. You do so with the top priority ones. Also, you do not need (require) a relationship to create a look-up or Combo Box. You can create a Combo Box using any table related or not.

Those queries are not good and not correctly done, so I guess useless is the right word. Once you add your Acknowledgement table and upload your review database I will create a couple of queries you will using for look-ups so you can see how it's done. How's that?

Okay then. I won't worry about those for now. I just need to settle my tables first.

I'm really sorry. It kinda seems like you're doing all the work. But I really want to make it and learn at the same time. It just takes a whole lot of time because I'm new to this. Sorry. :banghead:

 

[GinaWhipp]

That's why we're here! So, don't be sorry, keep asking away till you understand...

 

[johannaellamay]

Hi! This is what I currently have. I got rid of the queries and added the Acknowledgement table.

View attachment X v2.0.accdb

I have another concern though. So in the database that I am making, items could be anything from office supplies to wood/lumber. However, some items like cabinets, cellphones, etc. need to have property numbers because they are considered assets. And staff, when given such items, should sign a Property Accountability Form. This is a totally different process, but there are redundant information that would be required in keeping track of assets like PO and RR numbers, item, brand, color, etc. But I would also track the serial numbers, accountable employee, date issued, etc. These items are not consumables.

Question: What do you suggest that I make: a new database OR incorporate assets to the one that I am currently working on?

Thanks!

 

[GinaWhipp]

Hmm, will look at this tomorrow (or should I say later today!) Just saw at 2:30 AM.

 

[GinaWhipp]

Let me start off by saying... This looks GREAT! You are starting to get the hang of this

As to your question, you need to incorporate assets into the same database, makes no sense to put much of the same information in a separate file... would probably create confusion down the road.

My observations...

1. You do not need saDSPN in t_SuppliersAcknowledgment because it's already in t_SuppliesRequestsDetail. You will be able to draw it out of that table via a query. The same is true for saItemCode.

2. The only other thing I see is Primary Keyes and Foreign Keys should be named the same, except for the prefix, no matter what table they are in, i.e.

t_Department - dDepartmentID is linked to t_Employee - eDepartment

For clarity the one in t_Employee should be named eDepartmentID. This will be helpful down the road, so you'll want to fix those where they don't match.

 

[johannaellamay]

Let me start off by saying... This looks GREAT! You are starting to get the hang of this

As to your question, you need to incorporate assets into the same database, makes no sense to put much of the same information in a separate file... would probably create confusion down the road.

My observations...

1. You do not need saDSPN in t_SuppliersAcknowledgment because it's already in t_SuppliesRequestsDetail. You will be able to draw it out of that table via a query. The same is true for saItemCode.

2. The only other thing I see is Primary Keyes and Foreign Keys should be named the same, except for the prefix, no matter what table they are in, i.e.

t_Department - dDepartmentID is linked to t_Employee - eDepartment

For clarity the one in t_Employee should be named eDepartmentID. This will be helpful down the road, so you'll want to fix those where they don't match.

Hi! Thank you. HAHAHA.

1. Actually, I need the saDSPN in t_SuppliesAcknowledgement. It serves a different purpose than the one at t_SuppliesRequestDetail. The DSPN for t_SuppliesRequestDetail is for charging of items requested for purchase. The one at t_SuppliesAcknowledgement is for charging of items taken from Admin, and later on, will be charged for the next batch of bulk order. For better visualization, here's the process:

View attachment 55494

2. Okay. Here's the edited one, according to your advise.
View attachment 55495

 

[GinaWhipp]

Hmm, something wrong with your attachments, coming up as invalid, please redo...

 

[johannaellamay]

Okay, replay! HAHA. I hope the links are working now.

Hi! Thank you. HAHAHA.

1. Actually, I need the saDSPN in t_SuppliesAcknowledgement. It serves a different purpose than the one at t_SuppliesRequestDetail. The DSPN for t_SuppliesRequestDetail is for charging of items requested for purchase. The one at t_SuppliesAcknowledgement is for charging of items taken from Admin, and later on, will be charged for the next batch of bulk order. For better visualization, here's the process:



2. Okay. Here's the edited one, according to your advise.
View attachment X v2.1.accdb

 

[GinaWhipp]

It's BEAUTIFUL!

Now, the only I don't see is the Assets portion but other than that you are in great shape. The process chart was very helpful and the way you have it set up now you should be good to go BUT don't be surprised there may still be some tweaks but so far so GREAT!

 

[johannaellamay]

It's BEAUTIFUL!

Now, the only I don't see is the Assets portion but other than that you are in great shape. The process chart was very helpful and the way you have it set up now you should be good to go BUT don't be surprised there may still be some tweaks but so far so GREAT!

HAHAHAHA. Thank you! I still need to add tables thought. It's for the item issuance (items that need property accountability). But I don't how to go about it.

And you did say this: "Once you add your Acknowledgement table and upload your review database I will create a couple of queries you will using for look-ups so you can see how it's done. How's that?" HAHA.

Can I maybe have an example of a query for the database? Then I'll try to study and complete the rest.

Once I make the last tables for property custodianship, what's next? Forms?

 

[GinaWhipp]

Oh, sorry I forgot...

Will work on in a bit and re-upload.

 

[GinaWhipp]

Here you go...

Once you add the balance of the tables, yes, you can move on to Forms where even move queries will be created.

 

[johannaellamay]

Here you go...

Once you add the balance of the tables, yes, you can move on to Forms where even move queries will be created.

Thank you! Okay will study it before I proceed.

 

[GinaWhipp]

And, I'll be here when you are done studying.

 

[johannaellamay]

And, I'll be here when you are done studying.

Okay, I know this might sound so stupid but, what are those queries for? And how should I use them?

 

[GinaWhipp]

You will be using them in Forms and Report. The Employees one will be used anytime you want to see the Employees name. The Purchase Order Detail will be used to filter out the detail for Purchase Orders and then the same for the other one. Open them and look you will see how it tells it's for a Purchase Order.

 

[johannaellamay]

You will be using them in Forms and Report. The Employees one will be used anytime you want to see the Employees name. The Purchase Order Detail will be used to filter out the detail for Purchase Orders and then the same for the other one. Open them and look you will see how it tells it's for a Purchase Order.

I have just another question,

why does the qryPurchaseOrderDetail and qrySupplierRequestDetail look the same? Everythings the same actually, except for one criteria.