Prefix + AutoNumber as Item Code

[GinaWhipp]

1. Okay
2. Then you need a junction table for Department Donors but you also need to have a Donor tied to Sample Request? Or is the Sample Request tied to the Employee which is tied to the Department?

 

[GinaWhipp]

Okay, I have adjusted the tables but still not quite sure I understand what you are doing with the t_DSPN table. However, here's my thought process...

I have typed the word REMOVE next to some fields because you are getting the information from related tables so no need to have twice.

You have two Reports tables but those can go as it looks like the information is in the other tables and it that case you only need a query with those tables included.

Also added t_DepartmentDonors

 

[johannaellamay]

1. Okay
2. Then you need a junction table for Department Donors but you also need to have a Donor tied to Sample Request? Or is the Sample Request tied to the Employee which is tied to the Department?

Hey. I know it's so complicated. Haha.

So the Supplies Request is tied to the Donor. But it is also tied to the Employee and Department. -_-

 

[johannaellamay]

Okay, I have adjusted the tables but still not quite sure I understand what you are doing with the t_DSPN table. However, here's my thought process...

I have typed the word REMOVE next to some fields because you are getting the information from related tables so no need to have twice.

You have two Reports tables but those can go as it looks like the information is in the other tables and it that case you only need a query with those tables included.

Also added t_DepartmentDonors

WOW. This looks so much tidier than the model I made. -_- I feel so embarrassed. Haha. I'll study it first then maybe I'll make a few changes depending on how I understand it. I'll update as soon as I can process all the info. It's hard to digest. Haha. :banghead:

 

[GinaWhipp]

Don't be embarrassed I have been doing this a lot longer! Just sorry it took me so long to get it to you...

I'll be here when you're ready for questions!

 

[johannaellamay]

Hey Gina,

So I basically have three major forms: Supplies Request, Purchase Order, and Receiving Report.

In my previous model, I made three different tables plus one table each for their details. That would result to redundancy of information since the items that are in PO are the same items in the SR and RR.

So I was thinking, maybe I could make a sort of checkbox in the SR for a certain item being ordered. If that is clicked, it should automatically be reflected in the Purchase Order Form with the corresponding purchase order number. Then, if the items have been delivered, there's another checkbox which should automatically reflect to the RR.

It's just like the sample database in Access 2010. Where a Purchase Order has a checkbox that says "Post to Inventory." Except, the checkboxes are linked to the PO, RR, and the actual inventory for tracking the items that came in.

I really want to make a similar database, the template in Access. But it's not exactly the same because I have a lot of donor sources, then a lot more forms. So it's much more complicated.

Anyway, the question is, how do I do that? I mean, how do I make checkboxes linked to a certain table/form or something? I hope I'm making sense. -_-

 

[GinaWhipp]

Yes, you are making sense...

Using the check box would trigger the item to show up on the PO side since your query would include a criteria of True. So, in the query if the Check Box is True then it will show in the Purchase Order query. Have you made the queries yet? These queries will be the Record Source for your Forms which should help *clear* it up in your mind.

 

[johannaellamay]

Yes, you are making sense...

Using the check box would trigger the item to show up on the PO side since your query would include a criteria of True. So, in the query if the Check Box is True then it will show in the Purchase Order query. Have you made the queries yet? These queries will be the Record Source for your Forms which should help *clear* it up in your mind.

Hmm. I haven't really made any queries yet. Because I didn't know how to relate them to the forms when it comes to checkboxes. So are you saying that the checkboxes will be in a query and not a form? And that I have to make queries for all the three forms (SR, PO, RR)?

 

[johannaellamay]

In the Northwind Traders template, the form has the checkbox "Post to Inventory" which is dependent two other tables, and two other forms:

1. OkAlert form
2. YesNoAlert form

And I'm not sure about this but I think the checkbox triggers a Macro. But I don't want it to be too complicated and i don't want to have to write codes in Macro. Would that be possible?

 

