I have a search form to run a query to produce matching records from a few tables. However there are many fields so I was wondering if I could make something like a split form to view the results from the query in one window.
I know you can make queries produce tables and you can have split forms linked to table information. I wasn't sure if you could/ how you would run a query and produce a table with a split form.
Lets say I have the follow Tables:
Code:
Outfits:
ID | Top Color ID | Bottom Color ID
1 2 1
2 3 4
Colors:
ID | Name
1 Red
2 Blue
3 Green
4 Orange
I'd like to have a query in design view to have the following result
Code:
Outfit ID | Top Color Name | Bottom Color Name
1 Blue Red
2 Green Orange
In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name".
I need some VBA coding to do the following tasks
Table 1: two columns - Child Tag and Parent Tag. Parent Tags can also be in Child Tag column. In other words, a parent can have multiple levels of children.
Table 2: one column - Backup Tag.
I'd like to have a form with a combo box, pick a Parent Tag, the search all its child tags and compare each Child Tag found with records in Table 2 to see if there is a match. Then populate all results in a tree view control.
A visual example :
Parent Tag
...Child Tag 1 - Back up tag found
......Child Tag 11
......Child Tag 12 - Back up tag found
...Child Tag 2
......Child Tag 21
...Child Tag 3
......Child Tag 31
.........Child Tag 311 - Back up tag found
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column
(2) [shift+arrows] to select all of the columns I need
(3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
I have a Form which I have linked correctly to a subform. The Text boxes are showing in the Design view but are not when one switches to the Form View. Labels for Fields are visible in the Form View. Have even created a new subform and that will also not display the Text Boxes.
View 1 Replies View RelatedWhy the ability to view the properties of an object within a form is not available when you double click on it in design view?
I was happily working away double clicking on a command button to edit some code when for some reason the next time I tried to edit it did not open up for me.
I was unable to access it even by right clicking on the object & selecting properties as that also appears to be disabled, not greyed out or anything but just does nothing when selected.
Have I inadvertently changed a setting somewhere that prevents the properties from being displayed?
See title :)
I have seen that in some sample db's rightclicking the mouse showed the menu with an option to check or uncheck the dataview option, unchecked the formview was applied, but when i 'just' import the subform, i cannot find somewhere the possibility to change from dataview to formview.
Maybe someone can help me with this, probably very simple(?), question?
Anybody ever heard of this before? I open a form, and it is empty, just a blank window. I open the same form in Design View, all appears well. :mad:
View 2 Replies View RelatedI am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
I have a table called NewTransactions_tbl that I have been using for some time. I am trying to add a new field called CustomerProjectNumber. It's not being displayed when I look at the table view. I've attached an image showing the settings for the field. I am probably missing something obvious.
Any ideas why it is not showing?
I am using Access 2000.
I have 4 tables and around 440 records but can only view up to 417 in the form I have designed. I have been adding new records via form and it has been added to my main table, but when i try to view it in form view - the record is not available to view. What do I need to do to correct this problem?
I have checked that there are no filters, data entry is set to No, Auto deletions, additions and edits are set to yes.
Also to mention it seems that the problem has arisen since I set up some new queries, there is a one to one relationship between the tables!
I want to "zoom" to the underliyng data from pivot view. When in excel someone doubleclicks a field in a pivot table, it automatically creates a table containing all the lines that field were made up from. I want to achieve the same behaviour in Access.I started to think towards a VBA coda, that could be initiated from the form's double click event. It should go to datasheet view with the prpoer filter criterias.
View 1 Replies View RelatedHere's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
I have a form which is opened with a filter from another form. For some reason it views the form in form view, although it was set up as datasheet view. I even went to the form properties and the default was datasheet and i made it not to allow other views. This still didnt solve it. I want it to be in datasheet view because i want to show multiple records at the same time.
If u kno how i could resolve this please offer ur help
thanks
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
IIf(DatePart("w",Now())=2,Date()-3,Date()-1)
Using >Date()-3 doesn't work.
Thanks
Bruce
I am wondering how you can output your query into a form. I have a form to select the information the end users is looking up, which refers directly to the query but how can I tell the query to place the results into a form view?
View 10 Replies View RelatedI have inherited some Foxpro tables which I am trying to convert to Access and I hae set the exported tables up and have tried to set the relationships between the tables so that I can run queries and setup forms to review and add new records. However I am struggling to get the relationships in place that will allow me to do this. When I try to drag the common fields from one table to another to setup the link I get a screen which shows Relationship Type as 'indterminate' rather than 'one-to-many'. Whatever I do I can't change the type of link and when I view a query based on the two linked tables the * in the record navigator is greyed out so I can't add new records.
I have a sample of the tables,links,queries and forms I have setup to date (with a limited number of records), if someone could have a look at it and advise where I am going wrong that would be great.
The data and the structure of the tables is pretty much fixed as this is what is coming through from Foxpro and there is a lot of historical data to bring across.
Many thanks
I am trying to view a query in design view but everytime I right click on it (or any method) it brings up the:
syntax error in from clause.
I then have to say OK and that's it. Cannot view it.
Interestingly enough, the query works fine when called from my VBA code.
Know anyway round this?
I am having a problem with editting the SQL source of a query. Every time I position the cursor it highlights from there to the end of the query so as soon as I type it deletes to the end of the query. I have tried pressing the Insert Key but that does not seem to have any affect on this problem.
View 1 Replies View RelatedIs there a keyboard shortcut to open a new query in SQL view?
I find myself encouraging carpal tunnel by:
> going to "queries"
> "new query"
> "design view"
> closing the "show table" dialog box
> right clicking the new query
> finally selecting "SQL view".
There has GOT to be an easier way that this 6-step process to ~run a query~ in a _database_ ... but I haven't run across it yet though. Any help here? My wrist thanks you ; )
Gents,
I'm building an update query for my supplier prices and I've run into a problem.
When I select fields such as part number, model, description and supplier name (all of which I won't be updating) in the design view, when I come to view the data before running the query all that is listed are the two fields I'm updating namely the list price and the review date. How can I get the view screen to list the additional data that I wish to view and check prior to running the query.
Cheers for any help
D
I would like to run my query, but have it open into form view- any suggestions?
*I don't want filter by form, or query-by-form, I want the info that would come up for the query, "MsterQry1", to be shown in the form. Thx~