Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-15-2019, 08:03 PM   #1
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 338
Thanks: 55
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
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 by david.paton; 09-19-2019 at 03:36 PM.
david.paton is offline   Reply With Quote
Old 09-15-2019, 08:06 PM   #2
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 338
Thanks: 55
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: I a little direction in starting my project

For every month in the allocation sheets, the header row is in row 3 and the date starts in row 4.
david.paton is offline   Reply With Quote
Old 09-16-2019, 01:21 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,236
Thanks: 40
Thanked 3,644 Times in 3,514 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: I a little direction in starting my project

little unclear as to the requirement. you say

Quote:
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?

Quote:
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?

Quote:
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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 09-16-2019 at 01:40 AM.
CJ_London is offline   Reply With Quote
Old 09-16-2019, 01:54 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,401
Thanks: 68
Thanked 2,702 Times in 2,587 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: I a little direction in starting my project

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-16-2019, 10:47 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: I a little direction in starting my project

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-17-2019, 05:42 PM   #6
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 338
Thanks: 55
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: I need a little direction in starting my project

Quote:
Originally Posted by CJ_London View Post
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.
Attached Files
File Type: zip allocation sheets.zip (93.9 KB, 8 views)

Last edited by david.paton; 09-17-2019 at 07:14 PM.
david.paton is offline   Reply With Quote
Old 09-17-2019, 06:25 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,356
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Quote:
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.

The_Doc_Man is offline   Reply With Quote
Old 09-17-2019, 07:46 PM   #8
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 338
Thanks: 55
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: I a little direction in starting my project

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?
david.paton is offline   Reply With Quote
Old 09-18-2019, 12:40 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,236
Thanks: 40
Thanked 3,644 Times in 3,514 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: I a little direction in starting my project

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
CJ_London is offline   Reply With Quote
Old 09-18-2019, 05:08 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: I a little direction in starting my project

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?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-19-2019, 03:32 PM   #11
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 338
Thanks: 55
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: I a little direction in starting my project

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
david.paton is offline   Reply With Quote
Old 09-19-2019, 06:14 PM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,356
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: I a little direction in starting my project

Quote:
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-19-2019, 07:10 PM   #13
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 338
Thanks: 55
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: I a little direction in starting my project

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?
david.paton is offline   Reply With Quote
Old 09-20-2019, 12:51 AM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,236
Thanks: 40
Thanked 3,644 Times in 3,514 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: I a little direction in starting my project

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-20-2019, 08:42 PM   #15
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: I a little direction in starting my project

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
New Project - Need some direction NetEcho General 4 02-13-2014 04:25 PM
New Learning Project for me. Starting guidance please JayJay00 General 3 07-01-2011 12:06 PM
Access 2003: Create Project and add Records to Project spudracer Queries 7 01-20-2011 01:17 PM
Starting a new project Jason1 General 7 03-16-2010 01:35 PM




All times are GMT -8. The time now is 03:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World