[GinaWhipp]

I don't do Macros for this kind of thing. (Haven't used a Macro in years) We will most likely use VBA or UPDATE queries so no worries there. However, you need to start building your queries and Forms provided you have the tables the way you want them. So, are your Tables done?

 

[johannaellamay]

I don't do Macros for this kind of thing. (Haven't used a Macro in years) We will most likely use VBA or UPDATE queries so no worries there. However, you need to start building your queries and Forms provided you have the tables the way you want them. So, are your Tables done?

No, not finished yet. I'm redoing everything, keeping in mind the new model that you have made. However, I'm stuck at SR-PO-RR. Because I really don't know if I should make tables for each of them. I can't imagine how it would look like.

I'm not sure if I'm able to explain this right. So in the model that you've made, you eliminated t_PurchaseOrderDetail because you said it would be redundant right? You just related it directly to t_SuppliesRequest. But without the t_PurchaseOrderDetail, how would the database reflect which items in what Supplies Request have already been made Purchase Orders? If I understood it correctly, your model will only show the PO Number related to the SR, but it won't show what items are in the PO. Is there a way, to show the items that have been purchase ordered? I mean, yeah, I will see them, but as a whole in the SR, I won't the actually PO number related to each item.

I'm sorry. It's so complicated. :banghead:

 

[johannaellamay]

View attachment X.accdb

So,

this is where I'm at. I'm really stuck with SR-PO-RR relationships. I don't know how they should be connected. And it's so hard to explain.

 

[GinaWhipp]

Here are the fixes...

1. The relationship between t_SupplierRequest and t_PurchaseOrder should be based in the first PK in t_SupplierRequest. You should not create a second Key it's not needed.

2. You store the spSupplierID in t_PurchaseOrder (so, poSupplierID) and relate on that not the Company Name.

3, You can remove t_Employee1 as the relationship is not needed and will create confusion the way you have done it. You do not need to relate it to see it when running queries.

4. If Department is related to Employee there is no need to also store it in t_SupplierRequests

5. What happened to the Acknowledgement Form table?

6. Remove the Key from Company Name in t-Suppliers it's not needed.

To answer your question, you add a Yes/No field to tSupplierRequestDetail. When checked it will indicate it is on a Purchase Order and that same check box will also determine which Report it shows on. You also need a field to store the poPuchaseOrderID in, so you know which PO that record belongs to.

It's really not that complicated it's just that this is new to you.

 

[johannaellamay]

Here are the fixes...

1. The relationship between t_SupplierRequest and t_PurchaseOrder should be based in the first PK in t_SupplierRequest. You should not create a second Key it's not needed.

2. You store the spSupplierID in t_PurchaseOrder (so, poSupplierID) and relate on that not the Company Name.

3, You can remove t_Employee1 as the relationship is not needed and will create confusion the way you have done it. You do not need to relate it to see it when running queries.

4. If Department is related to Employee there is no need to also store it in t_SupplierRequests

5. What happened to the Acknowledgement Form table?

6. Remove the Key from Company Name in t-Suppliers it's not needed.

To answer your question, you add a Yes/No field to tSupplierRequestDetail. When checked it will indicate it is on a Purchase Order and that same check box will also determine which Report it shows on. You also need a field to store the poPuchaseOrderID in, so you know which PO that record belongs to.

It's really not that complicated it's just that this is new to you.

1. DONE

2. Actually, I did. I mean, I related it but just named it different because that's how I want the table to look like. Is that okay? Or should I just change it to SupplierID? And another thing, I know it should be related to SupplierID but in the lookup, what should show is just the company name, and should not include the PK. Will that be possible?

3. It actually had three Employee tables because I created too many relationships with it. So what I did was create queries instead. However, for the two remaining relationships, I think it's hard because in the original Employee table, there's a field called eImmediateSupervisor which should also relate to the eEmployeeID. I think that's why Access automatically created a copy table. I've edited the relationships though so it's done.

