I would like a textbox in my vendor form to display the number of times that the vender’s name appears in a jobs-completed table during a particular calendar year. I’m not sure where to start. I have tried building a query as follows:
SELECT Count(tblCompletedJobs.Job#) AS CountOfJobs
FROM tblCompletedJobs
HAVING (((tblCompletedJobs.DateOfEngagement)>=#1/1/2006#));
Any suggestions appreciated!
I can't seem to find a way to solve this problem:confused:
I'm working on a database to run my window cleaning business. I have a customers table which contains all the details for each customer ie address, name etc.
There is also a Jobs table which will contain a record every time a customer's windows are cleaned.
There is a one to many relationship between them ie, 1 customer to many jobs.
I would like to create a form which would list all the customers which are due to be done and then be able to create a job record for each customer in the Jobs table, showing that it's done, with the click of a button.
For one, I can't get both tables to display on a continous form because of the relationship and then I don't know any other way of creating multiple new records in one go.
Would i have to use VB code for this or is there a simpler way? I just can't get my head around this one:mad:
Setup
i have a form (frmCustomers) with a subform (sfrmJobs). As you might have guessed, it keeps track of customers and jobs. One customer may have many jobs. within sfrmJobs are about 300 or so controls for about 300 or so fields (mostly yes/no boxes) and at the moment contains two subforms of its own. customers have an autonumber CustomerID and jobs have a user-entered numeric JobID.
Goal
it has been brought to my attention that some jobs may just not work out and must be deemed as "cancelled." however, my client wishes to retain these records rather than delete them. in order to maintain the integrity of the reports, i would like to remove these records from the non-cancelled customer database and place them elsewhere, but still retain the ability to review them if need be (but NOT to modify them).
Problem
the job can be cancelled at any point down the line, meaning that a job could have nearly no info or nearly all the info, and everything in between. each job record has a ton of fields. i don't know how to cut and paste an entire record (except for doing it field by field, which could take forever in this instance) into a new table. in this case, it would be several records, since i have a list of materials pertinent to each job as well as a list of services necessary for each job - all of which would have to be removed and placed elsewhere.
Cry for Help
what is the best way to go about this? some ideas i had were creating a parallel table for each table used in recording all of the job information and just manually (well, via VBA and recordsets) copying, pasting, then deleting. another is creating a checkbox that when clicked disables every control for the cancelled job (but oy vey that's a lot of controls to code the disabling for) and then have it reflected in my reports that these are only from the NON-cancelled jobs. does anyone else have an idea?
help?
thanks in advance
-Jason
Hi
is it posible to run overnight batch jobs to say update a table in a particular Ms Access Database.
If so how is it done?
Thanks
Norm
Hello all, I have an Access database for a project I'm currently working on.
It's a simple stock control system which records sales and the level of stock.
How can I set up a form that when you click a button, two queries are run?
I want to add a record to the 'Sales' table but also modify the 'Stock' table to edit the 'Current Stock' field.
Any ideas?
Thanks
Does anybody know how to schedule in access like sql. Like a "job" in sql server?
View 14 Replies View Relatedhello all.. trying to resolve an issue in my system..
basically i run a taxi firm.. for those that dont know.
this issue deals with credit card payments, and in particular payments for multiple transfers.
lets say you are booking 2 journeys..
the first journey is on 2nd June from London Heathrow to your hotel in Kensington
the return journey is on 5th June from your hotel back to Heathrow
you want to pay for both journeys at once with your credit card.
both transfers cost £40 each, 40x2 is £80 plus 5% transaction fee is £84.
we keep the credit card details on file to save our passengers time when they want to pay for their journey using the same card.
so lets say we have a job table.. and a credit card table..
Card Table
cardid (pk)
cardtype
nameoncard
last4digits
secnumber
expirydate
startdate
issuenumber
Job Table
job id (pk)
jobdate
jobtime
jobnotes
should i create a new transaction table which holds the jobids that were paid for and the cardid that paid for it..?
shall i include the price of the job in the jobtable.. and store the transaction fee in the transaction table?
if this is clear i would like to hear a few opinions on this.. i will be thinking hard about this over the next few days so i might come to some conclusion but what do you guys think
cheers.
the reason im asking this is because at the moment i am storing the credit card details.. pricing information and job details in the job table..
when i send my passenger an email confirmation.. it sends them the job details, card details and payment details..
this is fine for single transfers.. but if they book more then one transfer i have to send them multiple emails outlining each job individually.. and if they wish to use the same card i have to find their card from the original job and copy those details.
if someone books 4 jobs and pays for them all with one card and one transaction i really have difficulties, i would have to send them 4 emails (with 4 job details respectively) and i would have to include all the payment details in each job to verify it has been paid.. this means i would have to enter their card details 4 times.. and even though each job might be £20 each.. i would have to write £80 in the total charged field because it is covering all the jobs..
anyways.. if you can spare me a thought i would be grateful.. thank you
hi
is there away to filter rows when certain values in three different tables are the same. e.g. it is a stock control db - so when goods orded = goods in = goods taken - can this then be filtered automatically to hide rows and avoids congestion on the user interface (form)?
thanks
scott
I am working with a training database. I am a fresh newbie to access (more of an excel user).
I need to find employee's who have never completed a certain training (DPW Medication Training), which is mandatory.
I have the following tables:
Employee data TBL - Contains active ee's names
CompletedTrainings - Contains all trainings completed by individual
Events - name of all the trainings, including mandatory trainings
I have the following queries:
DPW Med Training - Lists all who have completed the training
DPW-Med-Last - Lists when each employee last had the training
Med_01 - Lists those who have had the training, but need it renewed
Any thoughts?
I have a form that a user fills out and the record is not uploaded to the database until the user hits the submit button. In other words, if the user closes the form without saving, the record is not inserted.
My end goal is to have the user click the button and have 3 things happen:
1) Record inserted into the database
2) A PDF of the record is saved to a per-identified directory
3) An outlook new message window opens with recipients pre-entered and the newly created PDF included as an attachment.
I can achieve steps 1 and 2 no problem. However, the way I get the PDF is by having the VBA simply print and the user selects the Adobe PDF. This does not let the VBA dictate the directory (or file name) and then makes me completely stumped for step 3.
I have some code that checks if a file exists, its file size, creation date and date modified. The code works fine..however..
I need to import the file it is checking and that file can sometimes take up to 30 minutes to complete its posting. In fact I used the code to check it the other day and the code showed that it exists, its creation/modifed date and also its file size - however, the file size was what it was at that moment I ran the code. If I immediately re-run the code, the file size changes - obviously due to the fact that the file is still posting (and growing due to the date being posted to it). Also note that the (completed) file size will vary each day the file is posted
So, what I would like to know is, is their a way to check to see if a file has completed posting? Something akin to .filecreated like .filecompleted (yes or no) type thing?
Here is the code:
Public Sub sub_chk_files2()
Dim obj_fs As Object
Dim obj_folder As Object
Dim obj_files As Object
Dim obj_f1 As Object
Dim db As DAO.Database
[Code] ...
I have created an append query to update completed courses to a previous education table.
INSERT INTO Education ( ID, [Qualification Name], [Level], [Date Achieved] )
SELECT Courses.ID, Courses.CourseName, Courses.CourseLevel, Courses.CertRecvDate
FROM Courses
WHERE (((Courses.CertRecvDate) Is Not Null) AND ((Courses.CourseCompleted)=True));
The theory here is if CourseCompleted checkbox is check and there is a value in CertRecvDate, the qualification has been achieved and therefore should be added to the previous education table (which will be used later for CV's etc)
The problem I face is I only want it to add any particular course once to the previous education table and not every time the query is executed. I am thinking I need to pass the CourseID field aswell to the education table and somehow check to see if that has been already added .
I have attached a screenshot showing the structure of the two tables.
I have created an access database to track orders through out production. I have also created a form labeled "orders" with a subform where it has all the orders information, customer info and product info. Along with this there are 3 check boxes for each order they are labeled, "Routed", "Pressed" "Finished". I am looking for a macro for when I click the "finished" check box that order is moved to another form to reduce the amount of clutter on the orders form. Is there anyway to do this?
View 1 Replies View RelatedWe have a form, which has a field linked to a "Recipients" table through a lookup. The field is bound to the recipient's name, but the lookup displays additional columns for user to identify the correct recipient in the case of duplicate names.
The "Payment" table's Recipient field only displays the recipient's name. So where duplicates occur, it's impossible to know which recipient is the correct one associated to a payment.
I would like to add an additional field to the "Payment" table called "RecipientIDNo". I need a way to auto-populate this field, based on the already existing Recipient field. Is this possible?
Why I just don't modify the recipient field on our payment form to be bound to the user ID number, there are two reasons.
(1) The field is linked to too many other reports and queries at this point. I believe it could cause an enormous amount of work to modify.
(2) the form likes to display whatever it is bound to. There may be a workaround for this, but I'm not aware of it and don't want the form displaying a number instead of a recipient name.
How do you make certain fields "required" to be completed before exiting?
View 3 Replies View RelatedI'm enrolling medical centers in a new multi-center database. There are a number of steps that must be completed before the center is enrolled in the project. I have a form that serves as a checklist. I want the textbox at the bottom of the form to display the percentage of steps completed. I guess you would have to count the number of fields on the form that are filled in, but I don't know how to do it.
View 1 Replies View RelatedI have a primary school database. I'm trying to create a form that allows a teacher to select their class, then select a subject and then be presented with a data entry form in a table layout that lists only their student's names in one column and an empty column to input results for the selected subject.
To simplify my explanation to just three tables, lets say my tables are:
> Students....which stores student names plus a foreign key for their class
> Classes...which stores the class name
> Results...which stores all the results (fields are: ResultID (key), StudentID, SubjectID, Result, DateofResult)
I have no problems creating reports where the teacher selects their class from a combo box to generate a report based on a crosstab query. But this one has me stumped.
I have a form with lots of combo boxes and text boxes. They are all linked to various MS Access tables and some of the fields are mandatory. I would like do an check to see if all the mandatory fields are not null i.e. are populated with a value and then run an update query for my master table in the database to flag the records as being complete if all the mandatory fields are populated.
If (IsEmpty(tab1.cmb_a)) Then do something
But I am just blank at the moment on how to proceed, especially on how I can do all the checks and then populate the database.
I have 2 tables, one is like a main table, containing all of the main data, such as a Job Number, Customer, Quantity, etc. I have a second, related, table that acts a breakdown of information. There may be several related records to one main record, it entirely depends on the nature of the job.
What I'd like to do is run a function that looks at a main record, checks if all the related records COMPLETED field is ticked and then tick a field in the main record. I only want it to do this for records where all of the related records are COMPLETED.
I have a table called Neutron2015. I want the current date and time to complete when entries are made in two other fields. So I am looking for this date and time to populate in EXLStart when an entry is made in Processed By, and also the current date and time to appear in EXLEnd when an entry is made in Completed By with the bold word being the field names.
Is this possible, and how difficult is it?
I'm completely new to Microsoft Access. This project was thrown my way. I have an accounting database to track payables, receivables, financials, and deliquencies/collections. Is there a way to generate a report for any of the items in the previous sentence that haven't been completed to keep track of workload.
View 5 Replies View RelatedI have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
View 1 Replies View Relatedi am trying to count the number of records based in a query
can some one send me in the right direction
I have a database where there are numerous fields but they all only have one three values Y, N, N/A.
how do i get something similar to Excels "countif" function to summarise the totals of Y's N's N/A's inach field ?
Thanks
I have a database where there are numerous fields but they all only have one three values Y, N, N/A.
how do i get something similar to Excels "countif" function to summarise the totals of Y's N's N/A's in each field ?
Thanks
How can I count the number of times an employee shows up on a report. The final result would be:
Employee 1: Reader1
Employee 1: Reader2
Employee 1: Reader3
Employee 1: Reader4
If an employee shows on a report 4 times, I need it to look like the example above in sequential order, not just a total.
Thanks for your help.