I have written a query to calculate how many hours we have worked for our clients. This has a number of sub-queries which sum to make different columns in the main query ie: Hours invoiced so far, Hours to be invoiced, Hours remaining from their allocation, etc.
I am refreshing the queries due to a design change and now have a question..
Which is the better design:
To have those sub-queries with selection criteria from a join to the same table in each (to filter out records at a lower level)
OR
To have that same selection criteria and join just once in the main query? (which of course means its processing many more records but the join to the selection criteria is specified once)
Anyone know an efficient way of setting the value of a textbox to the result of an sql query?
I am using the following, but it seems to be slow when populating a large form:
Make.RowSource = "SELECT BarcodeDATA.Make FROM (Customers RIGHT JOIN CustomerSales ON Customers.CustomerID = CustomerSales.CustomerID) LEFT JOIN BarcodeDATA ON CustomerSales.ItemNum = BarcodeDATA.ItemNum WHERE (((CustomerSales.ItemNum)=[Forms]![FormCheckConsignmentStatus]![ItemNum])); "
Exit_cmdSearch_Click: Exit Sub Option Compare Database Option Explicit
Private Sub List2_DblClick(Cancel As Integer) Dim rs As Object
DoCmd.OpenForm "frmquery"
Set rs = Forms!frmquery.Recordset.Clone rs.FindFirst "[RETURN NUMBER] = " & Str(Nz(Me![List2], 0)) If Not rs.EOF Then Forms!frmquery.Bookmark = rs.Bookmark
I'm thinking of 2 different ways, but not sure how Access will handle them.
1) A table that maintains the start and stop date of the relationship (i.e. employee has a job title from a start date to an end date).
This is the ideal, but I'm concerned about the number of records. The database will store 3,000 employees and I'd estimate around 2000 changes a month can occur to the employee data (transfers, hires, promotions, terminations and all cascading changes on dependent information).
2) A different database for each month/year. (i.e. Employees_March2013, Employees_April2013)
I don't have concerns about the number of records, but I'm not sure how the front-end will work with multiple back-end databases. Is there an easy way to setup a form to choose which "effective date" of employee information you'd like to choose and have it link to the correct back-end at that point before running a query/report?
I am wondering if there is an efficient way to compare two fields from one table to another two fields from another table. So basically
Code:
If targetTable.Field1.Value = sourceTable.Field1.Value And targetTable.Field2.Value = sourceTable.Field2.Value Then targetTable.Field3.Value = sourceTable.Field3.Value
The problem is that I need to run this for all entries in targetTable. The only I could think of was to use 2 nested for loops (one for target table and one for source table) as outlined in the following (my data is currently in Excel, but I want to import it to Access)
Code: For i = 2 To 5754 For j = 2 To 3500 If targetSheet.Range("I" & i).Value = sourceSheet.Range("AR" & j).Value And targetSheet.Range("K" & i).Value = sourceSheet.Range("AS" & j).Value Then targetSheet.Range("I" & i).Value = sourceSheet.Range("AT" & j).Value
The above code works but it is really slow (takes about 12 mins on a high-end CPU).
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
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 now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
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 sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
From this table, I can determine which employees have taken which courses. But how do I determine which employees have not taken a particular course (prompt for course code).
**Note: There is no table that contains a list of courses, since there will be over 2000. The courses are simply entered for an employee as they are taken.
Table 1 contains telephone numbers and rates per minute Table 2 also contains telephone numbers and rates per minute
I am trying to check the rates in one table against the other.
The problem I have is that the telephone numbers in each table are of differing lengths.
For example ...... Table 1 contains telephone number 01234 567890 Table 2 contains a rate for numbers starting 01234 5
I have split the numbers in Table 1 in to 0, 01, 0123, 01234, 012345, 0123456, 01234567, 012345678, 0123456789 and 01234567890 but I am stck on how I can now use this to match to data in Table 2.
In 'words' I would like the database to 'say' ...
0 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 01 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 012 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 0123 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 01234 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 012345 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for .....
etc etc etc ....
The numbers in Table 2 are varying in length.
Can anyone provide guidance on database design and / or help with writing queries ?
Hello,I am attempting to create a database for a charity organisation, the database will hold information about there volunteers, roles, languages, building, and shifts.One of the main problems I have at the moment is how would I cater for the shifts. Open Sunday - Monday 6 Shifts at present different days are open for different shiftsAny help pointers greatly appriciated.
Design Query : by two criteria I have a table by tow field : 1- name : for insert name of book 2- date : for insert date And I have a Query by two criteria 1- name 2- date (Between [Enter Start Date and [Enter End Date]) In my query I have tried the following formatting 1-Criteria: Like "*" & [forms]![bank].[Text0] & "*" 2-Criteria: Between [Forms]![bank]![Text2] And [Forms]![bank]![Text8] Open Form “Bank” and try : 1- report by name (means report only of name Field) 2- report by date (means report only date between date Field) 3- report by name and date (means all complete fields in “bank” Form) But ; I'm not sure how to do that for two criteria in queries Please enter criteria in query
Hi All, I am currently in the process of writing a manual for one of our access database systems (it's written in a modified version of access). Unfortunately this database was not written by me and has many different queries which in the end returns a number of export files... Obviously I could look through each query one by one and copy the details down into a table in word or whatever.
I am just wandering is there any way to export the design of the query itself or even print it out, as obviously I could then complete a Visio like diagram and include that in the appendix of the manual.
Searched google and the access help and what not but I can't seem to come up with anything :(
i'd like some initial help with how best to set-up my tables for a database i need to create. if anyone could give me a synopsis of what i need to do it would be great and then i can try and piece it together.
here are the requirements;
one student can attend many different courses.
each course runs for 10 weeks.
i'd like to be able to have forms to;
- add a student. - assign classes to a student. - record absence via a combo box for each student for each week
i'd also like to have a report mechanism that can record if a student is absent for 3 or more classes.
as i said, some pointers or examples would be great as i am new to this. i pressume i need;
a table for students studentid firstname lastname
a table for the courses courseid coursename
a table for attendance attendanceid courseid studentid present/absent (combobox) week
Am most stuck on the date part. the data may not be entered every week so i can't really use a date function. instead i need a combobox or something to be able to select the week.
I will try to explain this as best as possible, please let me know if there's any more info i can provide in order to get a better response. I am making a database that links the following tables:
Product Fund Company
The value for each of these combinations will be a simple yes/no, but the problem with the relationship design comes in because of the companies. For each company there are multiple smaller companies, for instance B company is comprised of a1, a2 and a3 entities. The information I am puting into the database is specific to the a1, a2 and a3 entities, not B company as a whole. What i need to query is B company as a whole. For instance, i need to know that B company can sell widgets(product) through 1, 2, 3 and 4 (funds).
I can't think of a way to consolidate the data from the smaller companies into the data for the company as a whole, especially since the smaller companies may sell the same products in the same funds. My current Table relationships looks like this: http://img338.imageshack.us/img338/5282/tableif8.jpg but i don't know how to manage the company as a whole(eg. B Company above)
The second problem i have is once i have the data for the company as a whole i need to be able to query multiple funds. I thought this was going to be easy at first by switching "or"s to "and"s but, even after playing with parenthesis any "and"s that i add just make it so it is looking for "1 AND 2 AND 3 AND 4" in one field. what im trying to make it do is query (Product = z, Fund = 1, available? = yes) AND (product = z, fund = 2, available? = yes) etc.. and have it output a list of Companies (eg. B Company)
I realize that I can query like this by querying a crosstab with funds as columns, but the problem with that is funds will change because I have 5 groups of people using the database who want different sets of funds (and it will output entities, not companies).The database needs to be dynamic so when one group of people changes their preference of funds or we add a new fund I dont have to make a brand new query/form/report.
Any comments/quidance is appreciated, please let me know if you need any more information. Thanks! :)
I am using Access 2003 with Windows XP Pro. I have a problem with using a query and the dreaded UK Postcodes!
I have a Client table that contains a field “Client Postcode 1”, in which is entered the first part of the UK postcode i.e. AB11, WC1A, E1, EC2V, etc.
In a Candidate table here are many text fields where we enter which postcode a Candidate wants to work in, i.e. TN, CV4, EC, W, etc.
We have a separate Candidate Search form where we select a particular client (Combo box from the Client table) which then displays the Client’s Postcode 1 data i.e. the first part of the UK postcode before the space.
We have a select query that then tries to match the Client Postcode 1 with the postcode that the Candidate wants to work in from the Candidate’s table.
In this query design grid I have successfully created criteria that will match the Client Postcode 1 field to the Candidate’s postcode field – very straightforward. This, for example, will match a Client with a TN39 postcode with a Candidate who wants to work in the TN39 postcode.
In the same query design grid I have also been able to successfully match the Client Postcode 1 field to a Candidate’s Postcode field using just the first 2 letters (using Left). This matches a Candidate who wants to work anywhere in the TN postcode area (TN1, TN2 etc.) with a Client whose postcode starts with TN
However, what is stumping me is where a Client’s Post code has only 1 letter followed by numbers (E1, B2 etc) in the first part of their postcode without returning EX, BL etc. I would like the accumulated wisdom on this forum to point me in the right direction to design criteria to input into the query design grid that will match ONLY the first letter of the postcode IF the second character is a number.
My problem is that I would to write a query to display the number of days taken for each of the 12 months of the year.
In the example of Employee 4 their sickness spans two different months, I can't get my head around how to produce the correct result which should look like:-
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3 1 5 7 9 2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN 1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2 2 d e
Table DELTA:
COLA_IND DAT1 DAT2 2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
I have a query where I want to see a percentage of executed demos. I currently have Name of the promo (group by), # of demos (group by), Status (criteria = E for executed demos, count) and Percentage: Status/# of demos. Everytime I run this query I get this message: "Data type mismatch in criteria expression." The only data in Status is either a O for open or an E for executed. The E in Status is the only criteria I have in the whole query. If I take the count function off Status, it runs, but does not give the right results. Does anyone know what I am doing wrong?
I have been asked to create a database in Access 2000 that will hold 1.6 million postcodes. There will be four fields within the a table one holding the postcodes, and three fields holding information as to whether or not the post code is classed as good, neutral or bad. The requirements are to allow a user to run a query that asks thenm for the required postcode and then displays the relevant information (good,bad,neutral).
There requirement is that the search is done as fast as possible returning the required results. Has anybody got any ideas as to the best way of doing this.
I have some queries that I created in 97 and convert to Access 2000.
However, when I go to query DESIGN view, I would like to see all the contents of long functions (iif) that I wrote for some fields as a result of running the query, but each field only shows a portion of it (as if it was truncated). I also use the "Zoom..." of that field or Shift+F2, still the same thing, only shows portion of it, the last portion was truncated.
Do any of you know HOW to show all, so I can check and revise if I need to, pllllllease.
We are opening up a database that was made in 2003 with 2007. The design view gives us an error of "'' is not a a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long" Does anyone have a solution for this problem?
I need to design an unmatched query on 2 tables to identify employees names that exist in 1 table but not the other (or names are misspelled, etc.). The tables exist in on a server and I do not have the ability to change either. Table 1 has the following structure:
EMPLOYEE NAME: Doe, John ID-12345 Table 2 has the following structure: LAST NAME: Doe FIRST NAME: John
Since the field names are not equal, the unmatched query wizard (as far as I know) will not work. I've tried using "NOT LIKE", LEFT JOINS, RIGHT JOINS, NUll Values, etc. in the SQL but nothing has worked as yet.Here's the SQL I've been working with but this returns all values (haven't figured out why):
SELECT DISTINCT [Table 1].[EMPLOYEE NAME] FROM [Table 1], Table 2 WHERE ((([Table 1].[EMPLOYEE NAME]) Not Like "([Table 2].[LAST NAME]*"));
