table1_ProductList -> has the following fields:
ID (primary key,auto number)
SERNO(text)
DESCR(text)
PRICE(currency)
table2_Jobcard -> has the following fields:
ID (primary key,auto number)
PROD_DESCR_ISSUED (droplist, get 'prod desc' value from table1)
PROD_QTY_ISSUED (decimal)
DATE_ISSUED (datetime)
___________________
PROBLEM/QUESTION :
Let's say I change the PRICE(in table1) on 1 Jan 2005, then all EXISTING records (before 1/1/2004) will also be updated with the new(changed) PRICE.
This is WRONG, right?
What is the correct procedure to use/update the PRICE value ??
Any advice will be greatly appreciated.
I can't seem to figure out the proper syntax for the FindFirst method. I am using several variations of this effort:
Dim dbs As dao.Database Dim rst As dao.Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset) rst.FindFirst "rst!ID = frmInvoice!txtID"
I get an error message that says that Access doesn't recognize rst.ID as a valid field. But, it most certainly is. I tried substituting tblInvoice but got the same error.
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?
I have a field (date field) that when I try to imput data will tell me that that what is being imputted is not in the correct formate or to large for the field settings. It is in the right formate etc. Is the field size applicable to just that field or overall everything entered in that field in the whole database? It's a decient size data base and I'm wondering if everything in that field is over the size, but then how can that be?
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.
I need to display the year, Date, agent name, amount field, note and sub total for each year.I need to write the SQL query code in VBA and i designed the report with proper field positions.
YEAR column: Display Value only once for the FIRST ROW for Maximum value of Date field value for each set of YEAR. If it has only one record, it display that year.SUB TOTAL Column: Display Total Value for Amount field only once for the LAST ROW of each set of year record (Mininum value of year). How to make it available the values for first and last record alone ?
I just wrote my SQL code based on your inputs in my editor..Based on the input from Plog (from this forum), i wrote a code which listed below.
Code: Private Sub Report_Open(Cancel As Integer) Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, CessioneCredito.Importo, " & _ " Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize,
[Code] ....
While saving it, it shows the code in RED COLOR due to compilation error.As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query as per Plog???
Change the Year Control Source to this: =IIf([GroupPos]=1,[Year]) Change the GroupTotal Control Source to this: =IIf([GroupPos]=[GroupSize],[GroupTotal])
If this is not possible in Me.recordsource, then its advisable to use recordset like writing in two different queries.
Code: Dim ds1 As Recordset Dim ds2 As Recordset myquery1 = "SELECT CessioneCredito.ID_Agente, CessioneCredito.Data_Movimento, CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))"
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.
I have created a report and, in the unit price field, I have set the decimal place to 5 in the report properties. Sometimes we have pricing for items up to 5 decimals long, such as $10.02985. It is rare though, so I'm to see if there is a way to have it drop the zeros down to 2 decimals when more is not needed. I'm thinking maybe a VBA event might work, but not sure.
I would like to display a report based on the table called "expenses", filtered by:
- "from" (datefrom field) and "to" (dateto field) date on "payment date" field; - multiple criteria on same field called "payment method" (I would like to include only payment methods "check" and "credit card", but not the other payment methods in the field, such as "cash", "transfer", etc.)
For that end I made a query based on the table "expenses", and in the "payment date", in the criteria field, I entered:
between [form]![formname]![datefrom] and [form]![formname]![dateto]
This works fine so far, however when I attempt to add multiple criteria on the "payment method" field, it does not filter accurately any longer. In the same row of the criteria field where I completed the date criteria, I enter "check". In the next row, same field, I enter "credit card".
Since it doesn't work, I tried putting both arguments in the same line as the date criteria (always in the payment method field) as: "check" and "credit card" but still does not work (now it filters the payment method correctly, but the dates filter appear as if I have never completed them).
And I thought this would be easy. I am trying to make a db of storytelling kits for my workplace. I thought it would be easy. 3 tables. Link. Make a form with the kit details and 2 subforms where I'd enter all the books for each kit and all the activities associated with the kit. Each page would show only the books/activites that the kit contained. Pfft. None of that worked so I'm going to be a real dummy and ask what to do with my relationships. So far I have
Book -ID -Title -Author -Format (lookup table to another table)
Activity -ID -Name -Description -Materials -Age Group
Format -Description
Format is linked to book (cause it was just so I could have an easily updated lookup). Activity and Book are both linked to Kit. Each kit has many activites and many books (fine) and I'm tempted to just leave it as each book only has one kit (even though that may not be the case in the future). How do I set it up so that it works? I enforced referential integrity but that screwed up my form by telling me that I couldn't enter data into the kit area until I'd entered it into the book area which was useless as we enter the kit details and then find things to add to it.
Any ideas...just in general. Sorry to be a pain yet again.
I recently created a database and installed buttons to properly close the database to avoid coruption of files etc. However I could not find a way to eliminate the X button in the upper right corner of access. Today my dbase crashed, I presume because someone did not use the buttons I installed, and just closed using the X in the upper right corner of access. The database is on a network, and was not password protected at the time. It will be when it is restored, however I would still like to know if there is a way to eliminate the x button in the upper right hand corner so that users will have to use the buttons created to properly close the database.
This is kind of a weird question. I downloaded and imported a table with cities and zip codes in it. All the city names are in upper case. I want to use the city names in that table as the row source for my City text box.
But in my database, I store city names in Proper Case, with just the first letter capitalized. So when I use the table as the row source, it pulls in the city name all in upper case
Is there a way, either within Access or not, to change the city names in that table to Proper Case?
Is there a way to correct an address field to Proper using a query? I have existing data with various formats. I am not able to correct it, but would like to create a new table using the data but with proper format. I do not write code.
Does anyone know how I can use the Proper Function in an append query.
I have a field whereby all the data is in UPPER case, but I only want the first character of each word to be upper case.
I have looked up the Proper function, which should do the job, but when I apply it in the specified field I get an "Undefined function "Proper" in expresssion message
my expresssion is as follows: salutation: Proper([strSalutation])
I hoping someone might know what expression I need to create to convert text in a field in an append query to proper case.
The reason for this is that I am receiving data in all uppercase and really need it to be in proper case, that is only the first character of each word in the field is capitalised.
A field name for instance is strProductDescription and I want the data to change from "FRIDGE FREEZER" to "Fridge Freezer" on appending the data to another table, hence a proper case function or expresssion.
(I use the sample database "Northwind" for my question)
I wish to do as follows: I added a field to the "customers" table, and name it "customer status". This field should be updated by update query as follows: I wish that for each customer that his total purchases wil be calculated through the "order details" table. If I multiply the "unitprice" in "quantity". I know how to make totals query, so I can see the total amount of orders per customers.
I wish that the "status" field will be update as follows: if the total amount per customer is higher than 100,000 the status will be update to "Gold Customer", if it is between 50,000 and 100,000 it will be updated to "Silver Customer" and the rest will be update to "Standard Customer"
I am trying to build a DB to fix movie folders and file names and I am struggling to properly find the file names.
Currently I am using "DirectoryName = Dir(folder, vbDirectory)" to find the files and loop through them correcting the names as I go.
Works perfect.
The issue I am having is how to properly then look up the file within the folder since Dir is already in use for the loop.
The straightforward question is how does one look-up a child file when the parent is known without using Dir().
Below is what is works to rename parent folders. I am sure it is hack to the trained eye but it gets the job done.
folder = "E:Videos" DirectoryName = Dir(folder, vbDirectory) Do Until DirectoryName = "" If DirectoryName <> "." And DirectoryName <> ".." Then If (GetAttr(folder & DirectoryName) And vbDirectory) = vbDirectory Then
I have a table which contains information about personnel. There are several fields which I want to have consistent values inputted. For example, somebody might populate the "State" field with: California, CA, Ca, or C.A. To avoid this, I created a seperate lookup table with a list of all states, fully spelled out, and the digraph abreviation associated with each state. The digraph is the primary key for this new table. In all, I have 12 such lookup tables.
Having all these extra tables, while nice for clarity sake, seems excessive. In most cases, the lookup tables really only require one column, though I've always used a minimum of two (one is an acronym set as the primary key, and one is a fully spelled out description). I'm finding the primary key is often not useful to somebody reading the data; the full description is much better.Could I have one catch all lookup table which combines all of the standardized fields which I want to use, and have no primary key for said table?
Is there a proper time? Or is it just anytime after you create the tables. Just wondering? I’m about to deliver part of a project today so they can start entering data. Much of the development is yet to come. Should this be split?
New to Access, and having a heck of a time learning it...or rather learning how to correctly design databases.
All my expertise is with Excel. I'm creating a project where I use Excel to parse a non comma delimited text file, then feed certain figures into an Access database. This is all through VBA.
It wasn't until yesterday that I realized I had a problem. There are two text files with data that makes up one complete record. With what I already have built, and with what I have tought myself (ADO w/ VBA wise) I tought the easiest solution would be to create two tables that will hold the data from each respective text file. This is what I'm working with:
A store has a department with 5 areas of measurement that is collected daily. One complete record would be like this:
Date | Store | Dept | Sales | Cust Count | Item Count | Avg Price | Mix
...and there are (right now) 3 stores and 15 departments that are watched in this project. What I came up with for a table design was this. Fields with an "!" prefix reflects primary keys.
Table1 (using data from txt file 1): !Date | !Store | !Dept | Sales | Mix Table2 (using data from txt file 2): !Date | !Store | !Dept | Cust Count | Item Count | Avg Price
In each table, I have to have a compound primary key to make up what is a unique record. I just learned I could use a compound index and an autonumber as my primary key. Either way, I'm using the 3 primary keys in each table with a 1 to 1 relationship. This seems to work if I make a query.
Now, will the way I did it hold up? Is there a better, more correct way to do it?
I have updated my copy of a database which included updates to a number of forms. I now want to import those changes into the proper database, so I used the Import function. I didnt get any errors when I imported everything, but the forms do not work as they did in my copy of the database.
One of the forms that dont work comprises of a number of drop down boxes, I have updated these so that the user has a filtered selection, to do this I put an SQL function in the Row Source property. This SQL function has been copied over correctly in the imported form, in fact when I go into the form in design view to check this fact then revert it back to normal view (after having not changed anything) it then works and all the other drop down boxes that didnt work on that form now work! How can viewing a form in design view and not changing anything make it now work?
So I could go into each form in the design view, check the Row Source property, then revert back to normal view and it would solve the problem of my forms not working, but, this database that I am importing the forms into is the back end of a split database and the first thing that the front end of the database does is import the changed objects into its database but the forms dont work.I have made changes in this way before and not had any problems before.
I have attachments I want to store in my database, most are images, some are excel files, etc. I'm sure its better to store a reference to the file instead of the attachment itself. Whats the best way to do something like this? Id like the attachment to still be displayed in the form if its an image...
I am trying to change all the fields of a table from All Caps to Propercase. I have used strconv propercase before, however only on a field by field basis. I have about 5 or tables that I have to do this to and am trying to save the typing. I know I can do this with a recordset (easily in asp), but am not familar enough to do this in vba. Can you do a for each fld, like in asp, or am I going at this the totally wrong way. I tried using an update query with the *, but access doesn't like that too much. Any help is greatly appreciated.
