Altering A Table
Nov 9, 2005
Hello, I use a database at work that I made. Unfortunatly, I did not add a field for numbering the items that a customer orders. When entering an order It is important to my customers to keep it in a certain order. how do I change it so it adds a numbering to all the past orders as well? I've been trying to think of a way for months and cannot do this on my own. Thank you to anyone who takes the time to help me out. If you have any questions I'll try to answer them the best I can.
View Replies
ADVERTISEMENT
Sep 19, 2013
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
View 4 Replies
View Related
Dec 13, 2005
I have a line chart built in Access. Is there anyway I can change the line colors like I can in Excel? The default fuschia, bright yellow, and bright blue are horrendous.
View 3 Replies
View Related
Dec 7, 2007
Hi
I'm trying to produce a database that will convert Ebay download files to a format more suitable for import into Sage Line 50. I have a table "Customers" that has all the customer information in it.
One of the fields is "Country", this lists the full name of the country the customer lives in. However, Sage requires countries to be listed as a two letter abbreviated identifier, GB, US etc.. I have another table "CountryCodes", with two fields, the countries full name and its abbreviated identifier. I need to be able to compare every "Country" field in the "Customers" table with with the "Country" field in the in the "CountryCode" table and change it's value accordingly. I also need the "Country" field to change to fixed value (ZZ) if the country is not found in the "CountryCodes" table.
Any help would be greatfully recieved.
View 1 Replies
View Related
Mar 12, 2008
I tried to revive an old post in the "General" section with this question, but it doesn't look like many people are looking at it. I figured it wouldn't upset the posting gods too much if I posted it here, since the the question is concerning queries.
I'm starting out with an excel sheet that looks like this:
Company Name..........Employee
Jones Electric............Kyle Brown
Jones Electric............Chris Ford
Jones Electric............Matt Daniels
Is there a way for access to make it look like this?
Company Name..........Employee..........Employee 2..........Employee 3
Jones Electric............Kyle Brown........Chris Ford............Matt Daniels
Thanks a lot, guys. I'll be sure to link the old post to this one.
View 6 Replies
View Related
Jan 9, 2006
Ok, so I have a form that I am using to apply certain parameters to a report. The form allows the user to specify one or more fields to sort by and then launches a Report with those statements in the Report's where clause using DoCmd.OpenReport. However, when certain fields are searched, I need to add information to the SELECT and FROM parts of my Report's query. I can't figure out how to do this with Access, if it is even possible. Otherwise, I will need to find a way around this.
Thanks for your help,
Alex
View 8 Replies
View Related
Oct 17, 2007
Let me start by saying I am very, very new to Access, but have been assigned a report to create.
I have the following textbox in my report:
=[Location]+(Chr(13) & Chr(10)) & [callnumber]+(Chr(13) & Chr(10)) & [Online Availability]+(Chr(13) & Chr(10)) & [PF Subject]
I'm using this format so that my 4 fields appear in a list and don't leave blank lines if the one of the fields happens to be empty.
The problem is that I need to alter or substitute some text.
First, in the location field, I can have several different entries, but if the entry is 'Circ', I don't want that text to display and I don't want a blank line either.
Secondly, in the online availability field, if there is any value in the field, I want whatever text that is to change to something like 'Online - Yes'. If there is no value in the field, nothing should display.
I think the use of the Iif function should work, but I can't seem to write it correctly. Help!!
View 5 Replies
View Related
May 22, 2015
I have a database containing a mixture of upper and lower case input. I can make upper case appear on the screen by using > in the format box in the property section for each field. This works great but the same old uncorrected stuff remains on the database. How can I get the database to store the corrected upper case data after I have entered it in lower case?
View 11 Replies
View Related
Mar 1, 2014
I am new to MS Access. So I am making a database for an automobile warehouse. When a new car arrives at the warehouse, it is allocated a specific parking slot (using a combo box available on the 'New Arriving Car' form).
However, once a specific slot has been allotted, the end-user should not be able to allot the same parking slot to other arriving cars, until the original car has left the warehouse so that the parking slot becomes available once again. Currently, the parking slot combo box shows all parking slots whereas it is supposed to display only unoccupied parking slots. How can I enforce this? (Note that there is a separate form called 'Exiting Car', which is filled when a car is leaving the warehouse).
Fyi, each car is referred to by a unique 17 digit code - so this is the primary key. Also, there are 120 parking slots available in all, with the slot identifiers ranging from A1, A2...A12 to J1, J2..J12 (10 x 12 = 120).
View 5 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Jun 28, 2013
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
View 3 Replies
View Related
Jan 29, 2015
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
View 9 Replies
View Related
Nov 4, 2014
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
View 6 Replies
View Related
Aug 5, 2014
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
View 2 Replies
View Related
Apr 19, 2013
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
View 2 Replies
View Related
Jan 13, 2014
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
View 1 Replies
View Related
May 28, 2015
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
View 8 Replies
View Related
Sep 12, 2014
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies
View Related
Aug 29, 2006
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
View 1 Replies
View Related
Nov 24, 2006
Ok my problem is this......
I have a form with a field "Property Status" on it. It has 4 possible values -
"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
View 1 Replies
View Related
Jan 21, 2005
Hi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
View 1 Replies
View Related
Nov 10, 2014
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
View 3 Replies
View Related
Aug 17, 2012
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
View 1 Replies
View Related
Feb 3, 2015
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
View 5 Replies
View Related
Jan 22, 2015
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.
View 2 Replies
View Related
Dec 16, 2013
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
View 1 Replies
View Related
