Preserve Order Of Items In Table Without ID Or Primary Key?
Nov 10, 2012
This is a CSV file that comes through an Access specification. I need to be able to copy the items from the table to other tables but somehow apply a unique and sequential ID to the rows first, this is essential to the job. The text import spec applies a unique ID but save the spec, and call it using VBA, it doesn't do that.
Also, there seems to be a bug where using a macro to run the text import specification, (and this is after an update from Microsoft), that it doesn't recognize the specs anymore... after I deleted several of them.
We have a lookup table that has a list of CLIN numbers and their costs. The contract that governs those CLIN numbers and costs will be changing to entirely new numbers. Unfortunately, I still need to have the old and new CLIN numbers linked to the other tables.
Will I need to merge all the CLIN numbers into one lookup table, or can I do it from two lookup tables?
I'm building an order entry database. It has two tables (amongst others) called tblOrders and tblOrderDetails, related together on the OrderID field. For every order, there is one record in tblOrder, and as many records in tblOrderDetails as there are individual lines in the order (so, if, for example, the order is for 10 pencils and 2 pens, then tblOrderDetails has 2 records).
Each entry in tblOrderDetails has a Status field, which indcates whether or not the items have are in manufacture, shipped, delivered, etc).
I need a way to get Access to show me only "Open" orders (i.e. ones in which not every item has been shipped). Can I set up a query to determine how many lines each order consists of, and then is there a way to get access to check if all of these are "Shipped"?
I am trying to find out if there is a way to track the order in which items are selected from a list. I am a dabbler and any keyword searches that I can think of don't bring up what I am looking. So, here is what I am trying to do:
I have a table of symptoms with 3 fields (ID, Category, and Symptom).I have a combo box that will allows the user to pick a category (using select Distinct on category field). I then have a list box populated with all the symptoms that have a category of whatever the user selected. The problem I am having is that I need to somehow track the order in which the user selects symptoms and then save that order for future reference and to be printed on a report. The order is important because the most severe symptom needs to be listed first.
I have a report that generates 100 items in alphabetical order. All of the items are also displayed in a table. I have a bound form attached to the that table, and when the items appear they are not in alphabetical order. Therefore, it is a hassle typing in the data when the form will not appear in order, because instead of going in order I am flipping through pages. What is causing this to happen? And what can I do to fix this?
I am currently a SQL DBA, and have been roped in to help a fellow staff member import some XML data into an access database. I am using Office 2003, and the Get External Data command. It successfully imports all of the data, but my colleague says that it is not acceptible because the data is being converted to UTF-8 encoding. He would like it to retain the same original encoding as the XML file. Is there any way I can do this?
So I have a company where the bonus amount for a calculation can change quarterly - if a person accomplishes 50-100% of plan they get that % of their bonus amount.
I have that working on a variable detail DB where the historical data is correct for the report.
i.e. if I want to look at January - the report looks at the requested date: January and calculates using the bonus number from the last update made before January (year is also factored in)
So: January 2014 if they make 50% of plan and their bonus is $100 this month - they receive $50
Good - no problem
NOW: Every year the formula on the report Could Change - so next year if the person makes 50-100% of plan and 30% of secondary plan - they get 30%(% of Bonus)
So now: January 2015 if they make 30% of secondary plan and 50% of plan with $100 bonus the report would give .30*(.50*100) = 15
I can change the calculation on the report - BUT then how would I go back and accurately show what they got in January 2014
How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.
Say I have a tables of words. tblWords numWordID txtWord
Then I have some entries, all more or less synonyms of each others fresh new clean
Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".
Kind of a many to many relationship junction table but only with one table!
I hope my explanation have not been to confusing, but let me know if you need a clarification.
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...
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)?
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K and 5 pc set = A,B,C,D,F and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000 B=3000 C=3000 D=3000 F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
ok , i have a primary table that have a field of "job", then i create another table that have a field of "job" too , then how can i do this task?
Whatever i type a data into the primary table("Job" field) i want it to duplicate the data into the another table("Job" field) . is it possible to do this? can anyone guild me to do ?
Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!
I have made a few lookup tables in the database I'm working on using the lookup wizard and entering the text I want in them. I made a form and they come over fine on them. My question is that I thought that there was a way when you entered something other than was in that field it would automatically add it to that list and you could just select it next time instead of typing it in again. Is that possible or am I thinking of something else.
I'm creating a database for my job. I have 4 tables with information. When I need to deliver some items i must know which items to take, They are in those 4 tables.
I want to make it that i can click a menu on my switchboard that i see a report with all the information with a clickbox behind it. If i select this box and click a button generate it creates a report with all the items i've selected with the select box.
So in short:
1.) How do I create a table with all the items from the other tables that are automatic updated when insert a items in one of those 4 tables.
2.) How do I create a report where i can select some items that are generating a report with the selected items.
I have made a query to search for some items on a table. I want there to be a button on the search form that says 'search' and takes you to the corresponding page on the form. (The form has multiple pages in, all with different products on) Is this possible?
I have a form which when displayed shows all the items on the table (usually 1 or 2) for a particular key.
The following row is part filled in awaiting further input for the rest of the fields of that row.
I have a button (exit) which basically closes the form but it also validates the (potentially) partial field ultimate row. If the user hasn't added any data to the partially filled row then I delete that row with this code.....
If IsNull(Me.Field1) or IsNull(Me.field2) Then ....If Not Me.NewRecord Then ........ DoCmd.SetWarnings False ........ strSQL = "DELETE * FROM [Component Swaps] WHERE [ID] =" & [ID] ........DoCmd.RunSQL strSQL ........ Me.Refresh ........ DoCmd.SetWarnings Trye .... End If
.... DoCmd.Close .... Exit Sub End If
This works. I have copied the exact code into the close event for the form but it seems like in that part of the code the previous row is being picked up.
So for example if I have 2 records on the table I display 3 rows (as 1 is created with partial data) thus
If the code goes through the exit event then Me.Field1 and Me.Field2 are Null and the record with the ID=3 gets deleted.
Going through effectively the same code for the close event Me.Field1 = "F11" and Me.Field2 = "F22" and the record doesn't get deleted (ID=2 as well)...
I am accessing a SQL database using Access but its not letting me view the table in design so I can set a primary or letting me change the table names.
I am able to do this from my home PC but not at work. Is there something in options or ODBC settings on the work the PC that I need to change.
I'm still new in ADP development and need some advices and helps from you who has already expert on it. Fyi, I used Ms. Access 2007 and SQL Express 2005. I found a problem when working with my project. Here is the problems :
I'm using form wizard to create master/detail form but why I can't insert record in the detail section (the navigation button also became disable) ? The detail table doesn't have primary key only have foreign key that related to the master table. Should a table must have a primary key if we use ADP ? As attached is my relationship diagram.
I have a small database, that I would like to use a combo box to allow user to select a value based upon values already entered on table, and then if value is not found enable the user to enter a new value that will then become part of the selection for future record adds.
I have a combo box that inserts data into an unbound list box and table. This works great but I am having trouble with the deletion part. I want to be able to dbl click on the item in the item list and delete it from both the list box and table. Currently, my code is deleting ALL items, not just the one item I want to get rid of. Any ideas would be most appreciated :) This is what I have for the deletion code:
Private Sub List92_DblClick(Cancel As Integer) DoCmd.SetWarnings False sql2 = "delete from PROFILE_Industry where " sql2 = sql2 & "profile_id = " & Me!Profile_ID sql2 = sql2 & " And Industry_focus = '" & Me!List92.Value & "';" DoCmd.RunSQL (sql2) Me.Refresh DoCmd.SetWarnings True End Sub
I'm trying to add items received to my inventory table. If the item is already in the table, I just want to update the number and cost etc. If the item is not in the inventory table I want to add it. My problem is determining which item in the source table is already in the target table so I can either update of add. how to find an item number in target table by looping through the source table?
Here's the code I've written which doesn't work.
Dim I As Integer Dim db As Database Dim rs As Recordset Dim rs2 As Recordset Set rs = Nothing Set rs2 = Nothing Set db = CurrentDb
I did some BAD planning before I started creating my database and I set a text field ("field1") of a table ("table1") as primary key, then I linked this field to a field in another table ("table2", one-to-many relationship). Now I wan't to change some values in table1 in "field1" defined as primary, but I can't (Access refuses to do so...).
Because I have a lot of data entered allready I would like to know if there is any way for me to change values in "table1"/"field1" and that all the linked fields in other tables ("table2") would change correspondingly??????
