I have a make table query that deletes the old table every time the new one is made. However every time the new table is made it resets the field property for the “Date” field to “Text” rather than “Date/Time”
Does anyone know how I can avoid this problem please?
I’ve tried an update query but Access won’t allow me to perform the sum function that I require the actual query to perform. Any suggestions would be very much appreciated.
I have created quite a substantial and effective database for a small gliding club with all the major data tables being linked. I need to be able to re-index tables periodically so that running totals, which are needed to calculate statement balances, works correctly - all well and good. The only problem is that the process of re-indexing requires the data in the main table to be stored temporarily and the original data deleted. When the temporary data is appended to the main table the auto-numbering just keeps clocking up.how to reset the auto-numbering in a local table.
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
I'm creating a database that keeps a track of questions and scores.
The questions in the database need to be dynamic and are changed frequently.
I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.
I want the question field in my scorecard table to populate with the value in my question table.
I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:
SELECT tblQ1.Q1 FROM tblQ1;
This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I have a make table query created from a linked to a table. i want to add field to the make table query that are not fields on the table. How do i insert a created field? Hopefully this is a basic question.....
I am trying to create a make-table query, with a new AutoNumber field.
I know that if you are creating a new Text field you type FieldName: "" in Field and for a Number field you would type FieldName: [], but what do you type for an AutoNumber field?
DB Setup: Table1: I have a table (Vendor) that has 2 fields (# & Name) with # being an AutoNumber. So only Name is being input via a form. I have formatted the autonumber field as 000;(000). Table2: A table that is populated via form with invoice info etc and vendor number is added through a drop down combo box (which also has the above format on it) Table3: Similar to table2, with slightly diff info but still vendor #
Query1: Is a make table that consolidates table 1 & 2 via union on like fields (ie vendor #) This make table also has the format from above in its properties field, although when i open the table it makes (Table4) the vendor field is not formatted as i need it. So 3 appears as 3 not 003.
Query2: takes table4 adds some extra info and exports file (as txt or xls)using outputTo & TransferText macro so that it can be loaded into a Hyperion Essbase system
My problem is that although the field value is formatted as 003 in appearance, when i take it to excel it changes back to 3 when i need it to stay as 003. I would like the make table query to also format the tables field as 000. Is the problem with the autonumber in the orig table or is it simply excel being stubborn when i take it there. If i changed the vendor field to text string in the make table would i still be able to link it back to the orig vendor table to get the names etc (ie number field linked to text field??) Thanks in advance
Hi all, stumped on an issue I thought would be pretty straight forwards (again) so i'm hoping to find the answer here! I have a make table query which produces the table using fields from three other tables and it runs fine however I now want to insert a new field with the value "Current" in it, and, if possible, an Autonumebr ID field as primary key. I'd like this is all done in one query so not sure if it will fly but if anyone can advise me I know someone from here can help.
I have numerous databases that I use with make tables in there, these will often contain Currency values that we need to be set as just General Numbers. We can get it to work in the Query but whenever we run the query, the table it makes always shows up as currency.
Is there a way so that the table created will automatically be just General numbers...
I am extracting information from a datawarehouse with a make table query. I will run this queary one time to create the table. Then I plan on creating an append queary to extract the same fields on a weekly basis. I want to keep track of the datasets that i extract weekly. I thought I could create a field with a julian date or the week of the year. Does anyone know how I could create this field in my append queary? OR any thoughts on how i can keep track of the records appended each week?
I have a colleague who executes a make-table query that reads a txt file. While doind this, he wants to convert two columns (binary format) to checkboxes (Yes/No format). Is there a way to do that?
I can add to my make table query UDate: Date() that will add a populated field to the output table that has today's date in it.
I can also add something similar like this for example V_Num: [V_Number] and when ran, will prompt with a dialogue box to add some data, it will then create the table with that new field name and populated with the text from the dialogue box.
But how can I automate this to skip the dialogue box and just add it to the expression?
In Access, when using Look Up function, Table/Query, how do I make more than 1 field from the query appear in the drop down list?I will be doing the same thing for students and some other tables. But for this example I am only wanting to show instructors first and last name and their ID number in the drop down.
I am using
Code: SELECT instructors.first, instructors.last, instructors.ID FROM instructors; in my Classes table.
What am I doing wrong?
I would like for it to show their instructor first and last name but actually put the ID in the field as I will be using that key for various relationship based functions. Show the first and last name to the person who will be using the database, but actually put the ID in the field so I can use it as a common field across other tables.
I'd like to know how to add a blank filed (memo), called "comments" that is not pulled in from any existing table, in an existing maketable query which is getting it's fields from different linked tables , so that the destination table that comes out of this query, has all those data along with an extra column for me to add comments.
I have a table called Locations that lists Countries and Cities:
USA, Detroit USA, Chicago USA, New York UK, London UK, Liverpool UK, Birmingham France, Paris France, Le Mans
I then have another table for inputing details on people I know. This table would have columns called Country and City. I've figured out how to make the Country column a "lookup column" so that I can only enter USA, UK or France. But how do I make the City column show only the appropriate list of cities relevant to the country that I've selected (e.g. Paris or Le Mans if France is the selected country).
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?
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?
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code: Dim strFile As String Dim temp As String Dim tbl As String Dim db As DAO.Database
Hi, I have a problem, I have a table were I list networkservices and their logical ports and I have another table were I list IPaddresses used by different machines. I'm using a multivalued lookupfield to pick which services I'm using for each Ipaddress, and thats working fine, but now I want to make the ports which is listed in another column, to automatically show in it's own column in the IPaddresses table depending on which services I'm choosing for the different IPaddresses. Is this possible?, and if it is how can I make this happen?
I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).
For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.
