Regarding Access Database (1 Viewer)

[A.A]

I m new to Access. I need help with smthin....
How wud u do this....
I need to make a record of record of some items related to an order.And keep the record of their deliveries.Items of same type my not be delivered at the same time.They may be dispatched in turns.
I made a record for the items to be delivered in total.I made another record for the items delivered.How can i include a field that says the number of items left.Total quantity is another table.
Um sorry if m not clear.Plzz help me Thanks for your time

 

[A.A]

Um atleast tell me if it's possible plzz

 

[John Big Booty]

The short answer is Yes

You might get some inspiration from one of the data models available here, the tricky bit will be identifying the model that best suits your situation.

 

[Severin]

Just add a field to your form and in the control source you write something like:
=NameOfToBeDeliveredField - NameOfItemsDeliveredField

 

[Severin]

...this will show you how much still needing to be delivered... if you want to know the total quantity from the other table you could use the above mentioned approach and change NameOfToBeDeliveredField with DLookUp("[NameOfTotalQuantityField]","NameOfOtherTable","NameOfTypeFieldInTable = '" & me.NameOfTypeFieldOnForm & "'")

 

[A.A]

Mr.Severin...like there are many records/deliveries not a single one so i shud add the quantity off all the records matching that criteria say name of item......can i do that using form.........
thanks Mr.Severin nd Mr.John for ur time

 

[A.A]

Mr.severin........Sir can you elaborate.

 

[A.A]

...this will show you how much still needing to be delivered... if you want to know the total quantity from the other table you could use the above mentioned approach and change NameOfToBeDeliveredField with DLookUp("[NameOfTotalQuantityField]","NameOfOtherTable","NameOfTypeFieldInTable = '" & me.NameOfTypeFieldOnForm & "'")

Sir can you plz elaborate

 

[Severin]

can I have a quick look at you database?~)

 

[Severin]

You need to restructure your db... sorry...
Your relationship layout should not contain cascade delete related records. As it is now if you delete a client you will loose every rawmaterial and delivery related to this client.
The relation between RawMaterial and deliveries should be between number and lotnumber.
QuantityLeft in the Deliveries table you need to delete.. The calculations you do later in a query when you do your forms or in the fields on the form with the method I mentioned in my first couple of posts!~)

 

[A.A]

You need to restructure your db... sorry...
Your relationship layout should not contain cascade delete related records. As it is now if you delete a client you will loose every rawmaterial and delivery related to this client.
The relation between RawMaterial and deliveries should be between number and lotnumber.
QuantityLeft in the Deliveries table you need to delete.. The calculations you do later in a query when you do your forms or in the fields on the form with the method I mentioned in my first couple of posts!~)

Sir will you please explain why is it b/n number and lot number.Lot number is smthin like the delivery number.
And related to query..cant we put the quantity left in a table? using an update query or smthing.And i tried putting thing in a query like "
SELECT client.ProjectCode, client.ProjectCode, RawMaterial.NameOfMaterial, RawMaterial.Quantity, Deliveries.Quantity, RawMaterial.Type, [RawMaterial]![Quantity]-Sum([Deliveries]![Quantity]) AS QuantiltyLeft
FROM (client INNER JOIN RawMaterial ON client.ProjectCode = RawMaterial.ProjectCode) INNER JOIN Deliveries ON RawMaterial.Number = Deliveries.Name
WHERE (((RawMaterial.Type)=[what type?]))
ORDER BY client.ProjectCode, client.ProjectCode;"
but it doesnt help.......can u plzz help me and elaborate things.
Thanks for your time sir.....

 

[Severin]

which version of access are you using?

 

[A.A]

which version of access are you using?

microsoft access 2010

 

[Severin]

When I get a moment I will try and give your db a push in the right direction... Probably tomorrow morning..

 

[A.A]

When I get a moment I will try and give your db a push in the right direction... Probably tomorrow morning..

thanks soo much....

 

[Severin]

everybody wants everything today... so I have to try again tomorrow.. sorry

..if you upload your db, maybe someone else can lend a hand!~)

 

[A.A]

everybody wants everything today... so I have to try again tomorrow.. sorry

..if you upload your db, maybe someone else can lend a hand!~)

tsk thanks anyways

 

[Severin]

ok.. I had a quick look and made something that works but maybe not with all the functionality you had in mind looking at your original data..

So you have to take it from here... and help from the forum!~)

 

[A.A]

ok.. I had a quick look and made something that works but maybe not with all the functionality you had in mind looking at your original data..

So you have to take it from here... and help from the forum!~)

Thanks for your time....... I really appreciate it.....

 

[Severin]

By the way... if you need to open the db in design mode hold down shift and open db!~)