I have got a db and that has 10+ ref tables, and i need to show everything from those tables in one, and these tables includes Attachment field. I have tried union but did not work.
Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.
Here's my problem: I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.
The main search query has the following criteria for each field - Like [forms]![Form1]![cboOffice] & "*" Like [forms]![Form1]![cboJobTitle] & "*" Like [forms]![Form1]![txtName] & "*"
It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank. If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
I have two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.
I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:
SELECT TableA.* FROM TableA Union SELECT TableB.* FROM TableB;
(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )
I working on simple MS Access program which have 2 tables. As you can see in snap shot. What I am trying to achieve is. I would like to run a query which shows data between certain dates(It is done) and also to show me which Technician has done how many jobs(i have 5 technicians) and how much money a technician received by a payment method like Technician ID 1 did Cash Transactions 4 worth $300 similarly Credit Card transactions 2 worth 120$ and so on for other technicians.
The main theme is to get weekly summary report of technicians how many jobs they done and how many transactions each technician made by cash, by credit card along with their amount.
PostCode District District Name Count of Postcodes = Count(Left([hull_PostCode.PostCode],4))
Now some Postcodes display 4 lines as they cover 4 Districts, my questions is I only want to show the highest Count per postcode and disregard the rest, Ive tried many variations of the Max function but am a little stuck.
I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field?
This is kind of a weird problem, and everything I've learned about access has been on my own through empirical experimentation so forgive my if my description doesn't make sense, or if I overlooked something really simple. (Apologies for that, I've looked over all the guides on queries and record counting I could find and got nothing on this.
I can't figure out a better way of explaining my situation so I'll do the best I can.
I need to make a query that shows the number of people that have registered for a class. In one table I have the primary key Schedule ID which is the unique class, which is linked in one-one relationship with another field of the same name. In the second table with that field I have the field schedule ID which shows up multiple times, for each student enrolled in the course, second is the field Participant. There is the following data: Schedule ID...Participant 1 ................ <name1> 1 ................ <name2> 1 ................ <name3> 4 ................ <name4> 4 ................ <name5>
What the result of the query should look like is this:
Schedule ID...Number of participants 1 ................ 3 4 ................ 2
But instead I get Schedule ID...Number of participants 1 ................ 5 1 ................ 5 1 ................ 5 4 ................ 5 4 ................ 5
I have tried various variations of the Count and Dcount functions but I always wind up with that result (if it works at all)
This is the expression I am using:
Expr1: DCount("[Schedule ID]","Class Roster")
where "Class Roster" is the name of the Table that Schedule ID is located. I have tried changing it out with the name of the first table, and I have tried using participant instead of Schedule ID, but neither works. So far I've been able to use a crosstab query to get something roughly what I want but I think the assignment calls for a regular query.
Again, sorry if this doesn't make any sense, I did my best to explain it, and I couldn't find an answer anywhere. Thanks in advance for any help!
Oh, by the way, it's access '03 that I am using.
UPDATE: I have a second problem I'm trying to solve, and this one seems more confusing than the first to me. I have a table with the following fields:
Project Name, Task name, employee ID, Production Week, and Hours Worked. The first two being text and the last 3 being numerical.
I need to create a query that totals up the hours worked for each employee (one employee ID can show up under multiple projects/tasks) and returns the total hours in a particular week (week 20) of each employee that has worked over 40 hours, and only the ones that have worked over 40 hours. I can not figure out how to create a field that will sum up the hours worked for a given employee, nor can I figure out how to show only rows where the total hours record exceeds a certain amount. It seems like I would want a conditional like an IIF statement but those seem to be only for returning values, not showing/hiding.
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example: For today, we would use 4085-0001, 4085-0002, 4085-0003, etc. For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
I am working with 4 tables and I am trying to get a count of the email address field in each table grouped by Email Group Name. I tried the dcount function but returned an error because some of the email addresses have an apostrophe in the field. Is there a way to do this in 1 query? This is an example of the result I would like:
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
How to count the number of times that the data appears in a certain field (which is [Ema!l]). I have a list of ema!l addresses and I want to find out whether that ema!l address has appeared once, twice or more. I want to add this as a field in a query. I don't want to delete it or anything because it's perfectly fine for the ema!l address to appear more than once, I just want to be able to identify when the ema!l address has already appeared.
If I was using Excel I would use a Countif function to count how many times the data in the specified cell appears in the whole column, and that would give me the number. I'm not use to the language of Access so I can't figure out how to achieve this.
I tried adding a Total row to the grid in the query and then changing the total to 'count' but this just returned 1 for every row.
The field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings FROM tblBookings GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0 The Hilton. Bookings 3 The Carlton. Bookings 0 The Lowry. Bookings 2
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A FROM [One Year Data Lines] GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
I have an button on my form that exports the database to the A Drive. The[problem is that if the user hasn't put a disk in then an error is produced. I would like to create a message box saying 'Ensure that you have placed a disk in the A:/ Drive' then with a continue ox under it. I assume it needs some error handling with it as well, but I dont know how to do it. Anyone know?
I adapted the findform from the address book sample database. After the search is performed I would like it to be able to continue to browse (continue scrolling through) the rest of contacts rather than excluding everything but matches. More like a go to kind of search rather than a select query. I'm betting this is a simple fix (I hope), I just don't know what it would be. Any help?
I have made a contact form where particular member credentials are coming in text boxes, i have kept a combo box also on the top to view by the phone number, if the phone number is entered the credential of the member comes, and also the navigation buttons that is first, previous, next and last.
The main problem if i select phone number by combo box on the top, the navigation button do not continue from that part, even if i press next or previous it shows no records found.
