import spreadsheets into a table (1 Viewer)

[Jim B.]

I get monthly dealer sales reports in an excel spread sheet. I clean them up for import into Access. I want to import them into 1 complete table every month so I have a accumulating sales table for all the dealers. Not every dealer has sales every month so the record count could be different. I have tried importing each spread sheet but I cannot get the months to go into their respective columns. I could create a table with all the dealer names and add in the monthly sales manually, but I would like to do something that is faster and much more accurate.

 

[Uncle Gizmo]

I cannot get the months to go into their respective columns.

It appears you have columns in your spreadsheet one for each month. Access is a different animal, and it's not normal to store the months, which are actually Dates in an individual column, one for each month. You would normally store them in a date field. However if you just want your MS Access database to look like an Excel spreadsheet, you can probably do it this way, but what's the point? You might as well leave it in Excel...

The real question is why do you want to move your data into MS Access? If you want to take advantage MS Access to do calculations, grouping of sales etc, then you're going to have to change the way you think about your data.

More information here:- Excel in Access - Problem & Solution!

 

[vba_php]

do u know how to code in basic? basic has a library associated with it called ADO. the connections that can be established with this library can be useful to you in a situation like this. then of course, basic also has a function called transferspreadsheet, but it just acts like a gloried version of what you can do by clicking buttons, which is what you're doing now.

 

[Jim B.]

Thank You, I will think through this some more and do some more looking for a better way to do thsi.