Integrating Excel with Access

[ccordner]

I have a roster package in Excel, which will always need to be in Excel. However, some of the functions I have built depend on lists of staff, locations etc.

The obvious thing seems to be to use Access tables, from a main database, read from Excel.

1) Is there an easy way of looping through all the records in an Access Table?

2) If the Access database is designed in two halves (front-end and back-end), would multiple Excel users be able to access the back-end?

Thanks
Chris.

 

[ccordner]

Thanks. I knew you didn't have to copy it all into a sheet, but I'm still getting my head around this, so I wasn't really sure which bits of code needed substituting.

So far, I've got this:

   With rst
    .CursorLocation = adUseServer
    .Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
    .MoveFirst
    Do Until .EOF
        Call TimeSheet(!PayNo, !Name, !Hours, !LocNo, !Location)
        .MoveNext
    Loop
    .Close
  End With

.

The strQuery is "SELECT tblStaff", but it's coming back with "No value given for one or more required parameters" on the .open... line.

Any ideas?

thanks
Chris.

 

[ChrisM]

Try

Select * from tblstaff

Assuming you want all fields

 

[ccordner]

Perfect thanks!

 

[Ruddles]

How's your SQL? I quite like W3Schools.com for their tutorials - take a look at http://w3schools.com/sql/.

Lots of people on this forum speak SQL.

 

[ccordner]

None-existent! But that certainly looks worth having a go at!

This is going to be quite a complicated task, half the battle is working out the best way to store all the information in Access - let alone doing anything with it.

But that function at the moment is working well - I copied the "sort by" bit from your example too.

 

[ccordner]

Thanks all for your ongoing help with this, I am amazed at how easy it all is once you get used to it.

Two more questions, I am afraid!

1) I notice in your example of SQL, I noticed " GROUP BY tblTimesheets.[staffID] ". What does this actually mean when retrieving records.

Say for example, I am retrieving a list of days off, grouped by the staff's home station. If I cycle through the table, is there any way of knowing when I reach the next "group" or do I have to keep comparing the location for each record to the location for the previous one?

2) Is it possible to produce a report in Access that will show information in a sort of "Year Planner" format.

For example, if I had two tables, one showing which days leave people had booked, another showing when they had medicals booked and a third one showing what days they had worked overtime, can you design a report with the days at the top and the names down the side?

Even better, can I then decide which tables to include in this? (Say there were half a dozen tables, could the user choose two or three to show on the report?)

This one might be a bit ambitious, but always worth asking!

Thanks
Chris

 

[Ruddles]

I think you're talking to ChrisM - I didn't post any SQL with GROUP BY in it.

Do you have a copy of MS Access? I have to admit my SQL isn't brilliant by any means, so I generally build the query in Access which has a graphical interface, drag-and-drop, that sort of stuff, then switch to SQL view and simply copy-and-paste the code into my VBA program.

Access is much more powerful than Excel for data handling and will do all those things you described.

 

[ChrisM]

You use GROUP BY when you are summarizing data with aggregate functions (sum, avg, max, min, etc). Say you have sales orders, and you want a report of sales $$ by day.

If you did this:
SELECT Day, Revenue FROM tblSales WHERE Day = 7/8/11;

You would get multiple rows of results, one row for every sales order you had on 7/8.

Instead, just like a pivot table, you sum up your results by day before viewing the result:
SELECT Day, Sum(Revenue) FROM tblSales WHERE Day = 7/8/11 GROUP BY Day;

This would return one row of data, total revenue for 7/8/11.

You can stack items in your GROUP BY, so you could see sales by month by day by region, etc. (GROUP BY SalesRep, Region, Day)

 

[ChrisM]

If you are looping through a recordset, no, there is not a way to tell when you move from one group to another, you need to track that yourself.

You may want to spend some more time on database design. If you have three tables, one for leave, one for medical, and one for overtime, then as you are finding, it is very hard to run reports that span three tables.

Each table is essentially the same data, just a different category. Why not one table, with an extra column:
EmployeeName, TimeCategory, Date

Values for TimeCategory would be:
L (Leave)
M (Medical)
O (Overtime)

Now run a query on that table for just overtime:

Select EmployeeName FROM MyTable WHERE TimeCategory = "O";

 

[ccordner]

This is working quite well, but I have a slightly more complex question now.

I have a query, "qryTimeSheet", which lists all the staff.

I am using the SQL "SELECT * from qryTimeSheet ORDER BY qryTimeSheet.[LocationName]" to work through each member of staff.

Each time it finds a member of staff, it will transfer their personal details (Pay Number etc.) to a Time Sheet, then find them on the roster (a Worksheet) and transfer the hours they worked across as well.

However, if there is a job share it will come across two pay numbers in one cell.

Is it possible to use a second SQL string, such as "SELECT PayNumber from qryTimeSheet WHERE Paynumber=12345" to find out details about this second pay number without 'losing my place' in the recordset?

Also, when transferring the times, it may come across codes from a table, "tblSAPCodes" - is it possible to refer to this at the same time?

I may not have explained this very well!

Chris