Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-03-2019, 05:47 PM   #16
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

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 is offline   Reply With Quote
Old 10-03-2019, 07:25 PM   #17
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

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 is offline   Reply With Quote
Old 10-03-2019, 07:52 PM   #18
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

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?

david.paton is offline   Reply With Quote
Old 10-03-2019, 11:45 PM   #19
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 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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 10-04-2019, 06:00 AM   #20
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

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.
__________________
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 10-10-2019, 03:09 AM   #21
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

In response to post 19, you say to import the data to a table in access CJ but should I put it in one table as there will be data from about 7 spreadsheets, each spreadsheet has 12 sheets for the months of the year and each sheet would have anywhere between approximately 100-400 lines. Would this be too much for a single table in access to handle?
david.paton is offline   Reply With Quote
Old 10-10-2019, 05:26 AM   #22
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

Quote:
Would this be too much for a single table in access to handle?
No, depends on how many columns but I have db's with millions of records. You are talking about 30,000 which is not much at all.

If you need to know which workbook and/or which worksheet a record came from then make sure you include columns for these as well as the data


__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   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 02:53 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