Can this be done

[posfog]

Hi All,
Just need to bounce an idea off you all and wondered if and how i could get round this issue.

I have built a form that contains the serial number of a product that has come into repair.

What i want to be able to to is to have a list of parts that make up this single product but have the option of saying if a part was scrapped or reused and how many of each part (a product could contain multiples of the same part Eg screw)

EG
Part Number Scrap Reused Qty
Part1 yes 2
Part2 yes 1
Part3 yes 1
Part2 yes 1

Hope this makes sense and i know its a big ask and i thank you all in advance for reading this


Regards

 

[welshgasman]

I'd start and think of how you would do it on paper.? Always a good start.
I'd (off the top of my head, and not being an expert) would have something along the lines of

A table for parts and their cost. Note in my naming Parts that belong to a Product, not product as you have named in the sheets.?
A table for Product
A table for ProductParts which links Product to Parts. In this table you would have a field for the number of parts used for each product.

Then a table Repairs. This table would hold a ProductKey,PartKey and NumberUsed (in your parlance scrapped). You could also work out the NumberReused as well and store it if you wished.

Then it should just be a case of a few queries of lookups to get your figures.
You would probably store the calculated value of number * cost price, so that when the cost price changes, you have the correct price at that time. The alternative would be to have to look up the price at that time when doing the calculations.

HTH

 

[posfog]

.
Review the formulas in the new cells.

https://www.amazon.com/clouddrive/share/PulP1LDI43B7PqUNWNFm5OsWcUZYxnnd3qTZmNqXweF

Hi Logit
sorry i am still learning with Access, can i upload this excel document into access now and it will work?

 

[posfog]

I'd start and think of how you would do it on paper.? Always a good start.
I'd (off the top of my head, and not being an expert) would have something along the lines of

A table for parts and their cost. Note in my naming Parts that belong to a Product, not product as you have named in the sheets.?
A table for Product
A table for ProductParts which links Product to Parts. In this table you would have a field for the number of parts used for each product.

Then a table Repairs. This table would hold a ProductKey,PartKey and NumberUsed (in your parlance scrapped). You could also work out the NumberReused as well and store it if you wished.

Then it should just be a case of a few queries of lookups to get your figures.
You would probably store the calculated value of number * cost price, so that when the cost price changes, you have the correct price at that time. The alternative would be to have to look up the price at that time when doing the calculations.

HTH

Totaly confused with that Welshman and by all means that is to do with my lack of knowledge of access


Regards

 

[Logit]

.
If you open Access you can open an Excel file from within Access.

Then any changes you make to the Excel file will be reflected within Access.


Sorry, I am not very familiar with Access so won't be much assistance to you.

 

[welshgasman]

Firstly, are we talking about Access or Excel here.?

I took it to be for Access as it is in the Access forum.
If it is Access and you do not understand what I posted in post #11 , then I think you might want to rethink your ideas. Whilst I am willing to try and help, I only know the basics and it would take me a fair length of time to create what i think you want, which I would not do. It would not help you, as you would not know how anything works.

The excel option might be better for you but using similar logic to that I posted.?

 

[posfog]

Hi Welshman
the reason i used the excel file is due to i have a better understanding on how excel works and kinda used it as a pen and paper exercise in help put across what i am trying to create in access.

 

[welshgasman]

That is fine, but I think this is going to be very difficult for you.?
So the short answer is, Yes it can be done', but not without a fair bit of work.

I tend to find that Excel is good at quick solutions, if a little clunky when trying to use as a database. Access is better for that obviously, but takes more work behind the scenes, to make everything run smoothly. Also one must forget their Excel thinking when using Access for the most part, else Access becomes very difficult.

In Excel you tend to think in columns.
In Access you should think in rows.