Assigning Parts To A Customers' "Workorder"
Jun 15, 2006
It's me again... :(
After getting (with a lot of help from ansentry) my database doing exactly what I wanted. I realised I wanted more... by my tiny mind can't see how to approach it - despite on the face of it looking like the simplest thing to do.
So here goes:
I have a table of customers. I also have a table of jobs, or workorders assigned to each customer. Each customer can have multiple jobs added at any time. All this works perfectly.
Now I want to assign parts (i.e. cables, tools, software) to each job. So each Job (sorted by WorkorderID) can have multiple parts added to it.
I'm using control tabs as well and would prefer it if I could use some kind of button on the Customers' Job summary view to "Add a Part".
Here's a link to my previous questions, complete with pictures....
http://www.access-programmers.co.uk/forums/showthread.php?t=108706
Ideally, it would be great if the Job Summary view I have made could also have a column saying how many parts have been used in the job. (This is very much a nice-to-have!)
I tried a search on the forums for clues, but I reckon I'm either not searching the right way or this is so easy no one else has asked it. Apologies in either case. Any help would be great... :D
View Replies
ADVERTISEMENT
May 30, 2012
I have a report with many unbound fields with expressions (over 200). I've began getting this error message. This may seem like a dumb question but what does it mean when it speaks of simplifying the expression by assigning parts to variables. I put a few examples of some of the expressions below.
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
=Count(IIf([District]=1,0))
=Count(IIf([District]=1,IIf([FSL]=4,0)))
=[D1Final4]+[D1Waiver4]+[D1PreMist4]
=[D1TotalCom4]/[D1TotalReq4]
View 14 Replies
View Related
Jan 24, 2007
I love all the email post information that is available, but I haven't been able to find exactly what I am looking for...
Suppose a customer enters a computer workorder request. Then upon completing the form, your boss wants it to automatically send your office an email "your.office@here.com" and the requester's email (which it would get from email field the customer just entered their request into). The boss wants it to also include in the subject line "Workorder #" and then the workorder autonumber that was assigned to that workorder request. The boss also wants certain information inside the body of the email... ProblemType, Description, Remarks & LastName.
This should be sent automatically without the "customer" knowing or having to interact with outlook in anyway (if possible).
Thanks!
View 7 Replies
View Related
Nov 15, 2006
I have a Maintenance work order form I would like to filter out completed records so when the form is open it will only show workorders that need to be copleted and also a way to view all the workorders that are complete
Thanks
View 4 Replies
View Related
Jan 23, 2014
I have an old access database (written with 2003 but running under 2010) that creates workorders and I need to change a couple of the reports to print three copies with each copy having different text in the footer. I'm converting the reports from a old DOT Metrix special form printer to a laser printer. I have already modified the reports as far as the titleing and cosmetics are concerned and they print and look great on the laser printer. I have also added a TxT box that I want to contain the information on the report.
The reports are generated in a couple of different ways off different screens in the system off buttons, but I figured if I can get one of them to work I can replicate it to the others.
I have gone through the reports forum and have found a couple of solutions but can't get them to work. This is what I have found:
On the on button to print a range of workorders (it drives a query that asks for a starting workorder number and a ending workorder number) click from the from the switchboard:
DoCmd.OpenReport "rptBulkWorkOrdersbyWorkOrderNumberRange", , , , , "1"
DoCmd.OpenReport "rptBulkWorkOrdersbyWorkOrderNumberRange", , , , , "2"
DoCmd.OpenReport "rptBulkWorkOrdersbyWorkOrderNumberRange", , , , , "3"
Then I have this code but I'm not sure where it goes. On the report in the "on open" expression? not sure..
Select Case Me.OpenArgs
Case "1"
txtBox62 = "Shop Copy"
txtFld1 = [qryField1]
[Code] .....
View 14 Replies
View Related
Mar 6, 2013
I need to make a DBA that will have 2 tables. One Has general Infomation name, address, phone, ect. Another that will log each call made to that customer. The purpose is to log everytime I speak to a customer and be able to determine using a qury who I have not called in a while.
View 3 Replies
View Related
Feb 23, 2012
I have a table for my customers with current information.
CustomerID:0001
CustomerName: xxxxx
Address:xxxxxx
Telephone :xxxxxx
etc.....
But some time customers change their information. I want to keep their old information in other table. How it is possible?
View 4 Replies
View Related
Feb 18, 2012
I have a table in my database for my customers. Sometime my customers change their address but I want to keep these changes in other table. How it is possible.
View 1 Replies
View Related
Jan 2, 2007
My customer searches hires a car between two dates - StartDate and EndDate. I want to build an expression that searches my database for all the customers currently using cars (using cars on the day of the search.) How can this be done?
View 2 Replies
View Related
Dec 18, 2007
Hello all, I'm new to this forum.
I'm having problems creating a query for a database (which I am building as part of my university assignment).
The database is a simple ordering system style database, and contains the tables customer, order, order/product and product. I am trying to build a query that can identify customers who have not made an order during the previous week.
I have tried a criterion " <(now()) - 7 " but all this does is show old orders. I am completely stuck. Any help would be appriciated.
In case it is needed here is a list of fields in each of my tables:
Customer:
Customer ID (PK)
Company Name
Company Street Address
Company Town
Company Region
Company Postcode
Order:
Order ID (PK)
Order Taken By
Date (DD/MM/YYYY) ( =now() )
Customer ID (FK)
Delivery Street Address
Delivery Town
Delivery Region
Delivery Postcode
Order/Product:
Order ID (CK)
Product ID (CK)
Quantity Ordered
Product:
Product ID (PK)
Product Description
Product Cost
Any help would be appriciated. Thanks for reading.
View 2 Replies
View Related
Mar 4, 2005
My database records order details for analysis. I have created a form to monitor individual trends in orders placed by my customers. As we supply bespoke items each customer has a list of products which they alone purchase, i.e we do not supply stock items, each item is specifically for one customer (they can then buy the items on a regular basis). The products are assigned to a customer in the products table.
The Analysis form I have created displays the customers name and two graphs. The first graph shows total sales each month, this graph updates as I navigate through the records (i.e. a different graph is loaded for each customer). The form also contains another graph, this is where the problem lies! This graph shows orders of the individual products, controlled by a drop down list containing all the products we supply. I need to limit this list to only show those products relevant to the current customer select on the form. (The products are assigned to a customer in my products table). Currently I achieve this by using a query which when the form is opened asks me to enter a customer ID, the drop down list is then limited to the products supplied to that customer.
What I would like is for the drop down list to update as I navigate through the records. i.e. when customer A is displayed show all the products assigned to customer A, when customer B is displayed show all the products assigned to customer B.
I’ve attached a snapshot of the form to help explain what I mean.
Thanks for any suggestions.
View 1 Replies
View Related
Jul 8, 2006
Hi,
I have 3 tables
Customers
Tranasaction
Items
Linked togather, working fine.
Now I have setup one form where i enter each transaction details
Item details, customer details and transaction details
I would like create customer find function, which will check current records for that customer account no, if customer account does not exist then form will let me create a new customer.
can anyone please tell me how can i do that . thanks
I tried using combo box, but shows me record but does not let me enter new customer account no.
thanks in advance : )
View 1 Replies
View Related
Mar 24, 2007
Hi all I have an order entry database. I need to talk through this problem I have, if anyone would like to let me call them then pls pm me.
Within the database there is an add ne w order & details form where, the functionallity behind this prolem will need to be explained to me in very shall we say easy terms.
I need the product price, to be different all customers. The product list is the same for all customers.
When the customer name is chosen by the "user" in the order entry form the form automatically tallies up orders based on the costs to that specific customer
any ideas guys on the best way forward.
Thanks for reading
Mark
View 1 Replies
View Related
Mar 7, 2014
I am in the process of putting together a database for use in a hotel to log details of customers, enquiries, and sales.
I have one table for storing customer contact details in ("Customers"), another table for storing enquiry details in ("Enquiries") and a third table for storing sales data in ("Sales").
Each record in Sales is linked to a customer from the Customer table, so with a simple query you can see what any given customer has spent their money on.
My boss would like to have a list of who her "top 10" customers are, over a given period, so she can work out who spends the most. I was wondering if there is a simple way to do this?
The only way to do this that I can think of is to run a query for every customer (to isolate their sales data), use the total function to add their sales together, note down the total, and input into excel (to work out the ranking). However, seeing as we have been collecting data for around a month now and have around 400 customers in the database this would clearly take a very long time. Is it possible to automate this process?
View 3 Replies
View Related
May 24, 2012
I have a table with all of the relevant data.
I want to automate an email to each one with the information that is held in the table. Much like a mail merge.
The email would be in one field.T
The subject would be: "Your item[ Item Name] has been shipped."
The body of the email would be: "Thanks for buying [product] on [date of purchase] etc etc."
My only problem is that I'm shit at code so I wanted to know if I could do it with Macros.
I could do it through a combination of using max records: sending an email, then repeating the exercise until all of my records have been sent.
View 1 Replies
View Related
Nov 15, 2007
I have a database that i am trying to clear out old customers that have't bought something in the last 6 months.
I have a table with the customer details in and another table with the purchases in.
I have been using the folowing criteria:
Not Between Date() And #01/05/2007#
But i think this still still shows customers who have actually bought more recent than 01/05/2007.
Is there a way to just show customers who have not purchased anything within the last 6 months of the date the query is run on and to only show their last transaction date.
View 4 Replies
View Related
Jun 9, 2014
I need a query to pick the bones out of sales transactional data like this...
Code:
CustomerName Currency
A Smith GBP
A Smith USD
B Jones GBP
B Jones GBP
T Brown GBP
T Brown AUD
C Wong GBP
C Wong GBP
S Giles USD
S Giles USD
From the above data, only A Smith & T Brown ...have made purchases in more than one (different) currency. How on earth do I trap that with a query? (or will it need a macro?)
If so, the table is called sales & the columns are as per the data above (CustomerName & Currency)...
View 14 Replies
View Related
Apr 10, 2014
I have a few duplicate customer queries (different duplicate fields etc). I would like to construct a form to make it easy for an end user to remove the duplicates.
The main problems are...
1. the newer customer is to be deleted
2. any/all orders from the newer customer will be moved to the older customer before deletion.
3. preferably an easy way to switch between the duplicate queries in the same form, though if this is a problem I could just have one small dialog box/form, linking to 3 different duplicate query/forms.
I think other changes between the customers will be easy enough to just do in the subform, i.e update email address/tel/status/salesperson etc. These can just be edited directly?
But the transferring of orders can't be edited directly because there may be several orders and could be errors etc.
tblCustomers [CustomerNumber] primary key
tblCustomerOrders [OrderNumber] primary key
[CustomerNumber] linking to tblCustomers
So all that needs to be updated is [CustomerNumber] in tblCustomerOrders. All orders for the newer customer to have the CustomerNumber field updated to match the older customer. Then the newer customer is deleted.
I would just have a button with a message box to run the code, update CustomerNumber/s and delete record.
View 2 Replies
View Related
Aug 13, 2014
Query which I want to create about finding the date of birth of the customer.
I explain:
I have a table (tblCustomers) which among others contains the field "DateOfBirth". I would like to create a query so that looking into tblCustomer about the dates where the month and day of birth of the client is the same as the Date () so that I know every day which customers have birthdays.
View 5 Replies
View Related
Nov 8, 2006
Hello!
Simple one for most, but I have having problems with a Database I am setting up
The database controls stock movement (i.e. inwards stock and outwards stock)
I have set up a Parts Inwards table with the following fields:
Part Number (linked)
Qty In
Stock Location
And I have set up a Parts Out bound with the following fields:
Part Number (linked)
Qty Out
Stock Location
Now for the problem, when I run a query to tell me how many parts I have left in the stock room i.e. Qty In - Qty Out, it duplicates the data in the Qty In.
I assume its got something to do with the fact that I have multiple Qty out’s for a single Qty In entry…hope this make sense?
Can someone help?
View 4 Replies
View Related
Jul 28, 2014
In MS Access i want to be able log jobs and be able to assign my stock to the jobs i have logged is this possible ? how to create a database to log jobs but how can i make it so when i add stock to the job it will remove it from my stock pool and assign it to the customer thus taking it out of stock.
View 3 Replies
View Related
May 8, 2013
I have a database that I will use for invoicing, but I would like it to automatically create an invoice for customers based on parameters set for that customer (e.g., monthly, biweekly, etc.). I have tables containing the customer information, the item they are being billing, the price, etc. I want to be able to have access automatically create the invoices and add them onto the invoice table each month.
Maybe there is a better way, but I thought that if I created a query for all people that are billed biweekly and all people that are billed monthly, that I could run the queries when applicable and then somehow write a macro that would go through the list of customers and add each of them to the Invoice table and add an autonumber. That way I could click run query, run macro, and then do my invoicing. I don't know if that is the way to go or not.
View 8 Replies
View Related
Jul 5, 2006
In a field “NAME” I have “Adam Smith”.
From this field I want in a QBE window (not in VB) to make two new fields “FIRST NAME” “Adam” and “LAST NAME” “Smith”.
In Excel there are the functions FIND and SEARCH with which I take the number of the gap “ ” (5) between Adam and Smith and with the functions LEN, RIGHTS and LEFTS I have a result.
What could I do in Access 2003?
View 3 Replies
View Related
Apr 28, 2008
Hi,
I am trying to delete a part of a string but not all of it.
For example; American Express Inc. I would like to delete only the "Inc.", but leave "American Express".
How is this done?
Many Thanks,
Tim
View 4 Replies
View Related
Mar 1, 2006
Hello All,
I am new to this forum :) and learning Access :confused: .
The Database
Tables:
Employees
Products
ExpectedQuantity
PartQTY
Parts
Most of the products are similar so they have the same parts for the majority. However they do not all have the same parts. So I created a parts table that contains all the parts needed for any product. Now each Product also can contain multiples of one part (such as wheels) where I would want to list the Product and the parts list and quantity needed of those individual parts for that product. Hence the PartQTY table.
Now what I want to be able to do, is go into a form where each record is one of the products. A subform (datasheet view) for that product that shows all the parts and the quantity of each part that make up the product. Such as 4
wheels, 5 screws, etc for Product A.
I would like there to be an ExpectedQauntity field in the main form which once you found the product you can enter in the number of that product expected. Then this Qauntity would be multiplied by the qauntity of each part required (in the datasheet subform a column with =[expectedQTY]*[PartQTY]) and list next to each part the number needed to order. So if for product A the expected quantity is 50 I would have to order 200 wheels and 250 screws for that product.
A report would Sum each part qauntity after the expectedqauntity has been entered for each product. So I could print a report that tells me how many of each part we need to order based on the expected qauntity of all the products.
I think I am complicating things and would like a fresh mind to assist me in which direction to go.
Thank You,
Adam
View 9 Replies
View Related
Feb 27, 2008
I know how to Grab the Right and left Characters and grab the mid characters from a field value
ex: Right([fieldName],4)
My question is how to I grab characters based on a space
Say for instance "Fred Smith" or a name field. There are going to be varing lengths of last names...but the format is the same...First Name, Space, Last Name....
QUESTIONS:
How do I search the string, count to the space, then take all characters after the Space?
One step further how would I grab the middle name in this example?
"Fred M Smith"
View 14 Replies
View Related