Posts by W_Scott_Taylor
-
-
Re: What is a LinkMasterfield? Linkchildfield?
The default is usually dynaset unless the writer changes it manually.
-
Re: Open, sort, save, close <-- using VBA
When you record the macro start by clicking onto the worksheet tab you need to sort. This will give you extra code for the sheets you will sort. The revised section for sorting will look something like this.
CodeSheets("Sheet2").Select Cells.Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("Sheet3").Select Cells.Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
-
Re: What is a LinkMasterfield? Linkchildfield?
I see, sometimes it is difficult to structure queries for subforms that will allow updating in that fashion. It depends on the joins. Also the query property for recordset type will need to be Dynaset (Inconsistent Updates) in most cases before that type of update will work.
-
Re: Excel to Access - Not importing alphanumeric value
I would suggest you record a Macro to get your sorting routine, but your code will look something like this. You will have to have a reference to the Excel Object Library. Just call this subroutine from your other code before the import piece, or copy the code into that routine, either way will work.
Code
Display MoreSub SortAndSave() Dim strPath As String strPath = "C:\Documents and Settings\taylor1\Desktop\Book1.xls" Workbooks.Open Filename:=strPath 'Sorting Routine from recorded Macro Sheet1.Select Cells.Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Workbooks("Book1.xls").Close SaveChanges:=True End Sub
Hope this helps,
-
Re: Open, sort, save, close <-- using VBA
Hee Hee, I was getting around to it from the other post.
I would suggest you record a Macro to get your sorting routine, but your code will look something like this.
Code
Display MoreSub SortAndSave() Dim strPath As String strPath = "C:\Documents and Settings\taylor1\Desktop\Book1.xls" Workbooks.Open Filename:=strPath 'Sorting Routine from recorded Macro Range("A1:A10").Select Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Workbooks("Book1.xls").Close SaveChanges:=True End Sub
P.S. YOU WILL HAVE TO HAVE A REFERENCE TO THE EXCEL OBJECT LIBRARY.
Hope this helps,
-
Re: Excel to Access - Not importing alphanumeric value
Quote from DerkAnother way is to sort the excel data first so that the alpha values are at the top. I think Access looks at the first few (20?) values to decide what the type will be.
I like Derk's method, and though it sounds odd, it will work. You could even have some VBA from the Access side open the spreadsheet and sort it/save it before you begin the import routine.
-
Re: What is a LinkMasterfield? Linkchildfield?
Quote from bearcubi guess the other question is can the child force the Parent (master) to update? If so, does it happen all the time or does it depend upon the type of query (subform) being used?
I am not sure why you would want the child to force the parent to update, but I think you would have to change their relationship. I think it probably only works one way.
If you are talking about editing records through a subform that is a different story...
-
Re: Access Wildcards
Concatenate the wildcard.
Like [Enter Location] & "*"
or to pick something out from anywhere in the string...
Like "*" & [Enter Location] & "*"
Hope this helps,
-
Re: What is a LinkMasterfield? Linkchildfield?
Quote from bearcub
In this type of master-child relationship, updates to the source (report) will automatically update the linkedchild (graph) but updates to the linkedChildfield will not update the LinkedMasterfield?If I understand your question correctly the parent absolutely forces the child to update. We could really see this more clearly perhaps if we were talking about a form where the user moves from item to item. As the user changes items and the parent form updates the basic item information, and the child graph of sales history follows.
-
Re: What is a LinkMasterfield? Linkchildfield?
Quote from bearcubThanks W Scott.
Looking at Access Help I read something about option buttons (or activex controls). How does these fit into your model about LinkedMasterfield and LinkedChildfields?
In this type of parent-child relationship, updates to the source will automatically update the linkedchild but updates to the linkedChildfield will not update the LinkedMasterfield?
Thanks
I have not used activeX controls in this context. My experience is limited to subforms and graphs. I am not sure I follow your question concerning updates. Sorry.
-
Re: Extremely odd import error...
Doesn't make sense to me. If the path, the workbook name, and the worksheet name are correct it seems like it should work. Maybe you have some corruption. Try compacting and repairing your DB and doing a file save as on the Excel Workbook making sure you save it in the appropriate version of Excel that Access is looking for in your Transfer Method. Other than that, is transferring your only option? What about just linking to the sheets?
-
Re: What is a LinkMasterfield? Linkchildfield?
It could relate to a subform, graph, etc. Any child object that is bound to it's parents 'master' recordset should have the property. For example, say you have a report that displays one item number's sales history per page and a sales history graph is on the report just below the data section. The parent recordset is that of the reports source table or query. Let's say the report uses a query called 'REPORT_QUERY_1'. The graph may or may not have the same record source. Let's say the graph's record source is 'GRAPH_QUERY_1'. To bind these two objects parent and child together so that the report data and the graph data for item X are diplayed appropriately we use the two properties you discussed in the graph object. The property on the graph object for LinkedMasterfield is the [REPORT_QUERY_1].[ITEM_NUMBER]. The property on the graph object for LinkedChildfield is the [GRAPH_QUERY_1].[ITEM_NO]. I made the two field names different on purpose, but they are the key, the join, the link between these two recordsets and objects contained on the report.
hope that helps,
-
Re: Make Table Query
Query 4 does what your formulas would do for the subtotals like construction etc. Actually if you want a grand total by month it would require one more query similar to number 4 where you remove the grouping on your subtotal descriptor and have another alias there as a place holder for the UNION query.
If this is purely for visual effect, and you are comfortable with Excel Objects and VBA why not do this on a form. You could use an actual VBA form in Access, not an Access form. You could set up the references to Excel you need. Then you could make a nice looking form where the meat of it would be an embedded Excel grid object. You could construct a query that consolidates all the information you will need to build the grid. Then spin through that query as an ADO recordset and populate the Excel grid on the form using the Range Objects.
Just a thought,
-
Re: Make Table Query
First let me say that what you are attempting does not seem simple, so don't feel bad for being stuck. You may be better served to write some code using ADO to build this recordset if you know VBA. Since you specifically requested a query we will explore that direction. Without a lot of study, considering your brief descriptions, and finally not knowing the exact dimensionality of your tables concerning dates, I am going to make some assumptions to get us started.
First your dates for your buckets must be contained somewhere in order to separate them into this timeline, and since you stated your proposal table only contains a start and end date I will assume the other pertinent dates are contained in the personnel and equipment tables where you are going to book those resources. If that is the case, then this seems to be a chain of seven individual queries to yield the final result you seek.
Query 1 - Step 1:
This query would link your proposal table to the personnel table and show a date column from the personnel table, the subtotal descriptor for each personnel resource, the personnel resource, and your cost.
Query 2 - Step 2:
This query would link your proposal table to the equipment table and show a date column from the equipment table, the subtotal descriptor for each equipment resource, the equipment resource, and your cost.
Query 3 - Step 3:
This query would UNION steps 1 and 2.
Query 4 - Step 4:
This query would aggregate step 3 by grouping the date buckets, grouping an alias column for a place holder with "Total" as a value, grouping the SubTotal Descriptors, and Sum the costs.
Query 5 - Step 5:
This query would union the detail query step 3 with the aggregate subtotals of step 4.
Query 6 - Step 6:
This query would use query step 5 and be a crosstab query where you would select the date field as a row, the resource field as a column, and the cost as a value.
Query 7 - Step 7:
This would be the make table query that makes a table by dragging the asterisk from query 6 into the design grid.
You will probably need to devise a sorting key for your columns since I doubt you will appreciate an alpha-numeric sort on your column headings. This could be a separate table linked to query step 5 and used in the construction of the crosstab query step 6.
It will be interesting to see if anyone has an easier way to approach this!
Hope this helps,
-
Re: convert rows to columns in EXCEL. Thanks a lot.
You posted the same thing in the Excel VBA forum.
-
Re: hlp in Access plz
Thanks, I am glad it worked out for you.
-
Re: Hidden Design Grid?
When I have seen situations like this it has been because the previous query user/designer had a pixel setting on their computer that was much larger than the current viewers. It is a bug in Access that the scroll bars do not exist to allow you to scroll to the design grid. Try opening up a new query on your machine and pasting the SQL from the old query into your new query and then go into design view. Or if your machines pixel setting go high enough you could try changing them first.
Hope this helps,
-
Re: hlp in Access plz
A Where clause will never be shown in a query, to see that data you would need to change it to a Group By. But I go back to the date range of the criteria, it is only a four day date range, that would make it impossible to get back the five days like your "correct" example shows. Also I find it interesting that you are looking at 2003 and not 2004.
-
Re: hlp in Access plz
Quote from sasso
Dept No - Dept Desc - last name - hours - days worked - avg hrs worked
3 - Electrical - Chin - 57.25 - 5 - 11.45
3 - Electrical - Ranger - 45.50 - 5 - 9.10
2 - paint shop - graves - 51.90 - 5 - 10.38
2 - paint shop - swift - 49.00 - 5 - 9.80
1 - plumbing - bracewell - 36.66 - 4 - 9.17If the above example is what you are supposed to get this is not possible with your date restriction. Your date range in the Between clause is four days. Is that the problem?