Question import from excel to create Database

[irishtrash]

Hi all,
just looking for some advice on what the best way to go about doing what I'm about to "try" and explain.

I have attached a sample of an excel report that is automatically generated every morning. The first column "batch number" currently stands for a supplier and product type. because this is the only column we can enter data into I would also like to add a po to this so it would read something like 310000456.

What I want to do is to be able to build a database so I can then import this spread sheet everyday to create a report to tell all I need to know.
I know I'm not explaining this very well so will try and give an example below I have put in the columns that I want to be able to pull the information from

batch number recipe fat content weight
310000456 18-20% 19.7 201

once I pull this information into access I'd like to be able to get a report that would tell me this

joe blogs bk70v (the first 2 digits of the batch number bk70v been the item he is supplying us) po number 456
fat content 19.7% weight 201.

but if the same supplier and po number came up again I want it to give me the total weight and the average fat content.

I hope this makes sense and thanks for any advice giving.

 

[spoole]

You will find it very hard to import that excel sheet into access as the layout does not lend itself to importing. You may have more success with the base data that produces the report and from there you can do what you are asking.

 

[irishtrash]

Would it be possible to do if the format of the excel file was like this?

 

[namliam]

Anything can be imported anywhere anytime. Problem is how much work do you need to do and what kind of data are you expecting to get from it

Both formats can be imported with different data, just again... how do you want it and for what?

 

[irishtrash]

an example from the spread sheet I have attached would be adding all of the code 3's together which gives me 1828. then we take the weight 515 and divide it by the total which gives me 0.2817287 then multiply that by the fat % which in this case is 32.2% which gives me the result 0.0907166414....

Pretty much I'm looking to work out the weighted average of fat % in each line using the total weight of that code used on any giving day.

 

[namliam]

Do you need to do this in access? Its also quite possible in excel for example.

What is your point for doing it in access?

Your excel file from post #3 should be very easy to import to a table, just use the external data for importing excel and walk thru the wizard.