Append Query... Can't Create a Unique Number to Append

[Vance]

This is probably very simple, but I can't figure it out...my
experience with Access is very limited. I work for a large school
that orders many supplies once a month. I created a SupplyItems table
that has a Yes/No field that the user can select if they need to
re-stock a particular item. The teachers purchase many different
items from many different vendors, creating one big list all at the
same time.

I then created an Append query to append the details for all the
selected records into a tblShoppingList, which is the basis of our
"Shopping List" report. The tblShoppingList is basically a
"PurchaseOrderDetails" table, but as the table is currently
structured, the fldShoppingListID is an autonumber that generates a
new number for EVERY item in the list:

tblShoppingList:
fldShoppingListID (PK, autonumber)
fldPONumber (FK for tblPurchaseOders… which is now empty)
fldOrderDate (created using a default value of today's date)
fldVendorID
fldItemName
fldUnitCost
fldQuantity ... etc.

I also created a tblPurchaseOrders table that relates to the
ShoppingList table where I want to assign one PO# for each vendor on a
given date (not a separate number for each item):

tblPurchaseOrders:
fldPurchaseOrderID (PK)
fldPO_Date
fldPO_VendorID

The two tables are linked (many to one) on the fldPONumber and
fldPurchaseOrderID fields because I do understand that they must be
linked this way, but... this is my problem... the fldPONumber in the
tblShoppingList table is empty because I can't figure out how to put a
number IN there at the time the teachers are selecting the items to
order (I really can't trust them to keep up with a unique number, and
type it into the field for every item they decide to order).
Therefore, I do not have a number to append to the tblPurchaseOrders
table that will provide the necessary Purchase Number link.

I thought about automatically generating a number that is a
combination of the current date and the vendor ID and appending that,
but Access gave me a conversion error… (I used CLng([Date()] &
[fldVendorID]) Evidently this was wrong).

Am I explaining my problem clearly enough? I feel like I am going
about this backwards because I am creating my PurchaseOrderDetails
table BEFORE I am creating my PurchaseOrders table.

Does anyone know how I should do this?

Thanks,
Everette

 

[Michel Walsh]

Hi,


Leave ShoppingList.PONumber NULL for unassigned PO number, ie, when
each teacher add an item. The day before you make the order, check the items
that have a NULL for that field... that indicates they haven't been ordered
yet.

From that list of desired items, compare with the pricesList from
all the suppliers, find the supplier with the best price for each item. Keep
that sublist of pricesList (bestPrices). The number of supplier, unless
other rules apply, would give the number of new PO. Generate those PO,
specify the supplierID it should be send too.

From the list of PO being built, from the bestPrices list (linked
together through eh supplierID) and from the list of desired items (linked
to the bestPrices list through the itemID), update the PO of shoppingList
with the PO from the list being built. Sound complex, but it is not:


UPDATE (ShoppingList INNER JOIN bestPrices
ON ShoppingList.ItemID=bestPrices.ItemID)
INNER JOIN PurchaseOrders
ON PurchaseOrders.SupplierID=bestPrices.SupplierID

SET ShoppingList.PONumber=PurchaseOrders.PONumber
WHERE ShoppingList.PONumber IS NULL



Sure, make tests on data after a "backup"



Hoping it may help,
Vanderghast, Access MVP

This is probably very simple, but I can't figure it out...my
experience with Access is very limited. I work for a large school
that orders many supplies once a month. I created a SupplyItems table
that has a Yes/No field that the user can select if they need to
re-stock a particular item. The teachers purchase many different
items from many different vendors, creating one big list all at the
same time.

I then created an Append query to append the details for all the
selected records into a tblShoppingList, which is the basis of our
"Shopping List" report. The tblShoppingList is basically a
"PurchaseOrderDetails" table, but as the table is currently
structured, the fldShoppingListID is an autonumber that generates a
new number for EVERY item in the list:

tblShoppingList:
fldShoppingListID (PK, autonumber)
fldPONumber (FK for tblPurchaseOders. which is now empty)
fldOrderDate (created using a default value of today's date)
fldVendorID
fldItemName
fldUnitCost
fldQuantity ... etc.

I also created a tblPurchaseOrders table that relates to the
ShoppingList table where I want to assign one PO# for each vendor on a
given date (not a separate number for each item):

tblPurchaseOrders:
fldPurchaseOrderID (PK)
fldPO_Date
fldPO_VendorID

The two tables are linked (many to one) on the fldPONumber and
fldPurchaseOrderID fields because I do understand that they must be
linked this way, but... this is my problem... the fldPONumber in the
tblShoppingList table is empty because I can't figure out how to put a
number IN there at the time the teachers are selecting the items to
order (I really can't trust them to keep up with a unique number, and
type it into the field for every item they decide to order).
Therefore, I do not have a number to append to the tblPurchaseOrders
table that will provide the necessary Purchase Number link.

I thought about automatically generating a number that is a
combination of the current date and the vendor ID and appending that,
but Access gave me a conversion error. (I used CLng([Date()] &
[fldVendorID]) Evidently this was wrong).

Am I explaining my problem clearly enough? I feel like I am going
about this backwards because I am creating my PurchaseOrderDetails
table BEFORE I am creating my PurchaseOrders table.

Does anyone know how I should do this?

Thanks,
Everette

 

[Vance]

Thank you for attempting to help me. I'm not sure how to do all
this, though; my experience is very limited.

When the teacher selects the item to order, that IS the best price
(because they are choosing from a list created through a many-to-many
relationship between fldItemID and fldVendorID... so I don't think I
need the BestPrice table.

I don't understand how the supplier number can be the new PO number.
It wouldn't be unique would it? We would order from the same supplier
more than once.

I don't know how to create "Inner Joins"... I'm sorry that my
knowledge is so limited. I did figure out how to create a unique
purchase order number, though by combining the OrderDate, the vendorID
and the materialID:
SL_PONumber: CLng(Format([OrderDate],"mmddyyyy") & [fldVM_VendorID])
This gives me a unique number to use, but now Access won't let me
append it to the tblPurchaseOrders -- it says that my records have
"Key Violations."

Thanks for trying to help me, though.
Everette

 

[Michel Walsh]

Hi,

Embedded comments.

Thank you for attempting to help me. I'm not sure how to do all
this, though; my experience is very limited.

When the teacher selects the item to order, that IS the best price
(because they are choosing from a list created through a many-to-many
relationship between fldItemID and fldVendorID... so I don't think I
need the BestPrice table.

I don't understand how the supplier number can be the new PO number.
It wouldn't be unique would it? We would order from the same supplier
more than once.

The PO would be unique, but you would know how many of them to create
knowing how many suppliers you need to supply all the articles.

I don't know how to create "Inner Joins"... I'm sorry that my
knowledge is so limited.

In the designer, you bring (at least) two tables. In the top half, the
graphical part, drag a field from one table and drop it over the field to be
matched in the second table.

If you have

table ShoppingList:
ShoppingListID (PK, autonumber)
PONumber (FK for tblPurchaseOders. which is now empty)
' no need of OrderDate (created using a default value of today's
date)
' no need of fldVendorID
ItemName
UnitCost
Quantity ... etc.

and


table PurchaseOrders:
PurchaseOrderID (PK)
PO_Date
PO_VendorID
VendorID

Then, assume ShoppingList.PONumber is filled with a valid POnumber ( an
existing one in PurchaseOrders.PurchaseOrderID ). Now, observe that I
mentionned that you do not need OrderDate in ShoppingList! Why? because you
can get that info by inner join:

Bring both tables in the designer, drag and drop ShoppingList.PONumber
over PurchaseOrders.PO_VendorID. In the grid, drag all the fields from both
tables ( * ). Look at the data, the field OrderDate in ShoppingList is the
same than the associated one in PurchaseOrders.PO_Date! Because of the inner
join, you can "pump" (or lookup) the data in PurchaseOrders through the
joining fields

PONumber - PO_VendorID.

Sure, as it is, that is working only for existing PONumber, but the goal
was to illustrate the power of joints.


If you have a table PricesList
ItemName (pk)
UnitCost
VendorID


You can get the VendorID you need to contact, this month, with something
like:


SELECT DISTINCT VendorID
FROM PricesList INNER JOIN ShoppingListID
ON PricesList.ItemName = ShoppingListID.ItemName
WHERE ShoppingListID.PONumber IS NULL




Again, we lookup through the items to be order to get the VendorID. The
items to be ordered are those in ShoppingListID where their PONumber is
still NULL (unassigned, unknown, at the moment). Since each item has one
VendorID, what we really want is the list without repetition, so the
DISTINCT "option".

I did figure out how to create a unique
purchase order number, though by combining the OrderDate, the vendorID
and the materialID:
SL_PONumber: CLng(Format([OrderDate],"mmddyyyy") & [fldVM_VendorID])
This gives me a unique number to use, but now Access won't let me
append it to the tblPurchaseOrders -- it says that my records have
"Key Violations."

So, now we know how many PO have to be issued ( exactly one by Vendor ),
we can start to create those records. It seems you want the PO be made of
the mmddyyy representation follow by the VendorID, great (note that I have
added one field to your PurchaseOrders table) :


INSERT INTO PurchaseOrders( PO_Date, PO_VendorID, VendorID)
SELECT Date(), CLng(Format(Date(), "mmddyyyy") & VendorID) , VendorID
FROM (
SELECT DISTINCT VendorID
FROM PricesList INNER JOIN ShoppingListID
ON PricesList.ItemName = ShoppingListID.ItemName
WHERE ShoppingListID.PONumber IS NULL
)


And all the PO number are then created (Observe that the inner most query is
the one we discussed before).

Well, half the job is done, we now need to update the ShoppingList table
with the PO number we just got.

Start with a new query. Bring the three table PricesList, ShoppingList and
PurchaseOrders. We will join ShippingList to PricesList through their
ItemName fields. We will also join PricesList and PurchaseOrders through
their VendorID field. In the grid, drag ItemName and PONumber from
ShoppingList, under PONumber, add the criteria IS NULL. Drag the PO_VendorID
from PurchaseOrders table in the grid too. Take a look at the data! Magic,
we just have to "update" the PONumber by its now associated PO_VendorID
value! Back in design mode, change the query from a SELECT type to an UPDATE
type, and update PONumber by [PurchaseOrders].[PO_VendorID]. You may also
want to update the field UnitCost of ShoppingList by [PriceList].[UnitCost]
at that moment. Just run that second query (after the INSERT INTO one), and
the job is now done, just running those two queries, each month.

Thanks for trying to help me, though.
Everette

Hoping it may help,
Vanderghast, Access MVP

 

[Vance]

Thanks for spending so much trying to explain. I was not, however,
able to make it work.

I do have a third table that is comparable to your "PricesList":

My "VendorsMaterials" table has:
VM_MaterialID (combined primary key)
VM_VendorID (combined primary key)
Cost
Quantity
Order (Yes/No field that the teacher selects if they order item)

This table resolves the many-to-many relationship between tblVendors
and tblMaterials, and it is uses a combined primary key from them.

I also have an Indeterminate link between the VendorsMaterials table
and the ShoppingList table based on the vendorID link. And I had a
final one-to-many link between the PurchaseOrders table and the
Shopping List table.

Bring both tables in the designer, drag and drop ShoppingList.PONumber
over PurchaseOrders.PO_VendorID.

When I brought the two tables into the designer, they came in with my
above-described relationship already in place. So when I used drag
and drop to place the PONumber on top of the VendorID, that made TWO
links between the two tables. Should I have deleted my existing link
first?

Then when I ran the query, I saw only the column headings... no data.
Even when I deleted the first link (mine) just to see what happened,
I still saw no data.

This is as far as I got. Inner Joins may be above my ability at this
point.

Thanks,
Everette

 

[Michel Walsh]

Hi,


You may end up with no data displayed if there is no "match" through the
join. That may occur is there is no PONumber already assigned, as example
(which can be the case if the system had never been used, yet). It is
possible to have more than one field involved between the same join between
two tables, even if that is not very usual, it is far from being something
you would never see either. Sure, if the join was already drawn for you,
probably because you have a data integrity rule (DRI), between the two
tables, then, you don't have to repeat it.

In theory, you do not need the Quantity field in VendorsMaterial ( it
would be supplied through a join, from ShoppingList). Order, the Boolean
field, may or may not be required ... as example, I assume there is a
possible match between the ItemName in Shopping (as example a blue pen) and
VM_MaterialID.

I can send you a small database, with some fictions data already filled
(previous PO) and new data not yet ordered. With just the run of two
queries, the Shopping and PO tables are filled (appropriately), with the PO
number schema you mentioned.



Vanderghast, Access MVP

 

[Vance]

That would be great if you could email it to me. Maybe I could
understand it better if I saw it.

Thanks!
Everette

 

[Vance]

Thanks! This really helped me understand it.

Everette

 

[Michel Walsh]

Hi,


You are welcome.


Vanderghast, Access MVP