Stock Symbol Table And Look Up Data Type In Related Table
Jul 10, 2012
I previously created 2 tables:
One lists all the stock symbols and company names = SYMBOL
The second table lists the purchase information for each stock = PURCHASE
I then created many queries, etc. using this data. Symbol is the key link between the various tables, queries, etc.
NOW that I understand the lookup wizard in the data type, I would like to change the symbol field in the purchase table to a Lookup field. I, of course, receive a message. I am told to delete the relationship with the other tables. If I remove the relationships and change the data type, can I then replace the relationship with out damaging all the queries and forms?
View Replies
ADVERTISEMENT
Mar 23, 2008
Hey guys,
OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:
If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.
My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so
Any help?
Thanks a lot in advance!
Daniel
View 4 Replies
View Related
Nov 1, 2013
I am tying to query the Symbol table to see if a recod exists with symbol code.
I am querying the tblSymbol table from Excelk vba and the Access DB is on my machine.
The code I am using is:
Set rs = New ADODB.Recordset
rs.Open "tblSymbol", cn, adOpenKeyset, adLockOptimistic, adCmdTable
Set rs2 = New ADODB.Recordset
sql = "SELECT * FROM [tblSymbol] WHERE [SymbolCode] = """ & someSymbol & """"
rs2.Open sql, cn, adOpenDynamic, adLockOptimistic
[Code] ....
MsgBox Exists always returns -1 but i know the code exists in the table.
View 5 Replies
View Related
Sep 5, 2006
Hello,
I have a form based on 1 table and I am trying to find out if I can display data from another table if I have a relationship between the 2 tables?
Thanks
View 1 Replies
View Related
Oct 18, 2011
I am new to access 2010. I need to change the primary key in a table ("stations", Primary key is "station number"), because future entries will result in double entries for this primary key.
I have another table ("species&stations") which has records of Species for each "station number".
So I created a new Primary key for the "stations" table, now called "species id", being a bit better in defining a unique station (as it contains the year and the species number, i.e. "451_2010")
Now the table "species&stations" references/(is in relationship with) the old primary key, identifying a station number for each record.
How do I get it to recognise the new primary key from the "stations" table, and keeping the records (over 1000...) with all there info.
Approach so far: I tried to create a new field in "species&stations", which should look up the new primary key from "stations" and then autofill the field. i did not manage this though
I attached a screenshot to make the relationships more clear!
View 2 Replies
View Related
Aug 19, 2013
I have a form based on a table which includes the mid field. I want to have a macro that takes the value of the current mid, and makes a new record in a 1-many related table (consisting of record id (auto), mid and trmntdate), paste the mid and insert the current date.For the life of me I cannot get it to work? The process should be something like:copy mid value, add new record to related table, paste value in mid, insert current date in trmntdate, save. I've tried append queries, experimented with copy etc, dabbled blindly with VBA and not got anywhere.
View 14 Replies
View Related
Mar 26, 2014
I would like to create a report that would really impress my supervisors, i just started at the company. I'm trying to create a call action plan, so i'm recording clients information on one table, and meetings we have had with each respective client on another table. Some clients will have multiple meetings, some few, and some none. I have a relationship set between them from the client's id number on the client table to the ClientID on the meetings table. one to many.
When I go to create the report, only the clients with meetings show up on the report, I would like client info to always show up on the report and meeting info to only show up under each respective client when it exists. I have worked out how to shrink and hide any text box without any info on it. It just seems like the existence of a meeting dictates where the client will show up at all in the report.
View 1 Replies
View Related
Apr 15, 2013
what I want to do is make a button to search range of columns in data table with data type Yes/no and display the results if the value is yes
View 9 Replies
View Related
Mar 5, 2007
Hi,
Trying to get back into Access after some time now and could do with some help please...
I have 2 tables, one table stores titles (mr, mrs, etc). I tried using the 'lookup' data type to link the data from the other table.
This however stores the field as a number and causes problems as only a number is shown rather that the text (in the tables).
How can I set it so Mr, Mrs, Miss can be displayed in a drop down list, rather than the Primary Number (ie Auto number).
Make any sense?
TIA
View 1 Replies
View Related
Aug 7, 2014
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
View 2 Replies
View Related
Jul 17, 2007
Is it possible to set the yes/no data type fields in an Access table to behave like radio buttons? In other words, if they click yes in one data field, it automatically prevents them from clicking yes in the next data field. If so, how?
View 4 Replies
View Related
Mar 16, 2013
I would like to work with a field "record number" like: "ABC01-01-2013A1".Standard data types do not allow for this possibility.I would also like the date updated itself automatically and the number incremented A1, A2 until A9 then B1
View 6 Replies
View Related
Sep 27, 2011
My database consists of one large table of approx 3.9m rows and about 12 different columns. One of my columns is of a data type "date/time" but i need the format of this column to be "text"
The problem is that when I right click the table/go to design view/ and try to change the data type, I get the error "Microsoft Access can't change the data type. There isn't enough disk space or memory."
Which means i can't take the easy way out. Is there a way I can just create a new identical table and change the data type of my one column? I was thinking there might be something in SQL I could do, or some how in creating an identical table where only the field type has changed. I should note, I looked up this using the access in file, and it appears the ALTER TABLE SQL will not work on my machine.
the dataset was created from a txt file which as been lost to the ether, and I can't import the database into excel using a macro I didn't write unless the format of my column is text.
View 3 Replies
View Related
Jul 6, 2005
I am trying to change a field that currently has a data type of number and I want to change it to a date/time data type. When I try to do this, I get a message that says there isn't enough disk space or memory. I am working with a database that has about 100,000 records in it.
View 1 Replies
View Related
Jun 18, 2014
I am creating a table that is a master list of all of my company's product. Each "customer" that we have will always be ordering the same items, but not all of the items that we have available. I need a way to go through the master list and click a yes or no and have that item added to the "customer's list of items on a new table.
I need to create a sublist for each "customer" like individual shopping cats for each customer. These individual lists need to link back to the master list in case of product changes, description changes, and cost changes.
I would like to create a form where the end user can type in a product number, description, or manufacturer number and have that item added to the "customer's" list.
View 3 Replies
View Related
Apr 24, 2014
i wanna to insert a new field in the table ... which fill automatically with the date in which i modified the data in this record ...and then i'll insert this field in a report
View 1 Replies
View Related
Aug 14, 2014
See attached the Workbook. I need to check the policy Numbers in Column A of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.
In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.
View 1 Replies
View Related
Feb 9, 2005
I was wondering if there is a way to export the Table Design Structure:
1) Field Name
2) Data Type
3) Description
Into a Spreadsheet.
I was able to do this a long time ago, but can't seem to remember. I'm using Access 2000.
Thanks!
Joe
View 3 Replies
View Related
Jul 18, 2013
I am trying to change the data type of a field in a table from calculated to something else. It gives me the error "this data type cannot be changed once the field has been saved"
Is there any work around to this?
View 2 Replies
View Related
Jul 17, 2014
I created a table in MS Access using a Lookup & Relationship data-type. This means that my record cells call upon a particular table for values. It creates a LIST of values for that cell.When I query a particular value in this table I get every possible combination of the value. My query and code are below:
OR,
SELECT MainT.Content.Value, MainT.Source, MainT.[Entities Impacted].Value, MainT.Update,
MainT.[Divisions Impacted].Value, MainT.Announced, MainT.Effective, MainT.Stakeholders.Value,
MainT.[Ref#ID], MainT.[Issuing Agencies].Value
FROM MainT
WHERE (((MainT.Content.Value) Like "*" & [Forms]![SearchF]![ContentCB] & "*" Or (MainT.Content.Value) Is Null)
[Code] .....
This means that if I query STATES: "California", I'll get back every possible combination that exists with the name California in that particular field.The issue with this is that I'll get MULTIPLE primary key values rather than just 1. So, if one record, under STATES has California in it, but the other fields in that record have the Lookup & Relationship data-type, then every possible combination of that record will query instead of the multiple field-values form that you'll see in the table I am querying.
notice that my primary key (REF#ID) is repeated numerous times! This obviously causes problems with generating records and forms concerning information for 1 particular primary key. Is there a way around this problem other than specifying search criteria down as much as possible?! Perhaps there is some SQL code.
View 1 Replies
View Related
May 5, 2013
I have a table TO-det and another table DO-DET.The table DO-det will have details about all DO for each TOID record.Both have a common field name TOID The tables are related under ONE-MANY relationship.One TO-DET record can have many DO-DET record
Now I wanted to create a form where if i add a new record to TOID i must also be able to add data for DO-DET for that corresponding TOID.
View 1 Replies
View Related
Aug 29, 2005
I'm using MS access and Excel 2000. I have an Excel spreadsheet that contained 8 columns, the first column has all cell format as Number, the rest of the column is set as custom date format of 'dd/mm/yyyy'. When I create a linked table in MS Access, the data types does not matched my excel spreadsheet columns, the 'Number' data type is a double and I want a Long Integer in Access, and the custom date format become text datatype but I wanted a DateTime datatype. Is there any work around this? Seems like it is a common problem.
Your prompt response is greatly appreciated!
Thanks in advance!
Martina
View 1 Replies
View Related
Sep 25, 2013
I created 2 table that are linked to a link table so to get a many to many relationship.I have a table for locations and a table for parts.One location can have many parts and One part can belong to many locations, that is way the many to many relationship.
how to get values from the parts table to the Location table using the linked Primary Key in the foreign key linked field.The values also have to be interactive, if I change the value in the part table it needs to update in the location table.
What can I put in the field of the location table to get a specific value from the parts table?
I tried setting the field to calculated field and put the Dlookup function but that didn't work. The lookup wizard seems to be the solution, but I can only create combobox or list box and I need to select the value manually. I just need a value to be put automatically by looking at the foreign key.
If I put the Dlookup function on the default value, those it update if a value is changed in the part table?
View 3 Replies
View Related
May 16, 2007
I have two tables.
The ITEM RECD table containing the fields -- recd_invoice_ no, invoice_date,part_no,qty.
The ITEM ISSUE table containing fields- issue_no, date, part_no,qty.
The two tables are related by the part_no field.
How do I print a stock book type report for each item individually with all the recd item details on left side and the issued item details on the right side? All the recd & issue details for all dates need to be there in the report.
Someone please help!!!!!
View 3 Replies
View Related
May 26, 2007
I have created a report in MsAccess for a typical stock ledger page as is shown below:
ITEM MODEL: XYZ
TRANS <------- RECEIPT -----> <----- SALES ----> BAL
DATE INV No INV Dt QTY INV NO INV Dt QTY QTY
25/05/2007 0278 27/03/2007 10 ------ ------ ---- 10
25/05/2007 ------ ----- ---- 2007/0001 25/05/2007 4 6
26/05/2007 ------ ------ ---- 2007/0002 25/05/2007 6 0
26/05/2007 0291 12/05/2007 27 ------ ------ ----- 27
I wish to print the stock ledger from one transaction date to another with the last balance qty appearing as the first row with the “brought forward” message. The report would look like this:
TRANS <------- RECEIPT -----> <----- SALES ----> BAL
DATE INV No INV Dt QTY INV NO INV Dt QTY QTY
Brought forward 6
26/05/2007 ------ ------ ---- 2007/0002 25/05/2007 6 0
26/05/2007 0291 12/05/2007 27 ------ ------ ----- 27
Require expert advice & help.
View 1 Replies
View Related
Jan 14, 2008
okay, last question. I promise. You all have been so helpful, though...
I have been given the assignment of splitting down a table into two more tables(three in total). My question is this: Could I use the Table analyzer? If so, what are the shortcomings? I would like to have the other tables referenced by the key of the original table instead of a lookup field, whioch I'm not sure if I can do in the analyzer. What attracts me to the analyzer is that it would make the needed queries to join the new tables. If I don't use the analyzer, would I use a make table query to accomplish it? If so, would I have to change every existing query in the database that used that old table?
I know it's a lot, but any help in pointing me in the right direction would be much appreciated
View 1 Replies
View Related
