Create Report for a Packing List (1 Viewer)

[janeyd]

I have created a database to handle orders / stock for dyeing yarns.
Background is we receive lots of different white yarns from company's to hold in stock. Each type of yarn will have different batches which need to be kept and recorded separately. These company's will then send us a 'dye to' order telling us which yarn and batch to use and how much and what colour to dye to.
The dyeing process actually reduces weight of yarn, therefore if customer wants 10kg we would dye 11kg (this is called greasy weight). When yarn has been dyed we record yarn, batch, colour, greasy weight, our dye lot no., net weight. We may then hold this coloured yarn for a while until we are ready to despatch. The despatch might be a complete order, or parts from different orders.
I need to be able to call up a 'white yarn stock' and 'coloured yarn stock' at any time, as well as outstanding orders, completed orders etc.
The database I have seems to work how I want, (I know its not designed perfectly but I am self-taught and have spent many hours with trial and error!), however I don't know best way to create packing list and hopefully stickers for the cartons. I have created a query which only shows yarn that has been dyed (because it has been given our dye lot no.) and from this a form - with a subform populating a advice note table to enter a carton no. and how kg of that particular yarn is in that particular carton, but from there I am a bit lost how to put that into a report that looks like a packing list (which I can print and hopefully at same time will print out a stciker for each box with what contents are inside).
I'm guessing I might have to do an update query??? Any help greatly appreciated.

 

[sneuberg]

I don't see where an update query would be needed. It should just be a select query.

The relationship between Advice Note Details Table and Dye Sheet Details Table needs to be defined. Something is missing. The one-to-many relationships look good from Order Table To tblSalesOrderDetails and then from there to Dye Sheet Detail Table as it goes from Primary Key to Foreign Key in these cases. But this breaks down between Dye Sheet Details Table and Advice Note Details Table. Is the relationship between these one-to-many? Could the primary key of Dye Sheet Details Table be the Linton Dye Lot rather than ID? That would put things in order and you could define the relationship.

Once the relationships are in order a report for the packing list and sticker shouldn't be a problem. Could you tell us what will be on these and how they will be laid out? Maybe we could give you some pointers.

 

[janeyd]

Thank you sneuberg
You are correct - I have made the dye lot no. the primary key.
I then created a query and report called Packing List. This report is essentially how I want it to print except I would like the summary section at the bottom of the page and altogether rather than after each order, plus I would like each line populated even if it is the same as the line above.
My main concern is that it shows all details I have ever put in to the advice note details table - once I have printed this I do not want them to appear in the next packing list, hence the question if I needed an update query.
Thanks again.

 

[sneuberg]

I've reread your post and now I think I was wrong about suggesting the relationship between Dye Sheet Detail and Advice Note Details. It now seems to me that Advice Note Details defines what's being shipped. Is this true? I assume an order may take up more than one carton. Can several orders be put in one cartoon? Is the carton made up of Order No - Yarn Order Details?

This needs some thought. If you were always shipping entire and distinct orders at once this would be easy, but if you are going to split this up I think you will need some way of accounting for it. I guess an order will be like a bank account from which you will be making withdrawals. I hope that perhaps I'm just confused and you can set me straight.

 

[sneuberg]

This can't be that hard and obviously has been done before. I'll look around and see what I can find out. Basically you need to move the pieces of the order to the cartons so an update will be involved somehow.

 

[janeyd]

Hi Sneuberg
Yes you are correct. A carton could contain more than one order, and similarly an order can span more than one box. Each line in the packing list needs to show carton no., order no, dye lot no, yarn, batch, colour and net kg. I then require a summary at the bottom sorted by order no., and including dye lot no., yarn, batch, colour, total net kg., and total greasy weight dyed (from Dye Sheet Details).

I'm with you and am sure something like this must have been done before. I am also continuing to look around and try things.

Thanks again