Moving or associating how do I tie it together.

[trythis]

I have a file import from Excel. It is a staffing spread sheet. It is all spread out. example

.........Column a .....Column b Column c Column d
Row 1 person name
Row 2 ....................date
Row 3 .................................Dept
Row 4 .............................................7am-7Pm
Row 5 new person

How do I tell access that all of these rows and columns belong to the same person? Also the persons name is always in Column "a" but their are blank rows and fields scattered through out. It is not stair stepped like I have shown it.

I thought I could try to add a column for employee id like this

.........Column a .....Column b ..Column c Column d ....Column e
Row 1 person name ................................................100
Row 2 ..................date .........................................100
Row 3 .................................Dept ..........................100
Row 4 ...............................................7am-7Pm ......100
Row 5 new person ..................................................200

That would tie the rows together, but this would be manual so I was hoping for a better idea.

Thank you for any help with this.
Tina

 

[Banana]

I would you're on right track. Access has no way of knowing which group of rows in Excel spreadsheet should be actually one row, and adding an column to identify those will significantly simplify the code for Access to read from the Excel spreadsheet and piece together the multiple rows into single row in its table.

As for not wanting to do this manually... The question would be whether there are any consistency or pattern you can count on using for building the identifier column? For example, if you know that every group of row always always fill in col a, then col b in next row and col c after that then back to col a for next group, then you can tell Access to look for that pattern and assign a new ID when the data goes back to col a as you iterate over the rows in Excel spreadsheet.

Of course, this is fragile and if the spreadsheet you have do not have a consistent pattern or one exists but is not guaranteed to be always that way today and tomorrow, then manual is the only foolproof way. You can at least try to automate the pattern, then manually check the correctness of the data prior to importing. For example, it is possible to program a function to work in Excel spreadsheet to consolidate each columns into a single row, which would also eliminate the need to group several rows together (if that is applicable, of course).

I hope that helps...

 

[The_Doc_Man]

This is not for the faint of heart, but there is a way to open Excel as an application, then read the spreadsheet and test explicitly whether a particular cell in the spreadsheet is occupied.

Look up Application Objects. When you open an Excel object, you can open a workbook, which will expose a collection of spreadsheets. A spreadsheet contains a collection of Rows and a collection of Columns. If you work by rows (which looks more likely than by columns based on your problem statement), then each Row contains a collection of Cells. Each cell corresponds to a column in that row. (It works the other way, too...Working by columns, the rows are represented by collections of Cells for each column.)

So you can do a for-each type of loop among the rows, seeing if the text for each cell is blank or not. When you get a new name, you store it in a variable. When you step through the rows and eventually get enough to store a record, have a recordset open for appending. Do an .AddNew, store the stuff you picked up from the spreadsheet, do a .Update, and return to the outer loop on rows. When you reach the row number equal to the Rows.Count property, you are done.

Requires a lot of VBA but it really is less complex as a programming action and more of just looking at how it is done in the Help Files. The greater problem will be to decide when you are ready to input a new record through the recordset.

Last bit of advice if you try this - always always ALWAYS remember to close what you open. Otherwise you get what is called a memory leak and you will curse me and my grandchildren for your problems.