If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
Membership database updates
In the Criteria cell of DateJoined in QryNewMembers I had put a date of the
form Between #dd/mm/yyyy# And #dd/mm/yyyy# and got a list of members who joined between those dates. I now want it to work so that when I open the corresponding Report I am prompted to put the month or year and I get a list of members who joined in that month or year. I have tried the Expression builder but can't seem to get it to work. "Evi" wrote: Remember, you will eventually have a list of members who have been removed on various dates. You can put a text box in your report header and type =[DateRemoved] You can combine this date with text by typing ="Members Removed By " & [DateRemoved] It will show the DateRemoved of the first record though. This is fine if you are then going to filter the report so that you show only one group of removals at a time or if you sort your Dates in Descending Order in the sorting grouping box so it shows the highest date first. Or you can type =Min[DateRemoved] & " to " & Max[DateRemoved] so that you will see the lowest date on your report and the highest with the word 'to' in the middle. You can filter your report using a combo box in a form so you can select the date you want to see or print. Great fun! Or you can group your records, using the Sorting/Grouping box by this DateRemoved and put the DateRemoved field in the Group Header so that you can see the list of member who were grouped on 21/08/07, the group who were removed on 20/08/06 etc Evi "Pennington" wrote in message ... It works - brilliant. Many thanks for you help One more thing, how do I put the Date Member Removed in a Report box so it shows in the title of the report? I might have others later as I go through the Reports and Charts. Is it OK if I post them here? "Evi" wrote: You'll have no trouble doing it, if you've done all this but just test it all thoroughly on a copy of your db before letting it loose on your precious data. The thing the macro won't do is to do the copy /paste from Excel because you need to check visually if the columns are in the correct order. So, you've saved your Update & Append & Delete macros Click on the Macro page. Click New Under Action, in the first row, choose OpenQuery. Next to Query Name choose the name of your first query in Step 4 from the dropdown list In the next row in the Action column, choose OpenQuery again and next to Query Name choose the name of your next query from step 5. etc, etc To run the macro, go to the macro page and double click it. There are ways of doing it all without you having to OK each step but do try it this way for now and read any messages which appear. There could easily be something neither of us have thought of yet. That RemovedDate.... If you are going to use the date when you run the update then you can put Date() in the UpdateTo line under the RemovedDate field - the RemovedDate will be the current date when you run the query. Otherwise you are going to learn another trick - a parameter query In the NotInList Update query, in the UpdateTo line, in the RemovedDate column, type [What Removed Date?] Because you don't actually have field called What Removed Date? When that part of the macro runs, a box with the text What Removed Date? above a blank box. Type your RemovedDate into the box. Again, test on a copy to see if it works the way you expect it to. Evi "Pennington" wrote in message ... It works - brillant, thanks ever so much. I tried it out on a few records to prove to myself that it added new members, annoted removed members and changed current members details. I suppose the next step is to write a macro that executes all 7 steps? The only intervention would be to insert the Removed Date in Step 5. Can that be done? I havn't written macros in Access before. "Evi" wrote: I would suggest this as step 3 onwards. 3 You don't even need to import the Excel spreadsheet. Copy and paste your Excel data from the spreadsheet straight into your MembersUpdate table. Just make sure that the columns in the spreadsheet are in the same order as the fields on your table. (adjust the order of the fields in both your table if they aren't) and don't select more of the spreadsheet than the required rows and columns ie don't select a load of blank cells by selecting using the column headings. - the easiest way to select a long list is to start at the bottom right of the list and sweep the mouse upwards and left. To copy and paste into your new table, select the first row by clicking in the grey area to the left of the row (don't click in the field). Press Paste on the toolbar. 4.Run the Update query to update Members details from your ExcelTable (leave out the space when naming it). 5.Run the Find Unmatched and Update query to identify which members are no longer in your new list and Update their DateRemoved field. 6.After doing all else, Append all the members from your ExcelTable to your Members Table (duplicate MemberNumbers won't be imported because you presumably have set your Primary Key field to No Duplicates) 7.When everything looks right, run a delete query to empty your ExcelTable ready for the next import. Evi "Pennington" wrote in message ... OK lets see if I have got this right: 1. Back up my database 2. Create a copy of the Members table without the data. Name it Members Update table (This allows me to keep the field names which might be different/additional to the import data) 3. Import Excel data into a new table named Excel table (This is because the field names might be different) 4. Create and run an Append Query to add the data in the Excel table to the Members Update table (This is so that the field names can be matched) 5. Create and run an Append Query to add the new members to the Members table 6. Create and run an Unmatched Query to identify Members Removed and change it to an Update Query with the #Date Removed# in the "Update To" line, then run the Query. (I will have now identified ex-members and new members but not updated current members details if they have changed - this is the next step) 7. Create Update Query linking Members table and members Update table by MemberNumber with [Members Update Table]![fieldname] (except member number) in the Update To row and RUN Query (Member details are now uopdated 8. Create and run a Delete Query on the Members Update table and the Excel table to make them ready for receiving the next update. I could probably skip step 4 if I make sure the fields of the Excel file are the same as the Members table before I import it and import directly into the members Update Table. Anyway I will try this out and thanks for your help "Evi" wrote: Yes of course. Back up your db before you start these procedures in case they don't do what you expect them to. I suggest that when you import stuff from Excel, first use an Append query to put it into a table that has all the field names and data formats you require (ie a copy/paste of the Main table but without its data. That way, you can save all your append/update queries and use them next time because none of the field or Table names will have changed. Your final query in the series will be a Delete Query which will delete everything from this ImportTable ready for next time. .. Eventually, when you know that everything is working as you expect it to, you can create a macro which runs everything, one after the other but you can imagine the chaos it can create if it updates or appends the wrong thing and you don't even see it as it happens.. If you want to replace everything for that member (and of course, I should have thought of that!) different or not, then you can still use the Update query. Join the 2 tables by the MemberNumber in Query Design. Put all the fields from your *Current Table* (except MemberNumber) into your query grid. Change to an Update Query. In the UpdateTo row under each column, type eg [YourImportTablesName]![FirstName] or [YourImportTablesName]![Surname] etc Substituting with the correct Table and field names. Remember, these Update To entries must not have quote marks around them. Press the Red Exclamation mark and run the query. Evi "Pennington" wrote in message ... Thanks, I will try Evi's solution but when I read the blurb in the Access Help I got the impression that I had to type an expression or value in the |
#32
|
|||
|
|||
Membership database updates
Coding a command button, creating a Unique Query, Using DateSerial to find
the first and last date of available months: May I suggest that rather than using a parameter report that you create a blank form for filtering your reports and opening them. You can open several reports and queries from this form and it is much more flexible than the parameter query. Eventually, you can even put a button on this to run your importing Macro Click on your main table. Go to Insert Query. Call it QryDateJoined. Don't drag any fields from your table into your query. Instead, in the top row of the first 2 blank columns type StartDate: DateSerial(Year([DateJoined]),Month([DateJoined]),1) EndDate: DateSerial(Year([DateJoined]),Month([DateJoined])+1,0) (this last gives you the end date of the month) Click in the grey area below the white Query grid. Click Properties If you have clicked in the correct area, you will see one line which says Unique Values Choose Yes next to that. Put 2 combo boxes into your blank form. For the first one, choose only the first column of this query (StartDate). Click on this combo, click on Properties and the Other tab. Name the combo cboFromDate. for the second combo choose only the second column and name this combo cboToDate Add a command button to your form. Using the Wizard, choose Report Operations, Preview Report, and your report Click the Code Button on the toolbar of your form's design view. you will see the code that opens your report Just under where it says Dim stDocName As String type this Dim Crit as String If IsNull(Me.cboFromDate) Or IsNull(Me.cboToDate) Then 'if either of the combos are blank then open the report unfiltered Crit = "" Else Crit = "[DateJoined]=" & Format(Me.cboFromDate, "0") & " AND [DateJoined]=" & Format(Me.cboToDate, "0") End If Now change the line that says DoCmd.OpenReport stDocName, acPreview so that it now says DoCmd.OpenReport stDocName, acPreview, , Crit Evi "Pennington" wrote in message ... In the Criteria cell of DateJoined in QryNewMembers I had put a date of the form Between #dd/mm/yyyy# And #dd/mm/yyyy# and got a list of members who joined between those dates. I now want it to work so that when I open the corresponding Report I am prompted to put the month or year and I get a list of members who joined in that month or year. I have tried the Expression builder but can't seem to get it to work. "Evi" wrote: Remember, you will eventually have a list of members who have been removed on various dates. You can put a text box in your report header and type =[DateRemoved] You can combine this date with text by typing ="Members Removed By " & [DateRemoved] It will show the DateRemoved of the first record though. This is fine if you are then going to filter the report so that you show only one group of removals at a time or if you sort your Dates in Descending Order in the sorting grouping box so it shows the highest date first. Or you can type =Min[DateRemoved] & " to " & Max[DateRemoved] so that you will see the lowest date on your report and the highest with the word 'to' in the middle. You can filter your report using a combo box in a form so you can select the date you want to see or print. Great fun! Or you can group your records, using the Sorting/Grouping box by this DateRemoved and put the DateRemoved field in the Group Header so that you can see the list of member who were grouped on 21/08/07, the group who were removed on 20/08/06 etc Evi "Pennington" wrote in message ... It works - brilliant. Many thanks for you help One more thing, how do I put the Date Member Removed in a Report box so it shows in the title of the report? I might have others later as I go through the Reports and Charts. Is it OK if I post them here? "Evi" wrote: You'll have no trouble doing it, if you've done all this but just test it all thoroughly on a copy of your db before letting it loose on your precious data. The thing the macro won't do is to do the copy /paste from Excel because you need to check visually if the columns are in the correct order. So, you've saved your Update & Append & Delete macros Click on the Macro page. Click New Under Action, in the first row, choose OpenQuery. Next to Query Name choose the name of your first query in Step 4 from the dropdown list In the next row in the Action column, choose OpenQuery again and next to Query Name choose the name of your next query from step 5. etc, etc To run the macro, go to the macro page and double click it. There are ways of doing it all without you having to OK each step but do try it this way for now and read any messages which appear. There could easily be something neither of us have thought of yet. That RemovedDate.... If you are going to use the date when you run the update then you can put Date() in the UpdateTo line under the RemovedDate field - the RemovedDate will be the current date when you run the query. Otherwise you are going to learn another trick - a parameter query In the NotInList Update query, in the UpdateTo line, in the RemovedDate column, type [What Removed Date?] Because you don't actually have field called What Removed Date? When that part of the macro runs, a box with the text What Removed Date? above a blank box. Type your RemovedDate into the box. Again, test on a copy to see if it works the way you expect it to. Evi "Pennington" wrote in message ... It works - brillant, thanks ever so much. I tried it out on a few records to prove to myself that it added new members, annoted removed members and changed current members details. I suppose the next step is to write a macro that executes all 7 steps? The only intervention would be to insert the Removed Date in Step 5. Can that be done? I havn't written macros in Access before. "Evi" wrote: I would suggest this as step 3 onwards. 3 You don't even need to import the Excel spreadsheet. Copy and paste your Excel data from the spreadsheet straight into your MembersUpdate table. Just make sure that the columns in the spreadsheet are in the same order as the fields on your table. (adjust the order of the fields in both your table if they aren't) and don't select more of the spreadsheet than the required rows and columns ie don't select a load of blank cells by selecting using the column headings. - the easiest way to select a long list is to start at the bottom right of the list and sweep the mouse upwards and left. To copy and paste into your new table, select the first row by clicking in the grey area to the left of the row (don't click in the field). Press Paste on the toolbar. 4.Run the Update query to update Members details from your ExcelTable (leave out the space when naming it). 5.Run the Find Unmatched and Update query to identify which members are no longer in your new list and Update their DateRemoved field. 6.After doing all else, Append all the members from your ExcelTable to your Members Table (duplicate MemberNumbers won't be imported because you presumably have set your Primary Key field to No Duplicates) 7.When everything looks right, run a delete query to empty your ExcelTable ready for the next import. Evi "Pennington" wrote in message ... OK lets see if I have got this right: 1. Back up my database 2. Create a copy of the Members table without the data. Name it Members Update table (This allows me to keep the field names which might be different/additional to the import data) 3. Import Excel data into a new table named Excel table (This is because the field names might be different) 4. Create and run an Append Query to add the data in the Excel table to the Members Update table (This is so that the field names can be matched) 5. Create and run an Append Query to add the new members to the Members table 6. Create and run an Unmatched Query to identify Members Removed and change it to an Update Query with the #Date Removed# in the "Update To" line, then run the Query. (I will have now identified ex-members and new members but not updated current members details if they have changed - this is the next step) 7. Create Update Query linking Members table and members Update table by MemberNumber with [Members Update Table]![fieldname] (except member number) in the Update To row and RUN Query (Member details are now uopdated 8. Create and run a Delete Query on the Members Update table and the Excel table to make them ready for receiving the next update. I could probably skip step 4 if I make sure the fields of the Excel file are the same as the Members table before I import it and import directly into the members Update Table. Anyway I will try this out and thanks for your help "Evi" wrote: Yes of course. Back up your db before you start these procedures in case they don't do what you expect them to. I suggest that when you import stuff from Excel, first use an Append query to put it into a table that has all the field names and data formats you require (ie a copy/paste of the Main table but without its data. That way, you can save all your append/update queries and use them next time because none of the field or Table names will have changed. Your final query in the series will be a Delete Query which will delete everything from this ImportTable ready for next time. .. Eventually, when you know that everything is working as you expect it to, you can create a macro which runs everything, one after the other but you can imagine the chaos it can create if it updates or appends the wrong thing and you don't even see it as it happens.. If you want to replace everything for that member (and of course, I should have thought of that!) different or not, then you can still use the Update query. Join the 2 tables by the MemberNumber in Query Design. Put all the fields from your *Current Table* (except MemberNumber) into your query grid. Change to an Update Query. In the UpdateTo row under each column, type eg [YourImportTablesName]![FirstName] or [YourImportTablesName]![Surname] etc Substituting with the correct Table and field names. Remember, these Update To entries must not have quote marks around them. Press the Red Exclamation mark and run the query. Evi "Pennington" wrote in message ... Thanks, I will try Evi's solution but when I read the blurb in the Access Help I got the impression that I had to type an expression or value in the |
#33
|
|||
|
|||
Membership database updates
On Sun, 23 Mar 2008 03:03:02 -0700, Pennington
wrote: In the Criteria cell of DateJoined in QryNewMembers I had put a date of the form Between #dd/mm/yyyy# And #dd/mm/yyyy# and got a list of members who joined between those dates. I now want it to work so that when I open the corresponding Report I am prompted to put the month or year and I get a list of members who joined in that month or year. I have tried the Expression builder but can't seem to get it to work. Part of the problem is that literal dates in Access MUST be entered in either American mm/dd/yyyy format, or an unambiguous format such as yyyy-mm-dd. Try creating a little form with two combo boxes, cboYear and cboMonth. The former would have values for year numbers of interest for your case - 1950 through 2050 to be generous; the latter would have values "All" and 1 through 12. Your criterion would be DateJoined = DateSerial(Forms!yourform!cboYear, IIF([Forms]![yourform]![cboMonth] = "All",1,[Forms]![yourform]![cboMonth]), 1) AND DateJoined DateSerial(Forms!yourform!cboYear, IIF([Forms]![yourform]![cboMonth] = "All",13,[Forms]![yourform]![cboMonth])+1, 1) -- John W. Vinson [MVP] |
#34
|
|||
|
|||
Membership database updates
I have only just got around to testing that the unmatched query works on
sequential updates and find that it overwrites any previous removed date. To recap: I have a members table and a members update table and step 5 is to identify members not in the updated list. The unmatched query adds a Date Removed if any members are not found in the Update list. I need to compare the members and update members tables and set two conditions instead of one : a) MemberNumber Is Null (In Update table) b) DateRemoved Is Not Null (In Members table where member number Is Null in Update Table) How might I do this? "Pennington" wrote: In the Criteria cell of DateJoined in QryNewMembers I had put a date of the form Between #dd/mm/yyyy# And #dd/mm/yyyy# and got a list of members who joined between those dates. I now want it to work so that when I open the corresponding Report I am prompted to put the month or year and I get a list of members who joined in that month or year. I have tried the Expression builder but can't seem to get it to work. "Evi" wrote: Remember, you will eventually have a list of members who have been removed on various dates. You can put a text box in your report header and type =[DateRemoved] You can combine this date with text by typing ="Members Removed By " & [DateRemoved] It will show the DateRemoved of the first record though. This is fine if you are then going to filter the report so that you show only one group of removals at a time or if you sort your Dates in Descending Order in the sorting grouping box so it shows the highest date first. Or you can type =Min[DateRemoved] & " to " & Max[DateRemoved] so that you will see the lowest date on your report and the highest with the word 'to' in the middle. You can filter your report using a combo box in a form so you can select the date you want to see or print. Great fun! Or you can group your records, using the Sorting/Grouping box by this DateRemoved and put the DateRemoved field in the Group Header so that you can see the list of member who were grouped on 21/08/07, the group who were removed on 20/08/06 etc Evi "Pennington" wrote in message ... It works - brilliant. Many thanks for you help One more thing, how do I put the Date Member Removed in a Report box so it shows in the title of the report? I might have others later as I go through the Reports and Charts. Is it OK if I post them here? "Evi" wrote: You'll have no trouble doing it, if you've done all this but just test it all thoroughly on a copy of your db before letting it loose on your precious data. The thing the macro won't do is to do the copy /paste from Excel because you need to check visually if the columns are in the correct order. So, you've saved your Update & Append & Delete macros Click on the Macro page. Click New Under Action, in the first row, choose OpenQuery. Next to Query Name choose the name of your first query in Step 4 from the dropdown list In the next row in the Action column, choose OpenQuery again and next to Query Name choose the name of your next query from step 5. etc, etc To run the macro, go to the macro page and double click it. There are ways of doing it all without you having to OK each step but do try it this way for now and read any messages which appear. There could easily be something neither of us have thought of yet. That RemovedDate.... If you are going to use the date when you run the update then you can put Date() in the UpdateTo line under the RemovedDate field - the RemovedDate will be the current date when you run the query. Otherwise you are going to learn another trick - a parameter query In the NotInList Update query, in the UpdateTo line, in the RemovedDate column, type [What Removed Date?] Because you don't actually have field called What Removed Date? When that part of the macro runs, a box with the text What Removed Date? above a blank box. Type your RemovedDate into the box. Again, test on a copy to see if it works the way you expect it to. Evi "Pennington" wrote in message ... It works - brillant, thanks ever so much. I tried it out on a few records to prove to myself that it added new members, annoted removed members and changed current members details. I suppose the next step is to write a macro that executes all 7 steps? The only intervention would be to insert the Removed Date in Step 5. Can that be done? I havn't written macros in Access before. "Evi" wrote: I would suggest this as step 3 onwards. 3 You don't even need to import the Excel spreadsheet. Copy and paste your Excel data from the spreadsheet straight into your MembersUpdate table. Just make sure that the columns in the spreadsheet are in the same order as the fields on your table. (adjust the order of the fields in both your table if they aren't) and don't select more of the spreadsheet than the required rows and columns ie don't select a load of blank cells by selecting using the column headings. - the easiest way to select a long list is to start at the bottom right of the list and sweep the mouse upwards and left. To copy and paste into your new table, select the first row by clicking in the grey area to the left of the row (don't click in the field). Press Paste on the toolbar. 4.Run the Update query to update Members details from your ExcelTable (leave out the space when naming it). 5.Run the Find Unmatched and Update query to identify which members are no longer in your new list and Update their DateRemoved field. 6.After doing all else, Append all the members from your ExcelTable to your Members Table (duplicate MemberNumbers won't be imported because you presumably have set your Primary Key field to No Duplicates) 7.When everything looks right, run a delete query to empty your ExcelTable ready for the next import. Evi "Pennington" wrote in message ... OK lets see if I have got this right: 1. Back up my database 2. Create a copy of the Members table without the data. Name it Members Update table (This allows me to keep the field names which might be different/additional to the import data) 3. Import Excel data into a new table named Excel table (This is because the field names might be different) 4. Create and run an Append Query to add the data in the Excel table to the Members Update table (This is so that the field names can be matched) 5. Create and run an Append Query to add the new members to the Members table 6. Create and run an Unmatched Query to identify Members Removed and change it to an Update Query with the #Date Removed# in the "Update To" line, then run the Query. (I will have now identified ex-members and new members but not updated current members details if they have changed - this is the next step) 7. Create Update Query linking Members table and members Update table by MemberNumber with [Members Update Table]![fieldname] (except member number) in the Update To row and RUN Query (Member details are now uopdated 8. Create and run a Delete Query on the Members Update table and the Excel table to make them ready for receiving the next update. I could probably skip step 4 if I make sure the fields of the Excel file are the same as the Members table before I import it and import directly into the members Update Table. Anyway I will try this out and thanks for your help "Evi" wrote: Yes of course. Back up your db before you start these procedures in case they don't do what you expect them to. I suggest that when you import stuff from Excel, first use an Append query to put it into a table that has all the field names and data formats you require (ie a copy/paste of the Main table but without its data. That way, you can save all your append/update queries and use them next time because none of the field or Table names will have changed. Your final query in the series will be a Delete Query which will delete everything from this ImportTable ready for next time. .. Eventually, when you know that everything is working as you expect it to, you can create a macro which runs everything, one after the other but you can imagine the chaos it can create if it updates or appends the wrong thing and you don't even see it as it happens.. If you want to replace everything for that member (and of course, I should have thought of that!) different or not, then you can still use the Update query. Join the 2 tables by the MemberNumber in Query Design. Put all the fields from your *Current Table* (except MemberNumber) into your query grid. Change to an Update Query. In the UpdateTo row under each column, type eg [YourImportTablesName]![FirstName] or [YourImportTablesName]![Surname] etc Substituting with the correct Table and field names. Remember, these Update To entries must not have quote marks around them. Press the Red Exclamation mark and run the query. Evi |
Thread Tools | |
Display Modes | |
|
|