Structuring my Database correctly

[rabuayya]

Hi there,

You can say I am a total newb and I am just starting to use Access. I have taken a begginers course where I learnt the basics of how to create Tables, Forms and Queries.

I am trying to create a database that will store and track ShoppingLists. Each shopping list should have a unique list#, date requested, and name of person who requested it. And of course it should a contain a list of items to be purchased with four fields: Serial# (always starting from 1 and increasing by increments of one), ItemDescription, Quantity and Notes.

The items to be purchased Lists dont have to be unique from ShoppingList to another. I.e some items maybe purhcased again and again and may show up in multiple shopping Lists.

I am lost and unsure where to start. This list within list thing is confusing me.

How should I structure my tables? Should I create two tables, one to store the ShoppingList and another to Store the ItemsList and then somehow link them in Forms ?

The entry in the Quantity and Notes field in the ItemsList could be different for the same item in different ShoppingLists. For example

In ShoppingList#1, it could have 2 items :
Sn#:1
ItemDesc: TV
Unit: Set
Qty: 2
Note: 1 for Kitchen and 1 for living room

Sn#:2
ItemDesc: Water Bottle
Unit: 24 Pack
Qty: 5
Note: For guests

In ShoppingList#2, it could have :
Sn#:1
ItemDesc: Letter size paper
Unit: Stack
Qty: 5
Note: For office


Sn#:2
ItemDesc: TV
Unit: Set
Qty: 1
Note: For waiting room.

As you could see TV showed up in the two lists, but had different Qty and Different Notes field.

I hope I explained my scenario well!

Some guidance here would be greatly appreciate

 

[MarkK]

This a classic many-to-many relationship, in which one list item might appear in many lists, and one list might contain many items. At minimum this is a three table solution.

tblItem
ItemID (PK)
Description
Unit

tblList
ListID (PK)
ListNumber
Description

tblListItem
ListItemID (PK)
ListID (FK)
ItemID (FK)
Quantity
Note

So you have a table of generic items as they exist apart from any list (tblItem), a table of lists (tblList), and a sort of abstract table that defines the relationships between a list and its member items (tblListItem), and note that here is where the quantity and other specific ListItem definitions belong, in this case the note.

Does that make sense?

 

[rabuayya]

Thanks lagbolt
I think I get it. The tblListItem ties the two lists together. I can't wait to go and begin this. I will keep you posted on the progress

 

[MarkK]

Yeah, you bet. Cheers,

 

[rabuayya]

Okay the three table structure seems to be working! It's a pretty aweosme feeling. Thank you!

I have one problem though:

I created a form out of the tblList for users to enter the data for each shopping list. However it seems that they can't enter any new items in that list yet since the ItemID for any item must be defined first in the parent list (tblItem) before it can be added to the shopping list (tblList).

Is there a way around that? or am I doing something completely wrong here by creating that form?

Thanks

 

[MarkK]

Yeah, that's right, the item has to exist in the item table before you can add it to a list. But it'd be easy to make a tool to add new ones, like there are only two fields in that table, so put two unbound textboxes on the parent form, and an add button, so the user can fill in the two textboxes, hit add, and then add the item to the table AND the list and let the user specify the quantity.
Hope that helps,

 

[rabuayya]

If I understood you correctly, that mean the user will have to enter each item twice? Once to identify it and the second time in the list. Is there a way such that the user will only have to enter the item once in the list and it will simultaneously be created in the its table?
thanks

 

[MarkK]

Sorry for the delay, I've been out of town.

Yes this is possible. Typically your main form will show the records from tblList, with a subform datasheet with records from tblListItem. The ItemID field in this subform will be a combo displaying records from tblItem that the user might wish to include in the list. As the user types text into that combo, that combo will automatically navigate to the item being typed, but if the typed item doesn't exist in the list you can handle the NotInList event raised by that combo. In handling that event you can add the record to the tblItem, requery the combo, and set the value of the combo to the newly minted tblItem record's ItemID.

Does that make sense? If you get stumped implementing this feel free to post back, but it is doable. Maybe a little tricky, but it's the best way to solve the problem too, if you want many lists to be able to include the 'same' items.

Cheers,

 

[rabuayya]

Thank you very much
Okay,That makes sense to me. I think I get it. I will try and will post back shortly.
Thanks once again. I really appreciate your help!

 

[MarkK]

You bet, and if you get stumped, post back.
Cheers,

 

[rabuayya]

Okay,
I am sorry, perhaps I am a complete amature at this. I thought I could do it myself from that point but I guess not.
I tried to implement a macro in the NotInListEvent to create a new reocrd when the user enters a value that isn't the list.

I went to the design view of the subform from TblListItem, clicked on the ItemID combo box, made sure that Limit to List is set to Yes. In Event. Clicked on Not in List. Clicked Macro.
In Macro:
I clicked Go to Record and filled the following
Object Tpye: Table
Object Name: TblItem
Record: New
Offset: -blank-

Then I added another action: Requery
ControlName: -I left it blank as I'm not sure if this step is correct anyway-

So I ran the form. I entered a new ITEMID but I still got that the item is not in the list. I switched the Limit to List peroperty for the combo box to NO, then i got the original message "You cannot add or change a record becuase a related record is required in Table 'TblItem'.

I would appreciate further help.
Thanks

 

[MarkK]

No apologies required. If this was really easy, everyone would be doing it.

It doesn't make sense to me that you are opening a table. The NotInList() event, when handled in VBA (I don't know anything about macros), provides two parameters, NewData and Response. With 'NewData' you get the text the user entered that didn't exist, which you can then automatically insert into the table. Maybe you want to prompt for 'Unit' using an InputBox or something. Then, depending on what value you assign to 'Response', Access requeries the combo, or cancels the user's addition, and so on.

Does that help? Check out Access VBA help for more details about how this event works. It's not trivial.