Hi,
I have a table containing the following fields;
ProductID (UniqueID)
Date
Price
Each day some, but not all, of the products in this table have their prices updated.
When I write a query how can I ensure that the latest price is recalled ?
Any tips greatfully received.
Hi guys
I am trying to create a simple database to keep track of computer components that I order for custom PCs. The data is fairly basic Quantity, Description, Stock Code, Delivery Status, Cost, Cost+VAT, Date Delivered. The list is currently 500+ rows of data. I simply cut and paste this info directly from my online invoices.
What I want is to be able find the the last price paid for an item (often the same items have different prices week by week) Eventually I would like to be able to create a rough quote using the latest prices (plus mark up) and also view a price history.
I would also like to include a few pictures as URL links to the suppliers website ????
At present the data is in Excel and linked to a single table in Access (but this can be changed). I realise that I have to group the records somehow but cannot get it to work. At present it lists every record either in date order or product order.
The stock code should not change, but product descriptions do. So any links to pics would be based on stock codes
Someone please tell me how to display the full list of products but only the latest price/date
If you think the current format is rubbish then feel free to suggest an alternative. I much prefer Excel but have never really got to grips with Access (used to be a vba programmer with Excel). To link to pictures and hide data that customers might see Access seems the obvious choice. So I try again!!!!!
Thanks
Steve
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?
Giving up after a zillion tries. I have a table (tblLOADS) containing: BROKER, PUDATE, MATERIAL & DRIVER. I am able to create this query:
SELECT tblLOADS.L_ID, tblLOADS.BROKER, Max(tblLOADS.Pudate) AS MaxOfPudate, tblLOADS.Material, tblLOADS.Driver
FROM tblLOADS
GROUP BY tblLOADS.L_ID, tblLOADS.BROKER, tblLOADS.Material, tblLOADS.Driver
HAVING (((Max(tblLOADS.Pudate))>0));
Problem: It's datasheet view displays all of the records for BROKERS, PUDATE, MATERIAL & DRIVER, instead of ONLY the records for the last PUDATE of each of the BROKERS, with their corresponding MATERIAL & DRIVER fields.
I must admit I am a "newby" to Access but I have bought my Access 2000 bible, as I am running Access 2000, and attempted to create my desired database. I have linked to 2 tables that I download on a daily basis. This is my inventory from two different suppliers. I then have created two queries that filters each of these files to only show positive quantity items, filters out item specifics, etc.What I want to do is join the 2 queries with their data already filtered. Both of the files from 2 different suppliers contain some of the same data. Once joined if there is an item that is the same I want to delete the higher cost item and only show the lower cost item, I also want to show the rest of the items that do not match. It is easy to identify the same items as every item contains a 12 digit identifier called a upc.I can create a UNION ALL query that shows every item from the two queries. However, I am unsure as to how I go about deleting the higher cost item and only showing the lower cost item if the item is the same. On a side note after I get the results desired I will then be createing an append to query and append all this information to another file that is then uploaded to another system.I will try to attach some sample data that I am using from the two queries I want to join, this data is only a sample of a much bigger file.Thank you and any and all comments or suggestions is much appreciated.
View 3 Replies View RelatedHi All,
Long time no see y'all...
Question: is it possible to obtain a list with all the Access based System or User DSN entries on a system? First I don't know how to get such a list at all, and second I don't want to bother the users with non-Access entries - they're confused enough as it is.
Any help appreciated & regards,
Michiel
Hello,
I've got a workaround for the SQL of obtaining a count of tables matching a certain name-style. Ideally, I want to use the Like function (sql2) as it codes more simply, but found that the query doesn't work. As a workaround, I rewrote with a string comparison using the Left function sql1.
My question is: What is wrong with sql2?
When I paste the SQL into the Query Designer it works, yet when I debug in module code form, it fails.
Any ideas?
Regards.
Private Function getTableName(strName As String) As String
'---------------------------------------------------------------------
' Purpose
' This function is used to create a new table name. First it looks up
' all the table names in the database matching the passed in variable
' name, and then returns an incremented version of the name.
'---------------------------------------------------------------------
' Returns
' Table name as string.
'---------------------------------------------------------------------
Dim sql1 As String, sql2 As String, rst As New ADODB.Recordset, n As Byte
' Get number of characters for passed-in variable.
n = Len(strName)
sql1 = "SELECT Name FROM MsysObjects WHERE left(Name," & n & ")=left('" & strName & _
"'," & n & ") AND (Type=1)"
sql2 = "SELECT Name FROM MsysObjects WHERE (Name Like '" & strName & "*') AND (Type=1)"
rst.Open sql2, CurrentProject.Connection, adOpenKeyset
Debug.Print sql2
Debug.Print rst.RecordCount
If rst.RecordCount >=1 Then
getTableName = strName & rst.RecordCount + 1
Else
getTableName = strName
End If
rst.Close
Set rst = Nothing
End Function
Hello to you all...
I am just after a bit of assitance if anyone can kindly help?
Basically, I need to redevelop a Complaints database for my employer as the current one is no longer fit for purpose. The current database is a FE/BE database and users are required to login with a username and password. This causes me a massive headache as users constantly forget their password and email me to reset it. I had included a password reminder form which emailed users their password if they got their pre-defined security questions correct but our business standards dept did not want us to store personal data for the reminder questions. Is there a way that the database can obtain the users network login ID from windows without too much difficulty?? We had something similar in a previous database but it dipped into the system registry and our IT dept had a fit when the found out....not surprisingly. :-)
Thanks people. Have a great day!
I have three tables A, B and C.
I want to build a query that displays three separate totals: Total of A, Total of B and Total of C.
When I try to do this, the query has a weird behaviour summing the totals of the totals of the totals, and I don't know why .
I'm attaching a really basic sample of what's happening, where totals SHOULD be $4, $8 and $16 instead of $16, $32 and $64.
I want to use the create date of a file which is stored as a linked table in my Access 2010 database. I have attempted this by creating a query (qrysysObjects) using the MsysObjects and that gives me the filename of the linked tables, well almost - a few are csv files and this does not have the file name in the database field text, but I have found it in the foreignname field except it is name#csv rather than name.csv so I can't simply concatenate this in a query.
I know there is a function called FileDateTime which you can use in vba or a query but can you use a variable with this rather than the actual path name? I tried this in the query qrySysObjects but it said unknown function.
In our Student Administration database, we have a Student Evaluation Report which prints a 1 page per student report in memo like format. The instructor had previously input rating numbers (4=Best, 1=Worst) for 9 categories for each student via a database form into the Evaluations Table.
Two other elements of the rating are an Attendance based on number of days absent and a Grade Point Average (GPA) calculated from the student's test scores. The instructor then prints and reviews the report with each student.
There is an Evaluations Parameter table which has the following fields:
Field
Description
ID
The autonum key field.
Class
eg., 2015-1
Evaluation Number
A single digit (eg., 1, 2). There may be more than 1 evaluation for each class.
EffectiveDate
The "as of" date of the evaluation.
There are then several other tables that are input to a query that will be the record source for the report:
Table
Purpose/Data
Students
Student Name, Class
Absence
Has a record for each student's absence with date and a 1 or .5 indicating a whole or half day absent.
Test Grades
Has a record with each student's test results with date and score.
Evaluations
Holds the rating score for each of the 9 rating categories.
The Student Evaluation report is launched from an unbound Reports menu form via a button. On the Reports menu form I wish to have controls for the user to select which evaluation to report on. The Absence and Test Grade information needs to include records that are <= the EffectiveDate in the Evaluation Parameter table. The Evaluations records need to match the Class and Evaluation Number in the Evaluations Parameter table.
I would like the user to be able to select the Evaluation Parameter via a combo box vs. specifying the Class, Evaluation Number and Effective Date separately and then have the query record source for the report use those two fields as criteria. But my limited understanding of the Combo box indicates that only 1 field from the lookup query of the Evaluations Parameter table can be stored in the control whereas I need 3 (Class Evaluation Number and EffectiveDate).
How the user can select the desired Evaluation Parameter record on the Reports menu form and use the 3 fields from the selected record as criteria in the report's record source query.
After being a member on this site for a while now I would like to get some opinions on "cost of application development" I know the forum covers a wide geographic area and the members cover a wide spectrum of degrees of expertise on Access and application development. So the question is this..What would your rates be and how would you determine your charges for developing Access applications for a business?PS... Please include your location if not in your profile.
View 13 Replies View RelatedHi
I have a problem which hope will have a simple solution, which has been
driving me mad all day
I have a client who has two prices for the same service depending on speed
of turnaround
These prices are entered into the Client table seperately using the CDetails form.
I then wish to use this information using a combo box for selection within
another form called SDetails.However I can only get the combo box within
SDetails to put one of the two prices rather than either of the two choices,
because of the bound column within the combo.
The combo when pulled down looks something like this
Price 1 Price 2 Client Name
I would prefer for
Price 1
Price 2
so that I have the choice of the price I want to choose.
Having spent time and many brain cells, I have ground myself to a stop.
HELP!!!!!! if only for the sake of my sanity
I am creating a db that handles orders.
My problem is that the products being sold are so at different prices depending on the customer. (e.g I am selling 100 burgers to Mcdonalds for £50 whereas to Burger King I am selling them for £35).
Therefore I have split my 'Products' table into two. A 'Products' table which contains the product name and category. And a second 'Prices' table which contains the Product name, Customer and the price.
I have setup a relationship between the two tables based on product name
How to I go about looking up the different prices depending on which customer I have placed an order for?
can it be done in a table or do I need to create a query to sort it all out.
I am in need of some assistance. I've been looking on here for an answer, but can only find solution that would give me a by column lowest price, and I need to compare the prices by row. What I have is a table of vendor prices for part numbers we sell. What I want to do is compare all the prices the vendors for each part number and return the lowest and 2nd lowest fields in a query. Here is an example...
Part # vendor 1 vendor 2 vendor 3 Lowest Vendor Price Low Vendor #
10526 .75 .97 .67 .67 3
10527 .60 .82 .85 .60 1
Thanks for any ideas....
ok i didnt know what section to put this in so i put it here. what i wanna do is add a price change history to my database. for example is the price of an item is changed in my database i want to be able to see what the previous price was and when it was last changed, either in forms of reports doesnt matter i just need somewhere to view this info. problem is i have no idea where to start.
any help provided is muchly appreciated thanks.
Hi all,
Before I start, please know that I am what you call a newbie to a certain extent even though I created in the last 3 years a very complex and efficient database for my business.
This is going to be hard to explain, but I'll try.
The time came to stop creating the Price List in Excel and copy and paste in Access.(mainly to make it easy for other person to maintain and understand)
I know more or less the tables that will need to be created.
My problem is how to store calculated fields in a table (I know I really shouldn't, but how else can I accomplish this), since all the costs and selling prices are the result of complex formulas.
If this information is not enough to understand my question please let me know.
Thank you for any help.
Emilio
Hi all,
I started a new thread, because it is a new subject even though is related to my Price List thread, I hope is OK.
If someone can please take a look at my attached Excel Price List (particulary the factors sheet) and give me an opinion of how my tables should be created.
I cleaned the Price List was too big to attach and it will be easier to be understood, in its entirety is kind of all over the place, exactly why I need to make simpler in Access for another user to update if I am not around.
I am below including what I think the tables should include, but not sure exactly how they should be, please be reminded that I am un unexperienced newbie.
----------------------------------------------------------------------
tblSuppliers
SuppliersID
SupplierName
SupplierCode
tblMarkup
MarkupID
MarkupName
MarkupAmount
tblTaxes
TaxID ------Do I need This?
TaxName
TaxAmount
tblExtras
ExtrasID
ExtrasName
ExtrasPrice
SupplierID
tblDiscounts
DiscountID
DiscountName
DiscountAmount
SupplierID
----------------------------------------------------------------------
Thanks for any help,
Emilio
I am building a database in Access which I would like to eventually link to my website in order to sell hotel bookings online. I believe I have set all the fields up properly but when it comes time to set the price I'm lost. Each hotel has about 3-5 different prices throughout the year depending on the date. How can I design a table(s) to accommodate this? Any help is appreciated.
View 3 Replies View RelatedI am stuck for a calculation expression for 'extended price' which can be seen in the attached screenshot.
I want to times item price by quantity and deduct the discount.
Thanks. :)
I have a combo box which looks up vessel names, it stores the Vessel ID in a field [Vessel ID]. This all works fine, no problem, easy. However as part of the vessel details I also need to know the service the vessel is providing “transhipment” or “direct”. If it is a “transhipment” service I need to show a second Combo box with the on carriage vessels. That’s also easy, I just hide the second combo box and only show it depending on the service status of the vessel.
Now I figure I just need to have a field [Vessel Status] which is updated after the first combo box is updated and then depending on the value show or hide the additional combo box. The problem I’m having is updating the [Vessel Status] field once the first combo box has been update.
Ok, having a very beginer problem here (I Think)
Here are my relevant tables:
I have a table called Items which has itemCode (primary key), itemDescription, and price.
I have a table called bidItems which has bidCode (primary key), itemCode (primarykey), and some other info
I have a table called Bids witch has a bidCode (primary key), and a bidDescription
Here is a form that I use to enter data into these tables and (want) to view prices and totals:
I made a form based on the Bid table with a continuose subform based on the bidItems table, with combo boxes that lets you select Items by itemDescription (then it stores itemCode automatically with the correct bidCode)
What I can't figure out how to do is get each item's price to automatically display on the form (remember price is in Items table, not bidItems). I've tried a number of things that havn't worked out.... any ideas. Then, ideally I would like the form to display the total of all the item prices. (if you have a pointer on that as well it would be much appreciated)
Thanks
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
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
How do I make an append query only append the records beginning with the next autonumber?
I have linked tables in db...one db is for warehouse employees to input their orders and the other table is in the original db for managers to use for reports. I need to append the records to original db throughout the day, but the records cannot be deleted from warehouse db because it keeps running total of order minutes (for production purposes) so employees know how many minutes they have (and need to make up) through the day. Also, managers need up to date db so they can see if they are on track.
The autonumber field is my primary key..
I hope this all makes sense.
Thanks in advance
Noreene
I've run several searches and can't seem to find what I'm looking for, so here goes:
Have a table with several (maybe as many as 30) dates related to each employee, such as:
NAME-------CLASS 1-----CLASS 2----CLASS 3
J.Johnson.....10/16/05 ......12/17/05...12/25/05
S.Smith.........09/05/05......10/15/05
Not all employees will have entries in every class.
Need a query to result in most recent Date for each employee, such as:
--------------
J.Johnson......CLASS 3........12/25/05
S.Smith.........CLASS 2.......10/15/05
Any suggestions?