Hi, I'm developing an wharehouse

[judvos]

Hi,

I'm developing an wharehouse - inventory access database. I have an inventory table (articles, wharehouse, production date, expiring date, Qty ), an incoming and an outgoing table. The articles leave the wharehouse based on the fifo system.
My products have an production date and an expiring date. My outgoing products most first be substracted from the oldest date.

Now I have trouble with my append query. Does anyone have a code to make it work.

Jud

 

[hneal98]

Can you explain a little more what your append query is supposed to do and what kind of trouble you are having?

If I am reading you correctly, you want to pull only those products with the oldest date. In your append query, you would use the Min(Expiring Date) to do that.

Thanks.

 

[judvos]

Hi,

Thanks for reacting. My append query must place my new records from my incoming table to the inventory table.
Updating my fields does not succeed either.

A wharehouse receives and distribute articles (articles that I have in Stock). My inventory table is this

e.g.
Inventory table (stock items)
[wharehouse] - [article] - [incoming order no.] - [incoming date] - [outgoing date]- [Qty]

New articles are being deliverd, so I have an Incoming table (this is a dummy table, when appending to the inventory table, the records will be deleted)
[wharehouse] - [article] - [incoming orderno]- [incoming date] - [outgoing date]

So I need to append my incoming table to inventory table, so I can see what i have in stock and when my products expire. But my append query doesn't succeed

And outgoing articles from the wharehouse will be substracted from the inventory table. But based on first in first out

 

[hneal98]

Can you post a copy of your query. In the Query desinger, just click on the SQL button on the button bar and that will take you to the SQL version of your query. Then copy it and paste it here so we can see it.

Thanks.

 

[judvos]

SQL view

INSERT INTO INVENTORY ( WHAREHOUSE, ART_NR, MOB_NR, PRODUCTION_DATE, EXPIRING_DATE, QTY )
SELECT INCOMING.WHAREHOUSE, INCOMING.ART_NR, INCOMING.MOB_NR, INCOMING.PRODUCTION_DATE, INCOMING.EXPIRING_DATE, INCOMING.QTY
FROM INCOMING;

THANKS

 

[hneal98]

Did you try it like this?

SQL view

INSERT INTO INVENTORY ( WHAREHOUSE, ART_NR, MOB_NR, PRODUCTION_DATE, EXPIRING_DATE, QTY )
SELECT DISTINCT
INCOMING.WHAREHOUSE,
INCOMING.ART_NR,
INCOMING.MOB_NR,
INCOMING.PRODUCTION_DATE,
EXPIRING_DATE = MIN(INCOMING.EXPIRING_DATE),
INCOMING.QTY
FROM
INCOMING
GROUP BY
INCOMING.WHAREHOUSE,
INCOMING.ART_NR,
INCOMING.MOB_NR,
INCOMING.PRODUCTION_DATE,
INCOMING.QTY

 

[judvos]

Got it,

thanks