I a little direction in starting my project (1 Viewer)

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
I need a little direction in starting my project

I have spreadsheets called allocation sheets. These are yearly documents and each row is a service instance.

The row headings are:


  • Date
  • Purchase order #
  • Req #
  • Child Name
  • Service
  • Requesting Organisation
  • Caseworker Name
  • Price ex. GST
  • GST
  • Price inc. GST
  • Allocated to
  • Date report received
  • Date report sent
  • Allocated by
  • Report sent by
  • Report sent by


There will be x number of allocation sheets within the same folder, all relating to different years. They will have the same name except the year will be different. The allocation sheet is broken up into sheets for the months of the year.


My supervisor wants to be able to search for things such as:


  • All children that have had a certain service
  • Dollar figure in regards to a certain caseworker and a certain child
  • Dollar figure that has been applied to a set Purchase order # over a certain time frame, (may be in the year of the allocation sheet of might span over several allocation sheets or years).
  • Any combination of the list above.


What would be the best program to achieve this in. Access was my first choice but the allocation sheets are going to be constantly updated. Would I create queries in access and have them reference the external excel files as the files will always be up to date or do I have to import the data from excel into access before I ran the query? Having it reference external files may be difficult as the filename will change every year, but I am sure that could be solved with some vba coding.


Any suggestions as to how I could tackle this would be greatly appreciated.


Thanks for your help guys,
Dave
 
Last edited:

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
For every month in the allocation sheets, the header row is in row 3 and the date starts in row 4.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2013
Messages
16,553
little unclear as to the requirement. you say

the allocation sheets are going to be constantly updated
all of them? or just the current one? Also, what does 'updated' mean. existing rows are changed?, new rows added? both?

My supervisor wants to be able to search for things such as:
how? by filtering a very large dataset (i.e. they want to see all the data)? or by entering some data and applying that as a criteria?

What will the supervisor do once rows have been identified? will they want to update them? If so, how? by going back to the original excel file? something else?

Would I create queries in access and have them reference the external excel files as the files will always be up to date or do I have to import the data from excel into access before I ran the query? Having it reference external files may be difficult as the filename will change every year, but I am sure that could be solved with some vba coding.
depends on the answers to the above but either import all data to a table - adding new rows, updating existing ones. Or use a union query.

You can identify all files by using the dir function and a loop - along the lines of

Code:
dim pfname as string
pfname=dir("pathtofiles\filename*.xlsx") 'assumes year is at the end of the file name
while pfname<>""
    'dosomething
    pfname=dir
wend

you can build a basic query in vba along these lines to query the excel file which you would insert into the code above where it says 'dosomething

