Hi I have a ms access database with 6 access tables.the 6 tables are: 1.Lighting consumption 2. Refrigeration consumption 3. HVAC consumption 4. Bakery consumption. 5.BioGas consumption 6. Main Meter consumption. Each table has the following fields 1.Store Number 2. Meter Reading Date and 3. Energy Meter Reading.every week the user has to delete previous week data i.e example :25th March 12 till 31st March 12 and then reupload the data from 25th March 12 till 7th April 12 (2 weeks data to be uploaded for preparing the report), for all these 6 tables . the data is in an excel sheet .i want to have a macro or button in excel which gives me the option to select the database name, table name and then append the data to the table. suppose i want to delete one week worth of data there should be an option to select the date range from when i want to delete the data .Once i delete the data there should be an option to upload or append as we say fresh in each of the respective tables .i have to do the same exercise for 6 tables in that database .could anyone help me out with an example or readymade file if available. my users are not ms access friendly so i want to give them and excel file where on click of a button they have an option to select the database and then table and then upload the data and also by giving a date range they can delete the data in the ms access database using excel as the front end
Appending , Deleting data in access database using excel
- Thread starter paulrh
- Start date
SirJB7
Excel Rōnin
Hi, paulrh!
I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, well, come back here, tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
Regards!
PS: About your question, many questions arise.
a) the Access file has only the data for 1 week, for 2 weeks or for all weeks historically?
b) that's to say, the user deletes rows from there each week or just copy/paste/import data from 1 or 2 weeks into its Excel file?
c) the macro you're asking for, should delete data from the Excel file or from the Access file?
I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, well, come back here, tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
Regards!
PS: About your question, many questions arise.
a) the Access file has only the data for 1 week, for 2 weeks or for all weeks historically?
b) that's to say, the user deletes rows from there each week or just copy/paste/import data from 1 or 2 weeks into its Excel file?
c) the macro you're asking for, should delete data from the Excel file or from the Access file?
Hi SirJB7,
This is a weekly report circulated to the management through out the year. in our company a week starts from Monday to Sunday. so on 9th April 12(Monday) when i am goin to do this report i must download 2 weeks data i.e: from 26th March 12(Monday) till 8th April 2012(Sunday).Now once this data is there I have to delete the old data i.e:from 26th March 2012(Monday) till 1st april 2012(Sunday)which was used for the previous weeks report, and then reupload the fresh data which I have downloaded, into the access database.This report is done in excel at the moment but i want to build a access database so that at any point of time the user does not have to browse through every weeks report. my users are not ms access friendly so im trying to build an excel application where in the excel worksheet there should be an option where the user can delete the previous week's data in access tables by specifying dates in the user friendly excel interface and then reupload the fresh data in those 6 access tables . then once the user clicks a button the report is downloaded automatically from the access database in the excel workbook in the form of charts.the user should have the functionality to select the dates for which the report should be downloaded. i have built queries in access which perform the necessary calculations but then the user has to download the data in excel and then prepare charts manually .i dont know the technique in which the user can delete the data specifying dates then upload excel data in the 6 access tables and how we can by a click of a button in excel retrieve the report in the form of dynamic charts in excel.I said dynamic charts becoz every week the report has to be prepared based on specifying previous 2 weeks dates.The macro im asking for should delete data in the access table.im not so good in excel vba so im bit stuck on how to do things.basically im working for the energy department so i haave to prepare this report for lighting, refrigeration , hvac, bakery energy consumption and whether consumption has increased in any of these circuits.if its possible i would like to share my files which are presently being used or im avaibale on skype with userid : paulh3225,if available on skype i can share my desktop anad show the details of this report .
This is a weekly report circulated to the management through out the year. in our company a week starts from Monday to Sunday. so on 9th April 12(Monday) when i am goin to do this report i must download 2 weeks data i.e: from 26th March 12(Monday) till 8th April 2012(Sunday).Now once this data is there I have to delete the old data i.e:from 26th March 2012(Monday) till 1st april 2012(Sunday)which was used for the previous weeks report, and then reupload the fresh data which I have downloaded, into the access database.This report is done in excel at the moment but i want to build a access database so that at any point of time the user does not have to browse through every weeks report. my users are not ms access friendly so im trying to build an excel application where in the excel worksheet there should be an option where the user can delete the previous week's data in access tables by specifying dates in the user friendly excel interface and then reupload the fresh data in those 6 access tables . then once the user clicks a button the report is downloaded automatically from the access database in the excel workbook in the form of charts.the user should have the functionality to select the dates for which the report should be downloaded. i have built queries in access which perform the necessary calculations but then the user has to download the data in excel and then prepare charts manually .i dont know the technique in which the user can delete the data specifying dates then upload excel data in the 6 access tables and how we can by a click of a button in excel retrieve the report in the form of dynamic charts in excel.I said dynamic charts becoz every week the report has to be prepared based on specifying previous 2 weeks dates.The macro im asking for should delete data in the access table.im not so good in excel vba so im bit stuck on how to do things.basically im working for the energy department so i haave to prepare this report for lighting, refrigeration , hvac, bakery energy consumption and whether consumption has increased in any of these circuits.if its possible i would like to share my files which are presently being used or im avaibale on skype with userid : paulh3225,if available on skype i can share my desktop anad show the details of this report .
SirJB7
Excel Rōnin
Hi, paulrh!
Yet skyped but in the meanwhile:
- "when i am goin to do this report i must download 2 weeks data": source and target? from where to where?
- "Now once this data is there I have to delete the old data": from target?
- "then reupload the fresh data which I have downloaded, into the access database": to source? source is the Access file and target the Excel file, or there are two database files, at least one in Access -target- and the other under any engine?
- "This report is done in excel at the moment": so target is the Excel file?
- Can you please upload sample files? it would help a lot for fully understanding
Regards!
Yet skyped but in the meanwhile:
- "when i am goin to do this report i must download 2 weeks data": source and target? from where to where?
- "Now once this data is there I have to delete the old data": from target?
- "then reupload the fresh data which I have downloaded, into the access database": to source? source is the Access file and target the Excel file, or there are two database files, at least one in Access -target- and the other under any engine?
- "This report is done in excel at the moment": so target is the Excel file?
- Can you please upload sample files? it would help a lot for fully understanding
Regards!
Hi SirJB7,
1. every week we get a sd list and we update the store master which is a hidden sheet in the UKTG_macro.xls
2. every week get the temperature details also which is daily wise which is updated in the UKTG_macro.xls
3. the data for lighting , hvac , refirg, others and mpan is downloaded and cleaned for any data inconsistency
4. the summary is then pasted in the uktg_macro.xls sheet . as an example in Others.xls file go to c885 and copy the data from d885 till q890 ad paste it in uktg_macro.xls work book and in sheet uktg_data and in b60 cell.this same process is done for Lighting , hvac, refrig also.
5. for All Sites.xls a slight different process is followed . first the data is downloaded in MPAn sheet . then only for those stores in MPAN sheet we collect the CHP data and place it in CHP sheet. In SE (MPAN+CHP) sheet we add MPAN + CHP data storewise.
6. we then copy cell d2635 till q2640 and paste it in uktg_macro.xls(UKTG_DATA) sheet in cell B20
7. Now in UKTG_MACRO.xls workbook we have Store electricity which is MPAN+CHP, HVAC, Refrig, Lighting and others.
8. in all sites work book we have a sheet called submetered, what we do basically is take a unique store list from all workbooks(Lighting, refrig, others and HVAC) and then we do a vlookup of the data from SE (MPAN+CHP) sheet. This is how we get submetered data.
9. for unmetered calculation we again take the unique store list from all workbooks(Lighting, refrig, others and HVAC). Basically the unmetered calculation goes like this Unmetered= Submetered+(HVAC+ighting+Others+Refrig).
10. there are 2 important criterias in umetered calculation for any store and for any day there should be no negative value , if there is that data is deleted manually for that store and secondaly if data is not present in any of the circuits i.e: in lighting, hvac, refrig, others, submeterd then that store unmetered calculation is considered as #N/A.
11. once the unmeterd data is ready the summary is copied like other fiels in the UKTG_Macro.xls file
12. chp summary data is taken from a separate file
13. Now we have summary data ready in the UKTG_DATA Sheet .
14. then we go to the uktg_final sheet present in the uktg_macro workbook and then format wise we start pasting the data in UKTG Commentary Week 01.xls file. For example for express format we copy data from a2:k15 in UKTG_final sheet (UKTG_MACRO.xls) into Express Data sheet of UKTG Commentary Week 01.simulataeously we do this for all formats like extra, metro, superstore, dotcom, homeplus.
15. then in weekly summary of UKTG Commentary Week 01.xls workbook we refresh the data and we get the report ready.
* What im trying to build is every week the user uploads data in the access database for lighting, others, refrig, hvac mpan and chp,latest store master list, temperature details and with a click of a button on an excel workbook the report is generated. I am also uploading the piece of work I have done till now .plus im also trying to represent the data in the form of geographical maps.
please find the links where i have uploaded the data.
http://www.mediafire.com/download.php?v6q56wwr2638tex
http://www.mediafire.com/download.php?93qoxodbczim442
http://www.mediafire.com/download.php?uwxgra4f9w3dodn
http://www.mediafire.com/download.php?92gawbami3cycbh
http://www.mediafire.com/download.php?iwbth56bl3t37n7
http://www.mediafire.com/download.php?3a7x28bnkg64o6v
http://www.mediafire.com/download.php?b3251hcpe3m4ods
http://www.mediafire.com/download.php?149zd3veck0e1bf
http://www.mediafire.com/download.php?oc656gae40o2f5r
http://www.mediafire.com/download.php?jxjhycse4g6ktde
http://www.mediafire.com/download.php?xa011p34qtzayai
http://www.mediafire.com/download.php?dahb45dld023bph
http://www.mediafire.com/download.php?jpbfy5gzajj1s5s
http://www.mediafire.com/download.php?9crfzcm3k18hfx7
http://www.mediafire.com/download.php?vy5ag5i8tub65l8
http://www.mediafire.com/download.php?5cwjds5lv6q7358
1. every week we get a sd list and we update the store master which is a hidden sheet in the UKTG_macro.xls
2. every week get the temperature details also which is daily wise which is updated in the UKTG_macro.xls
3. the data for lighting , hvac , refirg, others and mpan is downloaded and cleaned for any data inconsistency
4. the summary is then pasted in the uktg_macro.xls sheet . as an example in Others.xls file go to c885 and copy the data from d885 till q890 ad paste it in uktg_macro.xls work book and in sheet uktg_data and in b60 cell.this same process is done for Lighting , hvac, refrig also.
5. for All Sites.xls a slight different process is followed . first the data is downloaded in MPAn sheet . then only for those stores in MPAN sheet we collect the CHP data and place it in CHP sheet. In SE (MPAN+CHP) sheet we add MPAN + CHP data storewise.
6. we then copy cell d2635 till q2640 and paste it in uktg_macro.xls(UKTG_DATA) sheet in cell B20
7. Now in UKTG_MACRO.xls workbook we have Store electricity which is MPAN+CHP, HVAC, Refrig, Lighting and others.
8. in all sites work book we have a sheet called submetered, what we do basically is take a unique store list from all workbooks(Lighting, refrig, others and HVAC) and then we do a vlookup of the data from SE (MPAN+CHP) sheet. This is how we get submetered data.
9. for unmetered calculation we again take the unique store list from all workbooks(Lighting, refrig, others and HVAC). Basically the unmetered calculation goes like this Unmetered= Submetered+(HVAC+ighting+Others+Refrig).
10. there are 2 important criterias in umetered calculation for any store and for any day there should be no negative value , if there is that data is deleted manually for that store and secondaly if data is not present in any of the circuits i.e: in lighting, hvac, refrig, others, submeterd then that store unmetered calculation is considered as #N/A.
11. once the unmeterd data is ready the summary is copied like other fiels in the UKTG_Macro.xls file
12. chp summary data is taken from a separate file
13. Now we have summary data ready in the UKTG_DATA Sheet .
14. then we go to the uktg_final sheet present in the uktg_macro workbook and then format wise we start pasting the data in UKTG Commentary Week 01.xls file. For example for express format we copy data from a2:k15 in UKTG_final sheet (UKTG_MACRO.xls) into Express Data sheet of UKTG Commentary Week 01.simulataeously we do this for all formats like extra, metro, superstore, dotcom, homeplus.
15. then in weekly summary of UKTG Commentary Week 01.xls workbook we refresh the data and we get the report ready.
* What im trying to build is every week the user uploads data in the access database for lighting, others, refrig, hvac mpan and chp,latest store master list, temperature details and with a click of a button on an excel workbook the report is generated. I am also uploading the piece of work I have done till now .plus im also trying to represent the data in the form of geographical maps.
please find the links where i have uploaded the data.
http://www.mediafire.com/download.php?v6q56wwr2638tex
http://www.mediafire.com/download.php?93qoxodbczim442
http://www.mediafire.com/download.php?uwxgra4f9w3dodn
http://www.mediafire.com/download.php?92gawbami3cycbh
http://www.mediafire.com/download.php?iwbth56bl3t37n7
http://www.mediafire.com/download.php?3a7x28bnkg64o6v
http://www.mediafire.com/download.php?b3251hcpe3m4ods
http://www.mediafire.com/download.php?149zd3veck0e1bf
http://www.mediafire.com/download.php?oc656gae40o2f5r
http://www.mediafire.com/download.php?jxjhycse4g6ktde
http://www.mediafire.com/download.php?xa011p34qtzayai
http://www.mediafire.com/download.php?dahb45dld023bph
http://www.mediafire.com/download.php?jpbfy5gzajj1s5s
http://www.mediafire.com/download.php?9crfzcm3k18hfx7
http://www.mediafire.com/download.php?vy5ag5i8tub65l8
http://www.mediafire.com/download.php?5cwjds5lv6q7358
SirJB7
Excel Rōnin
Hi, paulrh!
Can't say you haven't uploaded a sample file
I'm reading your instructions, opening files, and switching once and again between them.
In the meanwhile, maybe you find interesting these articles from this site:
http://chandoo.org/forums/topic/using-access-to-store-data-for-excel-dashboard#post-17076
http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/
http://chandoo.org/forums/topic/how-to-sort-data-from-one-sheet-to-another#post-23093 (vijaySharma's post)
There you'll find a lot of stuff regarding how to interact between Office products (Excel & Access), examples of how to use Excel as a DB (for using Access as a DB you'd only change the driver connection).
BTW and as we accorded, give a look at this file (borrowed without permission from vijaySharma's post and adapted to your requirements):
http://dl.dropbox.com/u/60558749/Urgent%20Help_%20Appending%20%2C%20Deleting%20data%20in%20access%20database%20using%20excel%20%28for%20paulrh%20at%20chandoo.org%29.zip
In the workbook you have a Select button, an Insert button, an Update button and a Delete button. Each one of them is supposed to perform the same stuff as the Select, Update and Delete queries of the database and as the only Insert macro.
It intends to let you interact with Access from within Excel without any user stressing.
As we just finished talking about and because of time runout, the code under the Insert button doesn't work, but you can try replacing the code there by something like this (extracted from http://www.mrexcel.com/forum/showthread.php?t=82820):
-----
`Dim DB As Object
Dim pth As String
pth = Activeworkbook.Path &"DataBaseName.mdb"
Set DB = CreateObject("Access.Application")
DB.Visible = False
DB.OpenCurrentDatabase (pth)
DB.Docmd.RunMacro "mcrMaster" ' Macro name in DataBase
Set DB = Nothing
-----
Good luck and tell us how did you went on.
Regards!
PS: I assumed you were using Office 2003 (.mdb and .xls file extensions)
PS2: Very clear and detailed explanation of your issue, but next time consider zipping all the files and uploading only one link
Can't say you haven't uploaded a sample file
I'm reading your instructions, opening files, and switching once and again between them.
In the meanwhile, maybe you find interesting these articles from this site:
http://chandoo.org/forums/topic/using-access-to-store-data-for-excel-dashboard#post-17076
http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/
http://chandoo.org/forums/topic/how-to-sort-data-from-one-sheet-to-another#post-23093 (vijaySharma's post)
There you'll find a lot of stuff regarding how to interact between Office products (Excel & Access), examples of how to use Excel as a DB (for using Access as a DB you'd only change the driver connection).
BTW and as we accorded, give a look at this file (borrowed without permission from vijaySharma's post and adapted to your requirements):
http://dl.dropbox.com/u/60558749/Urgent%20Help_%20Appending%20%2C%20Deleting%20data%20in%20access%20database%20using%20excel%20%28for%20paulrh%20at%20chandoo.org%29.zip
In the workbook you have a Select button, an Insert button, an Update button and a Delete button. Each one of them is supposed to perform the same stuff as the Select, Update and Delete queries of the database and as the only Insert macro.
It intends to let you interact with Access from within Excel without any user stressing.
As we just finished talking about and because of time runout, the code under the Insert button doesn't work, but you can try replacing the code there by something like this (extracted from http://www.mrexcel.com/forum/showthread.php?t=82820):
-----
`Dim DB As Object
Dim pth As String
pth = Activeworkbook.Path &"DataBaseName.mdb"
Set DB = CreateObject("Access.Application")
DB.Visible = False
DB.OpenCurrentDatabase (pth)
DB.Docmd.RunMacro "mcrMaster" ' Macro name in DataBase
Set DB = Nothing
-----
Good luck and tell us how did you went on.
Regards!
PS: I assumed you were using Office 2003 (.mdb and .xls file extensions)
PS2: Very clear and detailed explanation of your issue, but next time consider zipping all the files and uploading only one link
Hi SirJB7,
I have detailed the entire report making process stepwise in the excel file uktg description.xls, which I have zipped and also I have zipped the files in one folder for your viewing . Step 8 and Step 9 is the place I would require help in creating this application.
http://www.mediafire.com/download.php?g36klryj30bblqe
I have detailed the entire report making process stepwise in the excel file uktg description.xls, which I have zipped and also I have zipped the files in one folder for your viewing . Step 8 and Step 9 is the place I would require help in creating this application.
http://www.mediafire.com/download.php?g36klryj30bblqe
dan_l
Active Member
---
Im comfortable with ms access queries but im not so good in excel vba and sql so im bit stuck with this development.
----
Emmm it's been a while since I've done this:
dim connection as string
dim recordset as adodb.recordset
dim sql as string
connection = (whatever connection string)
sql = whatever you want your sql statement to be
set recordset = new recordset
recordset.open sql,connect
The sql is pretty easy. I think you can just build the queries in access an, copy the sql out, paste it into the vba and you're done.
Im comfortable with ms access queries but im not so good in excel vba and sql so im bit stuck with this development.
----
Emmm it's been a while since I've done this:
dim connection as string
dim recordset as adodb.recordset
dim sql as string
connection = (whatever connection string)
sql = whatever you want your sql statement to be
set recordset = new recordset
recordset.open sql,connect
The sql is pretty easy. I think you can just build the queries in access an, copy the sql out, paste it into the vba and you're done.
Hi SirJB7,
I have reuploaded only that file.
http://www.mediafire.com/download.php?o876dc75san4e4o
In the Upload Data to Access Table.xls file all my requirements are met only one main thing which I wanted in that sheet is, the user can have the option to select the desired access database and then the desired access table and then he should have an option in the Upload Data to Access Table.xls file where he can specify the dates From and To and then he can delete the data for a particular access table, say fro example he can delete the data for lighting table.
One more suggestion if you can tell me how best I can represent those 2 reports in excel, i.e exporting the data from access database and then representing it in the form of an excel dashboard. as I specified in my recent post i want to present the data either in the form of a dashboard or a geographical dashboard,i.e: in the form of a uk map and then the user can just click any particular map say midlands(which is state in uk) and then the information pops up in another screen with all the data in the form of charts . so if you can guide me on that.
with regards, to your previous question, I am using access 2003 and excel 2007 for this development .
I have reuploaded only that file.
http://www.mediafire.com/download.php?o876dc75san4e4o
In the Upload Data to Access Table.xls file all my requirements are met only one main thing which I wanted in that sheet is, the user can have the option to select the desired access database and then the desired access table and then he should have an option in the Upload Data to Access Table.xls file where he can specify the dates From and To and then he can delete the data for a particular access table, say fro example he can delete the data for lighting table.
One more suggestion if you can tell me how best I can represent those 2 reports in excel, i.e exporting the data from access database and then representing it in the form of an excel dashboard. as I specified in my recent post i want to present the data either in the form of a dashboard or a geographical dashboard,i.e: in the form of a uk map and then the user can just click any particular map say midlands(which is state in uk) and then the information pops up in another screen with all the data in the form of charts . so if you can guide me on that.
with regards, to your previous question, I am using access 2003 and excel 2007 for this development .
SirJB7
Excel Rōnin
Hi, paulrh!
Here's the link to the "Upload Data to Access Table.xls" workbook:
http://dl.dropbox.com/u/60558749/Urgent%20Help_%20Appending%20%2C%20Deleting%20data%20in%20access%20database%20using%20excel%20-%20Upload%20Data%20to%20Access%20Table%20%28for%20paulrh%20at%20chandoo.org%29.zip
For the moment, I only changed the table names that are loaded in the hidden worksheet. The code intended to exclude system tables (MSys*) but not all of them, also filtered table type "TABLE" for omitting queries as stated but undone ("VIEW"), and temporary tables "~TMP*" (created from Excel code).
If using Excel 2007, why still using .xls 2003 files instead .xlsx 2007 ones? When saved a message of compatibility is displayed, among other considerations.
I have a few questions:
a) all the 11 tables are suitable for this procedure or only certain tables? (SD By Weather Region hasn't a field called Date, for example, neither SD OD List, not Sheet1)
b) which tables should be displayed in the dropdown list box? Maybe you want to standardize the names suitable so as to filter them.
c) adding two date cells (from/to) will be enough? Cells for checking values against Date field.
d) adding two "buttons" (upload and download?) will be enough? Please confirm source and target: upload from Access to Excel and download from Excel to Access or conversely? Please specify Excel ranges in detail. I'm getting two bits confused...
Regards!
Here's the link to the "Upload Data to Access Table.xls" workbook:
http://dl.dropbox.com/u/60558749/Urgent%20Help_%20Appending%20%2C%20Deleting%20data%20in%20access%20database%20using%20excel%20-%20Upload%20Data%20to%20Access%20Table%20%28for%20paulrh%20at%20chandoo.org%29.zip
For the moment, I only changed the table names that are loaded in the hidden worksheet. The code intended to exclude system tables (MSys*) but not all of them, also filtered table type "TABLE" for omitting queries as stated but undone ("VIEW"), and temporary tables "~TMP*" (created from Excel code).
If using Excel 2007, why still using .xls 2003 files instead .xlsx 2007 ones? When saved a message of compatibility is displayed, among other considerations.
I have a few questions:
a) all the 11 tables are suitable for this procedure or only certain tables? (SD By Weather Region hasn't a field called Date, for example, neither SD OD List, not Sheet1)
b) which tables should be displayed in the dropdown list box? Maybe you want to standardize the names suitable so as to filter them.
c) adding two date cells (from/to) will be enough? Cells for checking values against Date field.
d) adding two "buttons" (upload and download?) will be enough? Please confirm source and target: upload from Access to Excel and download from Excel to Access or conversely? Please specify Excel ranges in detail. I'm getting two bits confused...
Regards!
a) only the following tables CHP, HVAC, Lighting, MPAN, Others, Refrig, SD OD List, Temperature are to be updated with fresh weekly data and correspondingly last week's data should be deleted from these tables before uploading new data.
In my HVAC_Query query if you see HVAC table has the Store number and every week we get the new SD OD List (every week stores may be opened in Tesco or some stores may be closed ).This List gives that information, so we are concerned with stores only which are operating.In the SD OD List there is a field called SD No which is like, we can say tag for a store , so there are about 50 SD numbers and the 200 tesco stores can belong to any one of these SD Numbers.
Now SD By Weather Region file is a standard file. This file Im using because it contains region details of united kingdom. Its not of much importance now, but when I prepare the final output that time suppose I want to show in Midlands for example how is Tesco performing according to each format , That time this file when joined with STore list as you see in my HVAC_query query will help to get these details.
b) as of now I feel its ok cos Il change the final naming convention to HVAC Table say, so the user will know he has to upload data to this table only
c) That file has all the details i wanted I just wanted to have an important option suppose for Lighting table I want to delete data from 1st jan 2010 Till 31st Feb 2012,I should be able to select the database, then the desired table name and then an option to specify the dates for which I want to delete data, i.e: example from 1st jan 2010 Till 31st Feb 2012
d) Yes, Upload and download button will be enough.We have to upload data from excel to access , since access is the source or as we say main DATABASE OF INFORMATION and excel is the place where the REPORT WILL BE PRESENTED ONLY FOR FINAL VIEWING.
I was just reading one blog just now it said its better to do the entire reporting function in ms access itself instead of using excel as the front end, and then with the help of a click of a button in access just export the reports or charts in excel viewable format. do you suggest such kind of a thing.
In my HVAC_Query query if you see HVAC table has the Store number and every week we get the new SD OD List (every week stores may be opened in Tesco or some stores may be closed ).This List gives that information, so we are concerned with stores only which are operating.In the SD OD List there is a field called SD No which is like, we can say tag for a store , so there are about 50 SD numbers and the 200 tesco stores can belong to any one of these SD Numbers.
Now SD By Weather Region file is a standard file. This file Im using because it contains region details of united kingdom. Its not of much importance now, but when I prepare the final output that time suppose I want to show in Midlands for example how is Tesco performing according to each format , That time this file when joined with STore list as you see in my HVAC_query query will help to get these details.
b) as of now I feel its ok cos Il change the final naming convention to HVAC Table say, so the user will know he has to upload data to this table only
c) That file has all the details i wanted I just wanted to have an important option suppose for Lighting table I want to delete data from 1st jan 2010 Till 31st Feb 2012,I should be able to select the database, then the desired table name and then an option to specify the dates for which I want to delete data, i.e: example from 1st jan 2010 Till 31st Feb 2012
d) Yes, Upload and download button will be enough.We have to upload data from excel to access , since access is the source or as we say main DATABASE OF INFORMATION and excel is the place where the REPORT WILL BE PRESENTED ONLY FOR FINAL VIEWING.
I was just reading one blog just now it said its better to do the entire reporting function in ms access itself instead of using excel as the front end, and then with the help of a click of a button in access just export the reports or charts in excel viewable format. do you suggest such kind of a thing.
SirJB7
Excel Rōnin
Hi, paulrh!
Open the workbook, go to the VBA editor, look for this procedure: Sub ListAccessTables(strDBPath As String)
Then check the following code:
-----
[pre]
[/pre]
-----
Check too the tables names retrieved by the previous version and by this version to decide if you keep this modifications (embedded between 'SirJB7 start'/'SirJB7 end' comments).
Now I'm going to read your post carefully.
Regards!
Open the workbook, go to the VBA editor, look for this procedure: Sub ListAccessTables(strDBPath As String)
Then check the following code:
-----
[pre]
Code:
Select Case tblList.Name
Case "MSysAccessObjects", "MSysAccessXML", "MSysACEs"
'Ignore System tables listed above
Case "MSysObjects", "MSysQueries", "MSysRelationships"
'Ignore System tables listed above
'SirJB7 start
Case "~TMP" To "~TMPz"
'Ignore temp tables
Case "MSys" To "MSysz"
'Ignore all system tables
Case Else
'Only tables
If tblList.Type = "TABLE" Then
'SirJB7 end
rTableNames = tblList.Name
Set rTableNames = rTableNames.Offset(1, 0)
'SirJB7 start
End If
'SirJB7 end
End Select-----
Check too the tables names retrieved by the previous version and by this version to decide if you keep this modifications (embedded between 'SirJB7 start'/'SirJB7 end' comments).
Now I'm going to read your post carefully.
Regards!
SirJB7
Excel Rōnin
Hi, paulrh!
About your answers:
a) SD OD List hasn't a Date field, it shouldn't be included in this general procedure
b) I think it's not Ok because the user has no restrictions about what table use, he can make mistakes with unexpected results. Consider filtering (by name format or structure) within the code which tables will you allow the user to work with
c) Ok, but note observation of a)
d) It sounds a little obscure uploading data to the main information database... and even more deleting data stored there... and even more doing it from the place where the report will be presented only for final viewing (the more I read it, the more I dislike it: it'd be better to just download from Access to Excel, filtering by dates, but not uploading (inserting) and less deleting)
About reporting in Access and presenting in Excel, in this case I agree, it'd be consistent with previous considerations, in d). I'd do everything in Access (or in Excel, or in the database you want and your users can handle), and from that source, the presentation in Excel should pass the dates from/to as arguments and download the portion of the report involving those dates. But I'd never delete from the source, just filter at download time (you can replicate the Access SQL query definition strings and place them within Excel, as in my paulrh.xls first example, and as dan_l posted later.
Summary:
1) source should be a part of main information system (whether both in Excel, in Access or mixed)
2) target should filter from source accordingly to dates from/to and perform only presentation
Of course, you have to review:
i. why have you defined to have the source in Access if your users don't know Access (i.e., why not everything in Excel)
ii. who, when and how will update the source (if your users, then Access wouldn't be the first choice)
If you want to keep the Access source option, I recommend you to constraint operations on its data from Excel to filter and download: neither uploading nor deleting.
Regards!
About your answers:
a) SD OD List hasn't a Date field, it shouldn't be included in this general procedure
b) I think it's not Ok because the user has no restrictions about what table use, he can make mistakes with unexpected results. Consider filtering (by name format or structure) within the code which tables will you allow the user to work with
c) Ok, but note observation of a)
d) It sounds a little obscure uploading data to the main information database... and even more deleting data stored there... and even more doing it from the place where the report will be presented only for final viewing (the more I read it, the more I dislike it: it'd be better to just download from Access to Excel, filtering by dates, but not uploading (inserting) and less deleting)
About reporting in Access and presenting in Excel, in this case I agree, it'd be consistent with previous considerations, in d). I'd do everything in Access (or in Excel, or in the database you want and your users can handle), and from that source, the presentation in Excel should pass the dates from/to as arguments and download the portion of the report involving those dates. But I'd never delete from the source, just filter at download time (you can replicate the Access SQL query definition strings and place them within Excel, as in my paulrh.xls first example, and as dan_l posted later.
Summary:
1) source should be a part of main information system (whether both in Excel, in Access or mixed)
2) target should filter from source accordingly to dates from/to and perform only presentation
Of course, you have to review:
i. why have you defined to have the source in Access if your users don't know Access (i.e., why not everything in Excel)
ii. who, when and how will update the source (if your users, then Access wouldn't be the first choice)
If you want to keep the Access source option, I recommend you to constraint operations on its data from Excel to filter and download: neither uploading nor deleting.
Regards!