I feel like this is a stupid question but here goes:
I just created a large database of which a few fields are going to represent "total" fields as they are the sum of a few other fields.
I attempted to build an expression in the "update to" box in an update query but it does not seem to be working. I tried creating a simple addition expression and it tells me that I am about to update XXX number of fields but when I look at the data afterwards no changes have taken place. I am not familiar with how to use the Sum function so it was not working for me.
I'm not sure if I'm going about this the wrong way but if someone could push me in the right direction I'd appreciate it.
I am making a Make Table query where I want to add a new field called [Timesheet Date]. How do I ensure that this field is a Date type? I ultimately will populate the new field with valid dates.
I have a first name, last name and a company name on the customer/contacts table. I would like to figure out some way to create another new field that contains either the company name or a joined first and last name if there is no company name. I am sure I have seen this ability somewhere, but I am not sure about how to go about doing it. Any advice would be great. Thanks, James
I think that creating a new primary key is the only solution to this problem, but I would love to have other input, including potential pitfalls I might encounter in this process.
Here is the scenario: I have spent the past year creating a "survey based" database in my "spare" time at work. The database holds detailed information about nutrition questionnaires given to children over the years that they are in our program. I have based the structure on using ClientID as the primary key. ClientID = each individual's social security number.
We have another database that holds all the client's information. This is the database where any updates are done to their information. I export the client information (firstname, lastname, birthdate, socialsecurity, plus a few more) that I need into excel. Then I import that info into my database on a regular basis using an update/append query.
We started using the database in September and all was going well until a few weeks ago when I imported data and found a name that was entered twice. On looking closer, I found that the person had two different ClientID's. How could this happen...? Well, in our other database, if a child doesn't have a social security number yet, or we weren't given it, the program creates a fake social security number AAA-##-####. I knew this from the beginning, but wasn't aware of any problem. However, I was missing a significant piece of information. When the child finally does receive a SS#, we go in and change the number in the program!!!! At first I was told this rarely happens (I had about 6 duplicates the first time), but today I ran another import and found 90 instances of duplicated names!
There are just over 600 records in the database. Many of these have had information that is unique to this database entered in a related table, so I need to be sure I don't lose any of that data. There is data in other tables that I could import again if I have to.
Here are my options as I see them:
1. Leave the primary key alone and delete the duplicated values with every update.
2. Create a new unique autonumber primary key, knowing I will have to delete relationships and rebuild them. I would leave the clientID field in the system as it is the only way we have to determine the difference between two children with the same names and birthdates.
3. Run quietly away hoping they don't notice the problem until I am long gone. ;)
What would you do in these circumstances? If you agree with my assessment that I really should create a new primary key, what steps would you take to minimize problems both during the process and in the future?
And yes, I wish about 12 months ago, I had asked if the social security number was ever updated, but of course no one thought to bring that up when we were discussing the database design! :mad:
I've a question that is teasing me. How would I create a multivalue/multiple answer field in access 2002. P'se forgive if this question has been asked before. Thanks in advance,
I have a form that has two text fields "Time From" and "Time To".
I can get the current time to show up with either =Date() or = Time(). Althought when I use Date() the time shows up correctly in the field until I click on the field to edit it and then it shows the Date instead of the time until I tab out o the field. But I want the user to be able to pick another time without having to type it in. Kind of like a list box but with a scroll that with either increase or decrease the time.
Also I am trying to get the "Time To" field to be 30 minute higher than the "Time From"
I have tried different things like DateAdd("n", 30, [TimeFrom]) and although it does add 30 minutes when I click in the field it shows the Date and time (3/6/2006 12:30:00 AM) and not just the Medium Time (12:30 AM) even though I have the Conrol and the table field formated to Medium Time.
A fellow employee of mine who is self taught in Microsoft Access currently has a database with a form that contains fields labeled name and address. She is trying to set it up so that when she begin to type, it will automatically look up to see if that name and address was previously entered to cut down on having to repeat it every time. She has tried the look up function with no success. She is using a single table as a base. Any assistance anyone can give us will be fantastic!! If I have posted this in the wrong forum or if this question was already answered, I could not locate it and I'm sorry if I am not following proper forum protocol. Thanks!!
I have been trying to incorporate an access DB in to our business for sometime, but keep coming up against the same issue. We have a numbering system for the jobs we do, which was invented many years ago and cannot be changed. It is in the format "month/Year/unique 3 digits" the 3 digits are sequential, i.e. 02/07/123 ... 02/07/124... etc. I will need this all in one field. So far I have managed to get the month and year (easy enough) with the expression "=Month(Now()) & "/" & Right(Year(Now()),2) & "/"" Now I need to get the sequential 3 digits at the end, however, I am not sure of any way to do this and it has stumped many people. I have a field that is autonumbered, but don't seem to be able to add this to the end of the expression.
If any of you have any suggestions I would be greatful - bear in mind I am only a begginer at this.
Here is my problem. I have created a dbase that takes a download of financial data on a four weekly basis to produce an report for my users. The data always comes across with the same numer of columns but at each four weekly period the date moves on. To show this data in a meaningful way I need to change the field heading automatically, based on the date in the data to show the current Period and Year. The dates given in the download cannot be used as the field names as they are not in the correct format.
I am writing a select query and need an extra column which will be calculated using two tables.
The problem i am having is that i need to to say basically, return value A if value from table x < value from table y or return value B if value from table x > value from table y.
I tried using the HAVING clause but you cant use the if function with this it seems.
I would like to create a series of help fields for various text boxes on a form to free up space that is currently displayed in several labels.
Does anyone have an example that will allow me to create several help field icons on a form. Clicking on each individual icon will display a help message for that particular icon? I would prefer to store the data on a table. If I write this, I will probably use a recordset and display the data in a label.
I just wondered if anyone else has any other ideas.
I was wondering if anyone had code to split a field by semicolums, to create multiple records.The field has the first line of address for each property with a particular postcode. For instance
field name: PRMF Craven House; Ample Mansion; WHSmith; Bulevard Terrace.
I want to be able to split fields like the one above with these properties by the semicolums so they become seperate records, but share the same other fields e.g. postcode, city...
still not sure how to split one field therby creating multiple records from it. For instance: split field: PRMF Craven House; Ample Mansion; WHSmith; Bulevard Terrace.
to
PRMF Craven House Ample Mansion WHSmith Bulevard Terrace.
I have made it a little clearer on the attached word doc.
The plan is to prompt the user to enter a postcode, then based on postcode an sql statement splits the fields of the field metioned with the criteria on a query. Any help very much appreciated, Thanks in advance.
I want to create a single search field inwhich the user can search data within two different tables. So for example in both tables there is a field called Container Number. When they type in the number into the search field i want a query to search both tables and return if it is in table one or table two.
I've attempted to attach a Word Doc that shows fields I've labeled (customer, date, application & so on). Is there a way to put all this into an Access Document, and create a database that I could search by customer, or by serial #? I've got about 400 of these, but it's all in folders on paper now, would be much easier to do it in an Access database.
I have a Comments field on a form. I would like to set it up so that multiple comments can be added and logged at different times by different users. I have attached a picture of what I am looking to do.
I have created a report using a query. Further to this, I have added some fields from the query into the report which calculates the sum of records and the count of records.
My problem here is, using the chart wizard, I am only able to select the fields existing in a 'Table' or a 'Query'.
Can anywone help me to create a Bar chart / Pie chart using the fields in the report? :confused:
Hello, i have a database that simply holds all the information on one table at the moment and what im trying to figure out is how to create a form that i can enter data onto that searches all the different fields at once, instead of one search for one thing and another for another. Some of the fileds are Headset number, gasket number, make, model, engine cc, etc.. so what i want to be able to do is have a form with a text box for each of these fields that then searches the table to narrow down the search results. at the moment i have a search for each, say for one i enter the headset number, it searches and pulls up all records with that headset number, then i have a search for engine cc, but what happens is it searches all records again for the engine cc not following on from the search for headset number so i end up back with a load of records with diferent headset numbers but with the searched engine cc. Basically the search form needs to take data entered into each text bow for each field then the combined results from each, narrowing down the results. can anyone help? in laymans terms!! Thanks Paul:
I have a query, and I want a field that shows the current balance as of that transaction, eg:__________________________________________________ _______|__Transaction ID__|__Type_______|__Amount__|__Balance__||__1______________|__Deposit_____|__£10______|__£1 0_______||__2______________|__Deposit_____|__£30______|__£4 0_______||__3______________|__Withdrawal__|__£15______|__£2 5_______|So far, I have this:SELECT [Transaction ID], [Type], [Amount], (SELECT Sum([Amount]) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[Transaction ID] <= tblTransactions.[Transaction ID]) AS BalanceFROM tblTransactionsWHERE [Account ID]=1;Although this does not look at the 'Type' field; it just adds the amounts; regardless of it being a deposit or withdrawal. I'm really not sure how to add this.Thanks in advance.
My form contains the field Bill_Date, which may or may not be input during data entry. What I need is to run a query (I think) that allows me to input a specific date that will automatically populate all Bill_Date fields that are currently null with the date specified. Eventually this will end up on the front end where a biller will click on a link when they open the db, input the date into the field, and then process another report.
I have absolutely no idea how to even begin and was not able to locate any specific information in Help or here on the forum.
I would like to know is there a way to create a mask on a form for a currency field? I don't want a user to be able to enter in like 125.145. I just want to make it so that people can only type in 125.14. Or how can I write VB code to give a warning when a user enter in more than two decimal places.
I would like to know is there a way to create a mask on a form for a currency field? I don't want a user to be able to enter in like 125.145. I just want to make it so that people can only type in 125.14. Or how can I write VB code to give a warning when a user enter in more than two decimal places.
I have created a form to search a field in my database by running a query. i can only get it to search for direct matches as I am having no luck getting the "LIKE" Operator to work for me. the format should apparently be, like "A*" , this works but i woud like to replace "A" with a variable from a txt box in my form. which will be a combination of letters and numbers
followed what i think is the right instructions and pasted the appropriate bits in the expression builder to tell it where to look but doesnt work cant fathom out why its not working, it just produces blank queries
Also in the query design page do i need to do anything with the optional "Total" row dropdown menu?