Code:
sqlStr="SELECT *
FROM (SELECT * FROM [sheet1$A:Z] as xlData IN '" & pfname & "'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL"

depending on requirements this needs to be adapted to build a union query, or adapted to create update and append queries
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:42
Joined
May 7, 2009
Messages
19,169
you can import your data in access table, of course.
you may need additional table to monitor which one sheet have been imported, together with the Modified date of the excel file. in the future you will need the later field to check for new modified excel file, then you can re-import it and delete the old data.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:42
Joined
Feb 19, 2002
Messages
42,970
What would be the best program to achieve this in. Access was my first choice but the allocation sheets are going to be constantly updated.
No solution will work well as long as the workbooks remain the "database". Access is a RAD (Rapid Application Development) Tool. It is used to create applications to manage data. Access can store data in Jet (.mdb) or ACE (.accdb) format or use a server based database such as SQL Server, Oracle, or DB2.

Once you go through the effort of importing the data, future maintenance is best done in Access. You can keep history if it is important to see who changed what and when and you can do all the reporting and searching you want to do. You can even export the data back to Excel for certain types of reporting.

Access is a far better choice than Excel for data storage and reporting. Excel is like comfort food to people who have "always" done it that way. You can wean them off Excel and provide much better data security and validation and reporting with Access. And, if you do it correctly, upsizing to SQL Server when you get too many users or too much data for Jet/ACE to handle, will be trivial.
 

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
Re: I need a little direction in starting my project

little unclear as to the requirement


Sorry if I was confusing in my definition, I will try and be as specific as possible. I have x number spreadsheets called allocation sheets for x number of financial years. The spreadsheets record one of about 6 activities between youth support workers and children. I will use the word contact to refer to one of these activities.


The allocation sheets will be updated with new information whenever my supervisor decides to add details of a new contact. This could mean that a row needs to be added or even updated if something has changed. He may need to record a contact going to occur in the future so I will need to be able to search through future allocation sheets.



I have uploaded some sample allocation sheets for the 2019-2020 financial year. There are 2 allocation sheets for each financial year that are correctly populated (a NPSS Work Allocation Sheet and an Internal Work Allocation Sheet). My supervisor wants the ability to search through all of the allocation sheets for different pieces of information. Examples of the information required may be:



- number of children assigned to a certain caseworker

- number of contacts for a particular child in a certain month

- All children that have had a certain service

- Dollar figure in regards to a certain caseworker and a certain child

- Dollar figure that has been applied to a set Purchase order # over a certain time frame, (may be in the year of the allocation sheet of might span over several allocation sheets or years).

- Any combination of the list in my first post.



My coding knowledge of VBA or SQl is very limited so thanks for this help. I have probably left something out, let me know if you need something else.



Thanks.
 

Attachments

  • allocation sheets.zip
    93.9 KB · Views: 110
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:42
Joined
Feb 28, 2001
Messages
26,996
The allocation sheets will be updated with new information whenever my supervisor decides to add details of a new contact. This could mean that a row needs to be added or even updated if something has changed.

The question here is, is there any time when the allocation sheet becomes static. You said they are yearly documents and differ by year number. My question is, if you have one of these documents from 2015, will it ever change? If only this year's documents can change, that is one thing. But if any of the documents can change, you have the issue of "authoritative source."

As long as Access is not allowed to be the authoritative source of your data, it is very much unlikely that Access will be able to help you unless/until you TOTALLY redesign this system. Oh, Access can probably manage the files - but by having things spread over multiple sheets, you make the program complexity balloon out of sight in no time. Having them change unpredictably by editing a sheet (rather than a master table in Access) means that just about every attempt to perform a search or report will have to totally re-import everything to "take a data snapshot" from the multiple external authoritative sources.

I cannot tell you what to do. All I can tell you is what I would do, which is pitch to the boss that the current system is why he can't get the reports and queries he wants. I would say to just link Access to these allocation sheets as linked tables, but from the sound of it you have a lot of them and Access has a limit on how many external sources it can have for linked tables. The limit itself has changed over the years so the number depends on which version of Access you are using. I didn't see a specific version number for Office so can't tell you where your limit would lie, but odds are it will be too small. If you had, say, monthly files for three years, I think that would blow it out right there. And if you had to link different tables to different sheets because they are separated monthly (again, as you alluded to in the first post), you are flat out doomed on this being done through Access with external linked sources.

The ONLY way you will handle this that I can see is to combine ALL of these sheets into a single table. You would have the month and year as a field in the table so you could look up stuff for history. It would all be in one folder.

The next question is how many records are we talking about in aggregate across all folders, all years, and all monthly spreadsheets combined? That would also tell you whether Access has a shot at this or whether you would need something more along the lines of an SQL Server (or other active SQL engine) for a back end.
 

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
I think I had a different understanding of these documents as to what was needed. Entries will be copied to the allocation sheets and may need to be edited during the during the month.

However, on the 25th of each month, an invoice will need to be sent for the current month, made by incorporating several columns from the allocation sheet. Once that invoice has been sent, the allocation sheet for that month will become static and will not be changed.

I want to have a search field that will search through every row in past allocation sheets and return the entire row if a match is found somewhere in that row.

I only want to search through allocation sheets that have already become static.


I know everyone thought that this needed access to accomplish but with my new understanding of it, could it be done in excel?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2013
Messages
16,553
you can adapt code in my original post to do this in excel. Whether this is the right way to do it, only you can say.


Code:
have user complete a criteria range in a workbook
on the click of a button
loop through directory for workbooks that start with the format #### - ####
   open each workbook found
      loop through each worksheet applying criteria to the sql provided- and filter out any combination of filename, worksheet name that is later than last month
         copy lines to a spare worksheet

the sql needs to be adapted to use monthname as the sheet name and also to include the criteria
Code:
sqlStr="SELECT *
FROM (SELECT * FROM [" & monthname & "$A4:P10000] as xlData IN '" & pfname & "'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL WHERE " & criteria
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:42
Joined
Feb 19, 2002
Messages
42,970
How many people are involved in updating the workbooks? How many workbooks are there? Is the format consistent? Do all the workbooks start with July?
 

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
There will be 2 people that update the allocation sheets. I just need to focus on what has happened in the past at this stage so there will be 17 documents. I have some further understanding of what is needed now.

I want to search through the workbooks and find rows that have a match somewhere in the row. If it does, I want that entire row copied to a new workbook.


The workbooks have not got a consistent format. It has been the same up until this year, where I have changed it, but the columns have mostly similar information. It doesn't matter that the same information might be in different columns for different rows, I just need that row copied to a new document if there is a match in that row.



The filenames are not consistent, however, they all will have the words "Work Allocation" somewhere in the file. They are stored in the same folder. The sheet names up until this year were month year, for instance, "March 2018" and this year have started using just the month name.


The first sheet in the workbook is not consistent over the workbooks but I need to search through every sheet in the document.




I need all the rows that have a match from every document put into one sheet in the new document. This will allow me to see a summary of information regarding to the search query.


Thanks for this help,
Dave
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:42
Joined
Feb 28, 2001
Messages
26,996
The workbooks have not got a consistent format. It has been the same up until this year, where I have changed it, but the columns have mostly similar information.

Obviously you thought this was for a business need, but the truth is that you need consistency to manage search such as you described. Whether you are doing this in Access or Excel, you are compounding problems by changing data formats. Excel VBA and Access VBA would have more or less the same level of difficulty because of that inconsistent format. An Access query would have extreme difficulty if you tried to map a spreadsheet to a table only to find inconsistency of design/content.

One of the most important concepts that Access employs for you makes data consistency implicit - the fact that for a given table there is one and ONLY one tabledef structure with one and ONLY one set of field definitions. That means your code can "predict" where it needs to look for specific data elements.

The concept of data consistency means that you need only one set of code to manage searches. For every different format you use, you need ANOTHER set of search code for that particular file, and a way to know which one to use for your search. Excel does not impose any part of this consistency because technically, every cell in an Excel spreadsheet is isolated. Oh, it is true that a cell can reference another cell. But it is also true that a given cell might have NO external references. Nothing else will "care" about its format because in Excel, every cell can be different and it still works OK.
 

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
Come to think of it, there is only 2 layouts for the allocation sheets that will be used and I know the layouts, so I guess there will be some consistency. They will be from either the old format or the new one I have created. Does this make it any easier?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2013
Messages
16,553
Since you want to search every workbook, every worksheet, every row and every column you will need to do this in vba looping through each one in turn for a match.

You still have not said what your supervisor wants to do with the data once 'found' or indeed what 'search' actually means - a single term or multiple terms, using 'Like, >, <, and's and or's etc - so no idea whether you also need to return workbook/worksheet names as well and if you have different column headings then you either need a 'mapping table' to map back to a consistent result or return multiple reports.

Excel cells are a variant datatype which is why you can have text and numbers in the same column. So you will need to force all excel data and search terms to be text which may give problems with matching dates or whether numbers need to account for formatting, etc.

So does it make it any easier only having two layouts? Perhaps a little bit.

suggest you try some of the example solutions provided and try it. Perhaps you take just one format and get that working, then modify to include multiple formats. If the change is historic, perhaps it would be easier to modify the historic files to match the current format.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:42
Joined
Feb 19, 2002
Messages
42,970
Before we get into the "searching", you need to decide if you are going to import the data into Access and let Access be the master? Once you standardize the format, the searching will be much easier. In Access, you can ensure that data doesn't get updated once it is "closed". You can't do that in Excel. You hare to rely on people to not make mistakes.

The hardest part will be convincing the users that it is in their interest to use Access as the master and you will need to create an application for them that shelters them from Access. All they need to do is enter/update data and run reports, searches, and possibly exports. They will never see the underlying database objects.
 

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
My supervisor has agreed to have this search tool only analyse the allocation sheets from past years that have already been closed off and will not be changed. Therefore, they will all have the same format.


My supervisor wants a query tool that is able to query the past data for decision making.
 

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
How should I link to all the data. Should I import it to access from my workbooks or should I link to the files?


Also, if I import, is there a better way then manually importing every sheet?
 

david.paton

Registered User.
Local time
Today, 02:42
Joined
Jun 26, 2013
Messages
338
With the previous questions of how I should I link it, I want to search through every row in the table or multiple tables and return every record that matches a search string. Should I import every row from each sheet into one table in order to be able to search through them or do i kink to the tables?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2013
Messages
16,553
since the data won't change, I would import the data to an access table

you can do multiple files using code along the lines I suggested originally in post #3
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:42
Joined
Feb 28, 2001
Messages
26,996
The issue of using only the "old" sheets means that now you can do imports consistently. However, the number of files mentioned earlier makes me wonder if it wouldn't be a good idea to import all that data once and include as part of the imported data that you would have a list of files already imported. Then you can run your import process and ignore files already imported, only appending new data to the master table.

I see that CJ made the "import" suggestion as well. I'll second his proposal.
 

Users who are viewing this thread

Top Bottom