Forms :: Selection On Price / Stock And Delivery Time In One Query
Dec 13, 2013
I have a table with following structure :
Fabcode Price Stock Date Supplier
Fabcode : the unique code of the article
Price : the price by this supplier
Stock : quantities for the moment in stock by this supplier
Date : When there is no stock at this supplier, the estimated time of arrival
Supplier : name of the supplier
A selection for looking the lowest price for a supplier who has stock is no problem. But I want also the fastest delivery date when no one has stock.
Example
Fabcode Price Stock Date Supplier
Product1 5 3 A
Product1 6 4 B
Product1 7 5 C
Product1 8 6 D
Product2 14 73 A
Product2 12 56 B
Product2 15 14 C
Product3 30 0 30/12/13 A
Product3 24 0 B
Product3 25 0 26/12/13 C
Product3 32 0 26/12/13 D
Result :
Product1 5 3 A (because supplier A has stock and the lowest price)
Product2 12 56 B (because supplier B has stock and the lowest price)
Product3 25 0 26/12/13 C (because no one has stock, but supplier C has the shortest delivery time AND the lowest price)
Remark 1 : when there is no delivery date (and no stock), this supplier should be ignored for that product
Remark 2 : when no one has stock, the delivery date is priority, when 2 supplies has the same delivery date than the price is priority.
Can this in one query or SQL-statement ?
View Replies
ADVERTISEMENT
Sep 2, 2014
Say we have a table like the following, with hundreds of symbols:
SYMBOL
DATE
OPEN
HIGH
LOW
CLOSE
VOLUME
ALLTIMEMAX
[code]....
and we need to add the ALLTIMEMAX column. This is the MAX CLOSE price for the symbol on SYMBOL till the date on DATE.I think that if we can take the first CLOSE for each symbol, then for the following date wed just need to check if the new CLOSE is bigger than the previous. If it is, use it and if not, just keep the old one.
View 14 Replies
View Related
May 26, 2015
I have a form with continuous sub form.
The main form contains a combobox, populated from a query which pulls in specific data (time) from a table
The subform is linked to the combo box on the main form. Based on the combobox selection, the subform updates with associated records with the combobox selection
I would like to add additional functionality in the form load event, that would read the current time and identify the nearest value in my combobox.
I have tested the code below behind a button and it works
If Time() > "13.00:00" And Time() < "14:30:00" Then
MsgBox "The Time is " & Time()
cboPricingCADeadline.Value = "14:30:00"
Else
cboPricingCADeadline.Value = "NA"
End If
Would a loop through the recordset of the Combobox be best used here? Set the first and second values of the recordset to variables, query the time and then return the value if statement is true, or move to the next record in the rs replacing the first and second variable values
eg
If value1 > time() and value 1 < value2 then
cboPricingCADeadline.Value = value1
end if
If this is a good lead, how do I go about setting up my recordset?
View 4 Replies
View Related
Jul 29, 2013
I have a table called Books, in that table there is 4 columns ChapterName, Auther, ITEM, Price.
Each book has a item number, and each book has a few records with the same data, just the first column is different where its the ChapterName, each book has a price, but only once, meaning in the first record of each book it will be a price in the column price
Now I want a Query where i can get which book dont have a price at all, and which book has more than once a price, how can i do that?
View 1 Replies
View Related
Oct 27, 2014
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox:
SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox:
SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
How to get the checkbox selected for each corresponding staff.
Sample of the database has been attached.
View 4 Replies
View Related
Dec 27, 2013
I have a database I'm creating for DVD/blu-ray sales. I currently have a order system in place in a subform, which uses a combo box to find the customer and fills in all the information in the rest of the subform. One piece of information is the date of sale.
I need to be able to have the ability to change the price of the DVDs and have a history of sales with the right price. The easiest way to do this seemed to be by Date of sale. So the price is linked to the date of sale, when the price changes, all orders before that change use the old price, after use the current price.
I was wondering if there is anyway to have a second subform on the same page and us the date of sale from subform1 and have it display the price in the second subform?
View 7 Replies
View Related
Jan 7, 2006
Hello All,
I am making a table/form that will look at the wieght of an item and work out a costing based on a number of different delivery companies and will find the cheapest, I would like any comments on how to go about this.
Alastair
View 2 Replies
View Related
Aug 20, 2014
I am new to Access and I have get to where the users can make selections in the listboxes, but then I need to save the selection in a query or table to save thier changes.
View 1 Replies
View Related
Feb 18, 2015
I have created form with multiple tables field in some of these fields I have assigned the combo box selection the total combo boxes in form is four and all are unique numbers but this form is incomplete.
1.When I select or update any of four combo box then remaining combo boxes and fields on form should be updated automatically with related records.
2.I need to bring calculated fields from multiple queries on to my current form .how I can insert query fields in to form...
View 1 Replies
View Related
May 27, 2006
For an assignment I am required to do the following:
Quote: Use an expression (in field name of design view by using the build button) to create a new field (in Design View of a Query) called "Discount Price" in table "Stock" which is a 25% discount for all stock items from the new inflated price in query B (use expression builder if needed and create it the field name of a query; it will not appear in the table if you try to open the table).
So sorry to bug again. Any ideas or processes on how to go about this would be welcomed
View 2 Replies
View Related
Apr 1, 2015
We have this database:
It has Symbol on the first column, Date (daily on trading days) on the second and Close on the third.
We want a query that shows the last CLOSE of the month for each symbol and sum the volume of the month.
The query table would be like this:
Symbol
Date (last date of the month)
Close (last date of the month)
Volume (sum of whole month)
Here's the sample file:
Sample.accdb
View 9 Replies
View Related
May 24, 2013
This is a Customer Orders database created in Access 2010.
tblProducts has field UnitPrice.
I want the unit price to auto-fill based on product in tblOrderDetails.
Seems simple enough, but I can't figure it out. I don't know VB other than inserting code someone else has created. Need to be able to do within Access.
I've researched this and found many databases of this sort that has the Unit Price in the Products table but you still have to input the Unit Price when adding an order. If you have to manually enter the Unit Price on an order, what is the point in having it in the Products table?
View 2 Replies
View Related
May 1, 2015
I have a list box that is correctly listing resources from a table (tblResource). I would like to select one of the list box entry and run a query against the tblResource to show the information for that resource in a form. I have tried to use the lstindex with the control (lstResource) in the where clause of a query to accomplish this with no success.
View 5 Replies
View Related
Jun 11, 2013
I have a table with several fields including the following 3 fields:
dtmAppoint Date = stores appointment date
dtmAppointTime = stores appointment time
lngAppointDoctorID = stores doctor's id for specific appointment
When I enter a new appointment, I want to know whether a specific combination of these 3 fields already exists. The doctor is actually a dentist and maybe another appointment has already been placed for the same dentist on a different chair. Creating a recordset as indicated below works but I am having some problems with comparing Time (dtmAppointTime). The following works but I need to extract Hour, Minute and Second and put it into string variable strTimeDum.
Code:
rstSearch.Open "SELECT * FROM tblAppointment " _
& " WHERE (clng(dtmAppointDate) = " & CLng(rst!dtmAppointDate) & " )" _
& " AND (dtmAppointTime = #" & strTimeDum & "#)" _
& " AND (lngAppointDoctorID = " & lngAppointDoctorID & ");", CurrentProject.connection, adOpenKeyset, adLockPessimistic
Any way to retrieve the records that correspond to the specific time or a simpler way to access the records of interest?
View 10 Replies
View Related
Aug 8, 2006
Hello,
I'm setting up a delivery cost and location form. What i need this form to do is say i select Guildford for delivery i need the delivery cost text box below to say £12.50 without manually looking up the costs for that area and if it was Farnham then £24.00. What is the easiest way of doing it?
Thanks
View 3 Replies
View Related
Aug 14, 2014
when i want to make a delivery note - on many cases the Delivery Note Number that shows has more than one house name, Plot Number and Product - at the moment it only shows one specific house name, plot number and product when i would like it to show every one of these under the delivery note number and delivery note date?
View 2 Replies
View Related
Apr 28, 2005
My table, TblTYPE, will contain two field.
TYPE
DURATION
There will only be 7 records in this table:
FUS, 30 minutes
POS, 30 minutes
PRE, 30 minutes
NOV, 90 minutes
FUL, 90 minutes
ANN, 90 minutes
NPB, 60 minutes
1st question: How do I enter only minutes in the table for field 2?
------
I want the following to happen.
The end-user enters the start time and the type. As soon as the type is entered, field three calculates an end-time based on the start time and the type. When the type is entered, the minutes listed in field two of the type table are added to the start time (giving you an end time).
Can anyone help me?
1.) How to properly format the minutes in the original table.
2.) How to add the minutes to the original time in a query.
Thanks!
Scott
View 2 Replies
View Related
May 2, 2007
ok i have a table containg two columns. One column has a list of items and the second column has their respective prices. I wanna make a query that only displays each item and the cheapest price. Does anyone know how to do that?
View 5 Replies
View Related
May 20, 2014
I need to make an order purchase form like i have customer table and product table, i want to make a form which creates order for a specific customer and I can add as much items as i want and than calculate auto the total price, and an option to make a report for it.
View 5 Replies
View Related
Jan 9, 2014
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I'm using 2003 and have some VBA ability.
View 5 Replies
View Related
Mar 4, 2015
How to set a Default value "0" in Query Extended Price Calculated field ?
I have attached the screenshot with explanation, how to changes the formula.
I have used below following functions but there is no workout.
Extended Price: CCur([Qty]*[BPrice])
Extended Price: CCur(Nz([Qty]*[BPrice],0))
Extended Price: Nz([Qty]*[BPrice],0)
Extended Price: ([Qty]*[BPrice])
Extended Price: IIf(IsError(([Qty]*[BPrice])),0,([Qty]*[BPrice]))
View 4 Replies
View Related
May 9, 2015
Im creating a database that we can enter jobs into.A job will consist of 3 seperate parts, Called "Trusses", "Walls", "Posis". They will each have a different delivery date and possibly a delivery time.
I am having trouble creating a report that gives me a week view, Monday to Friday, that shows what deliveries are on each day.The main thing here is there is one job entry, with the 3 parts. So there will need to be 3 seperate entries on the report for each part of that job.
View 8 Replies
View Related
Aug 21, 2014
I have a sub report on my form to show all data needed for a delivery note.
these fields are :
House Type
Plot No
Product
Delivery Note No
when i use my query to search orders in my subreport - it will show the required data but will also show data i didnt search for ? why is this?
Sometimes my search results in the subreport show further down the subreport than the irrelevant data - all i would like is for it to show the specific data not the irrellevant data.
View 3 Replies
View Related
Sep 11, 2007
Query does not return all stock. In basic form the database has 3 tables. One is “stock” another is “stock in” and the third is “stock out”.
I want to run a query using all three tables so that I can take stock out away from stock in. The problem is the column stock in (in the query result) only shows stock that has a value in stock out. If any stock items have not had stock go out the stock does not appear in the query result. I want stock in to show all items regardless if stock for that item has gone out. I think this can be done but I cannot figure it out.
Sorry if I have not made myself clear.
Please Help. Thank you Keith Hawes.
:(
View 7 Replies
View Related
Sep 11, 2007
Query does not return all stock.
In basic form the database has 3 tables. One is “stock” another is “stock in” and the third is “stock out”.
I want to run a query using all three tables so that I can take stock out away from stock in. The problem is the column stock in (in the query result) only shows stock that has a value in stock out. If any stock items have not had stock go out the stock does not appear in the query result. I want stock in to show all items regardless if stock for that item has gone out.
I think this can be done but I cannot figure it out.
Sorry if I have not made myself clear.
Please Help. Thank you Keith Hawes.
View 2 Replies
View Related
Mar 8, 2006
Hi
I am building a stock control database, which uses a barcode reader to input data into the fields.. (stored in a product table)
For a sale the item is scanned.. and the details are displayed. However is there any way which the same form can be used to have multiple text boxes with the same names..
So in essence I need to be able to view more than one sold item on one form.. is it possible to have rows of different transactions??
Hope that makes sense.. any assistance would be great!!
Sunshine!!
View 1 Replies
View Related