4. In reality, employees from another department could make a supplies request for another department. But in policy, an employee can only request for supplies for his/her department. So for me, I still kind of need to store it in the table. However, I'm thinking of maybe following the policies strictly to accommodate your suggestion. Quick question, if I don't store srDepartment, in the form I will create in the future (lol), is it possible that when an employee is selected, his/her department will automatically show?

5. I didn't copy the Acknowledgement table yet because I was stuck with the SR-PO-RR. Will follow soon.

6. DONE

7. I've already made the Yes/No fields. And I created a srdPurchaseOrder field to relate to t_PurchaseOrder. Quick question: how do I create the relationship of the checkbox to the Purchase Order when it is clicked?

8. Another quick question: So in most relationships, what is usual is to relate a field to the PK, right? But on the actual form, I only want to see the actual data, not the PK number. So in the case of the the relationship of t_PurchaseOrder and t_Supplier, when I am in the PO form and I want to type in the supplier (or in most cases, it's in a drop down menu), I only want it to show the Company Name, and not the PK. Not sure if I'm making sense. But is it possible?

9. Another thing, I cannot understand how I would be able to make checkboxes relate to the Receiving Report. You said that there is only one checkbox for the PO and the RR right? But if I check that, it doesn't necessarily mean that an RR has been created. I want the checkbox to first indicate that the item has been Purchase Ordered. And later, then the items arrive, I want another checkbox to indicate that an RR has been made. Would that be possible?

Here's my progress based on your fixes.

View attachment X.accdb

Will add the Acknowledgement table today. Just a sec.

 

[GinaWhipp]

You need to look at the Relationships window and see what I have done. Study it and understand it before you move on. Then you need to remove the Calculated Fields out of the tables that is not where that is done.

Next, you can't drop tables in queries that are unrelated or your query will be a mess.

Stop worrying about what is going to be stored in the table. You always store the ID field. You will be able to show the name in queries, forms and reports so no worries there. You keep jumping the gun which is why I think you are stuck on the tables. Stop thinking about the forms and reports or we will never get off the tables.

 

[johannaellamay]

You need to look at the Relationships window and see what I have done. Study it and understand it before you move on. Then you need to remove the Calculated Fields out of the tables that is not where that is done.

Next, you can't drop tables in queries that are unrelated or your query will be a mess.

Stop worrying about what is going to be stored in the table. You always store the ID field. You will be able to show the name in queries, forms and reports so no worries there. You keep jumping the gun which is why I think you are stuck on the tables. Stop thinking about the forms and reports or we will never get off the tables.

Okay sorry.

When you said, "you can't drop tables in queries that are unrelated," what did you mean by drop? -_-

Anyway, will study it. I'll let you know if I have further questions.

 

[johannaellamay]

By the way, I'm not sure if you've answered my question about the RR.

"I cannot understand how I would be able to make checkboxes relate to the Receiving Report. You said that there is only one checkbox for the PO and the RR right? But if I check that, it doesn't necessarily mean that an RR has been created. I want the checkbox to first indicate that the item has been Purchase Ordered. And later, then the items arrive, I want another checkbox to indicate that an RR has been made. Would that be possible?"

 

[GinaWhipp]

Ah, I see you took that literally... well, I will be more careful in my choice of words. You need to put in the tables that *talk* to each by either being related or some other field to draw out the information you want. But you can't put on in there unrelated to any other table.

If you are unfamiliar with how queries work you need to review the links I posted in Post# 27. The one by Crystal has a section or two on queries.

 

[GinaWhipp]

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.

 

[johannaellamay]

Ah, I see you took that literally... well, I will be more careful in my choice of words. You need to put in the tables that *talk* to each by either being related or some other field to draw out the information you want. But you can't put on in there unrelated to any other table.

If you are unfamiliar with how queries work you need to review the links I posted in Post# 27. The one by Crystal has a section or two on queries.

Haha. Sorry. It's like you're talking to a child. -_- I will review it then.