Search results

Hi CJ, Please see below. TRANSFORM Sum(qryCapacityPlanningLabourTimesMonthWeek.[Job Time (Hours)]) AS [SumOfJob Time (Hours)] SELECT [EmployeeFirstName] & " " & [EmployeeLastName] AS Employee, qryCapacityPlanningLabourTimesMonthWeek.[Assembly Type] FROM qryCapacityPlanningLabourTimesMonthWeek...

Hi Pat, I have this working now with the weeks I need displayed (based on the selected month) and the full employee list, which is great. But it has a blank column with <> at the top now. I am assuming this is something to do with me showing all records from tblEmployee and only matching...

Hi Pat, How would I force the cross tab to show all employees even if they have no jobs assigned? I know I can force columns to appear, but how would I force rows? We currently have 25 employees, but in week 27, for example, only 7 have assigned jobs with a due date. I would like the crosstab...

Thanks Pat, I shall have a look into this. I hadn't even thought about cross tab. ~Matt

Hi All, I was wondering if anyone had a very basic planner form they have used. I have searched the forums and there are plenty out there, but not quite what I am after. I am trying to display employee workloads and I have a table with a due date, and a query with labour hours. I want to be...

Thanks Maj, I have never coded a loop before but I will find some codes and give it a go. ~Matt

I tried 2 methods of copying the data from Sage STOCK_TRAN table, 174063 records. Method 1: I use a query to make a local copy of the table then append this to my SQL table. Took around 1 minute 30 seconds to action both queries, then truncate the data. Method 2: I use a query to append the...

I have this working now and the TRUNCATE on the pass through works perfectly. Finally I can make use of Sage tables without having to use the horror that are the Sage tables. Thank you all. ~Matt

Thank you. I shall learn this now and see how I get on. ~Matt

How would I do this from access though? I need this to be able to be done on any client FEs that need to do it. ~Matt

Truncate doesn't work in MS Access, not that I can find. With regards to the import, I have 2 queries and use a make table to move the data locally, then append the local table my SQL table. These 2 queries run faster than trying to append the Sage table to SQL. Now I just need a working VBA...

Thanks Maj, I now have the import working in flash. The issue now is deleting the data from the SQL table. I need to find a way to TRUNCATE TABLE in access as the delete function takes upwards of 20 minutes each time. I have no reason to log the records that are being deleted as they are only...

Hi Pat, The data I am appending already has a TransactionID. What I am trying to do is append them in smaller batches, rather than a single query doing them all in one go. I want a query to loop through 1-1000, then update my label, then run through 1001-2000, then update the label, and so on...

Hi Minty, Thank you for your reply. There is an ID field and breaking it into 1,000 or even a 10,000 loop wouldn't be an issue. Yes, the Sage ODBC is the bane of my life. ~Matt

Hello all, Is there a way of doing an append query, but a single row at a time to enable the creation of a counted progress label. I have a table in Sage50 that I want to append to an SQL table so I can use the data faster. It is a transaction log that I use for some reports and it grows...

Thanks Pat, I installed that just as your reply came through. Jet lag from my holiday slowed my brain today and I couldn't find it when I looked earlier. ~Matt

Thanks Pat, I have actually managed to get it sorted, but would prefer to create my tables in access the "upsize" them. I know MS Access no longer has this as an option, but is there another tool that will do this for me? If I have to create them in SQL, I will, but as the tables are all...

I have a well established database with this as one of the modules. Feel free to message me if you want to bounce some ideas. ~Matt

Hi all, I am looking to move some of my larger tables onto SQL server, but I have been unable to find details on how to create a new server. Our Domain controller has 2 SQL Servers registered at the moment, but I wanted to create my own one instead of a new database inside one of these. I have...

Sorry to post this here, but are there any Crystal Reports wizards on these forums? ~Matt