Is It Possible Please To Extract Table Structure And Data?
Mar 25, 2006
Hello,
Is it possible please to extract a tables structure and data into SQL in Access. I know how to do this in phpmyadmin (an internet based DBMS) but not so sure in Access.
What I want to do is to create an identical version of the table in SQL Server, so therefore require the structure and data in SQL format if possible.
I want to write a simple routine to enter a value into a text box, retrieve a record from a table using the value entered as a key (or display a message if it doesn't exist) and then populate a form with the other fields from that record.
So if I entered code 123 it would find the record where code = 123 and display the associated description and price for code 123.
I've created the table and the form but am struggling to do the bit that retrieves the data ! Help!
Through searching, I have seen a lot of post regarding 3rd party downloads to extract pdf data to a table, any coding or a sample database that doesnt require 3rd software?
In the end, I want to import the pdf data to the table, then attach the pdf file automatically. URL....I have a pdf form that was developed in Adobe Live Cycle. For sake of example, lets say my form object names are:
I got two tables while one table contains (sales data) and another one contains (criteria). I would like to extract sales data based on the criteria tables and export to a new table.
Which method is the best to complete this?
Criteria contains many lines like this
CustomerID, ProductID & InvoiceDt A, Guliter, 2007/10/5-2007/11/7 B, Piano, 2006/7/1-2006/12/31
I am a new user to access. I would like to extract a table from a URL, it is 4 fields and has around 150 records. One of the fields is made up of png files, relatively small ones. Is it possible to extract all the text and png files straight into access without individually saving each picture and attaching it to a record?
Is there a way I can take each entry in a memo field and put it into a text field in a separate table. The database is getting really big and the customer notes field for each record has lots of entries. The memo field looks like this:
I want to take each line and put put the date in a date field and the text in a text field in a separate table linked by CustID. Is there a way to do that?
I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?
hi everybody, great resource you have here! my employer is tracking around 250 pieces of data for each client at our facility. i am making a new access system based on their existing mysql database and web front end. before i start messing around with forms and reports, i want to see how well this existing structure will work in access, and what kind of approach i should take. i am a newbie with access, but lots of experience with asp/mssql/php/mysql. making web forms is so time consuming that i figured i would be best off moving the whole thing to access and starting from scratch.
client information is stored in eight tables. each table has around 30 fields in it. the first table has a primary key autonumber, and the other seven tables have foreign keys with unique constraints that point back to the first table. that is, for each client record in the first table, there can only be exactly one corresponding record in the other tables.
i did some data massaging, and got the eight client tables into one big table, but the resultant table has almost 250 fields in it, and access doesn't seem to like working with tables that big. so i am thinking that it is best to leave the eight tables separate, but linked in one to one relationships.
i was kind of ideally visualizing a form with eight tabs so that i could edit/update all of the information from the eight tables rather seamlessly.
my question is: what approach to table structure will best suit my needs, and what approach should i take to add/update/delete the info with forms? will i need to do vb for this? any good one-to-one example databases anybody could point me at?
I have a database that tracks emissions from painting. Bear with me since this is going to be a long post. :o
Some background info. - a paint can consists of many parts mixed in a specific ratio. - a part cosists of many chemicals - a part may be used is many different paints
Here is how I have the existing database structured now. I’ve simplified it somewhat.
PK = Primary Key (Autonumber) FK = Foreign Key (Autonumber)
The Density or VOC Content (VOC = Volatile Organic Compound) for a paint can either be given OR it can be calculated by the mix ratio of parts and their respective Density or VOC Content values. One or the other must be complete.
What I did not account for was that there may be changes due to the paint manufacturer revising their paint composition, such as; the parts that make up a paint may change chemical make-up of a part changes (can be a change in Weight Percentages or the addition or deletion of a chemical). ratio in which parts are mixed for a paint changes Density/VOC Content values may change for a Paint or Part
The problem is that I cannot simply change the existing records as the emissions are calculated using all the data from each table and emissions need to be calculated using the paint/part/ratio/chemical weight percent info that was valid at the time of usage.
Another thing is that the Paint Name will not change, it’ll always be something like “BrandX Acrylic Blue”.
The person entering usage data only knows how much of what paint was used for a given day.
The person who enters paint usage has nothing to with entering the chemical make-up for parts and information for the paints and vice versa.
At any rate, my new draft table design is as follows. Two of the tables (tblChemical & tblUsage) will remain the same.
tblPaint PaintID (PK) PaintName - String
tblPaintVersion PaintVersionID (PK) PaintID (FK) PaintDensity - Double PaintVOCContent - Double PaintVersionDateIN - Date PaintVersionDateOUT - Date
tblPart PartID (PK) PartName - String
tblPartVersion PartVersionID (PK) PartID (FK) PartDensity - Double PartVOCContent - Double PartVersionDateIN - Date PartVersionDateOUT - Date
I might be able to do away with tblRatioVersion and just have one table to store the mix ratios. It should be the case that a change in mix ratios (either a change in mix ratios and/or what parts make up a paint) means a change in the Paint Density & VOC Content. But I am presenting both versions of the Ratio tables here for completeness.
Version 1 tblRatioVersion RatioVersionID (PK) PaintVersionID (FK) RatioVersionDateIN - Date RatioVersionDateOUT - Date
tblRatio RatioID (PK) RatioVersionID (FK) PartVersionID (FK) Ratio - Integer
Version 2 tblRatio RatioID (PK) PaintVersionID (FK) PartVersionID (FK) RatioVersionDateIN - Date RatioVersionDateOUT - Date Ratio - Integer
I plan on having the DateOUT fields be populated automatically to match the DateIN for the new version. That way I can use “BETWEEN DateIN and DateOUT” to select the appropriate info for calculating emissions. The idea came from an old thread I started (http://www.access-programmers.co.uk/forums/showthread.php?t=31677&highlight=historical+data). I think this is the way to go, but with all the relationships going on, I'm having a hard time wrapping my head around it all. Am hoping someone here can help me with this.
Anyone see any problems with the new table design? Anyone know a better way? :confused:
Some potential issues that I see If only the Density/VOC Content changes for a Paint, then the old set of records in tblRatio must be duplicated. If only the Density/VOC Content changes for a Part, then the old set of records in tblRatio & tblChemicalWt must be duplicated.
Thanks for reading this post all the way to the end! :D
EDIT: Thought about it some more. A new version of a Part, should trigger a new version of Mix Ratios which in turn should trigger a new version of a paint. Part --> Ratio --> Paint Ratio --> Paint
Also, a change in a Part must trigger a New Paint version for ALL Paints that currently use it! :eek:
Currently I have a remark field and want to just extract the amount. The data input is not in a structured manner therefore I could not use the mid function.
Example :
Remark CASH PURCHASE $50K.DEBIT 3007484701 SUB $20,000 FIRST STATE DIVIDEND AMT:$10,900(FS BRIDGE FD)NO DISC
I have a very simple question that I have not find the result or maybe I do not know what to look for! I have a query containing a field named year and one named sales, what I need is to have a single result of total sales per year. example year sales 2005 100 2005 100 2006 80 2006 70 2006 60
The result I would like to put then in a form is: total 2005 200 total 2006 210
All this by simply running a query without indicating any parameters.
OK, so i have finally got round to re-building my client's database as suggested several times by RV.
I have three main tables that I need to extract data from - tblMembers, tblModules, tblCompleteModules.
Basically its a database containing the training details of Scout Association leaders. Each Leader needs to complete 26 modules over a period of time.
tblCompleteModules contains the data for the modules that have been completed, MemberID, ModuleID Date Completed and CompletedID.
I know how I can extraxt the modules that members have completed, but how can i extract the data for the modules that have not been completed. For example I need to report on the number of people who haven't completed module number 20 for example.
I have a word doc from which I want to import data in a Database. The word doc has a table, something like this :
Projectnumber 10700004 LSname LSTK number one POnumber 1170-LST-04 Contactname Mr. Karel van Straten Vendorname Mothercompany name S.A. Manufacturer Operating Company name Otherdata Others Mancity 46100 Hamburg Mancountry Germany Manorder 31-0111-009 Mancontact Mrs. Angela Duval Mantel 0049 2323 209 151 Manmail email@testing.de
At this stage I copy the table data and paste it into a memo field in a Dbase Query and try to manipulate the text in that query to get some of the data segregated , i.e Projectnumber : 10700004 , Mancountry : Germany , etc. However, this doesn’t work as I want.
e.g. I’m trying to get the Manmail text thru this qry : Manmail: Mid([Memofield],Len([Memofield])-InStr([Memofield]," ")) , but the output is not consistent.
Would there be a better way to get the original ( doc-table) data in the same way in my database.
Note : I could use an additional step : copy/paste to Excel and then import to Access, but that’s something I would like to avoid.
I am trying to extract data from one field and then put it into another field. I have 2 fields, Code_A and Code_B. I am trying to take the Code * data from Code_A and put it in Code_B. Any help is appreciated.
I have imported several Excel files into Access to create tables in the database.
I teach online and basically I need to know how to extract certain bits of data from each table and put them together.
For example:
Table #1 is my student roster list and contains the fields: Firstname, LastName, SchoolName, and several other fields.
Table #2 is a list of schools throughout the state with fields such as: SchoolName, Registrar, ContactPerson, and so on.
There are several other tables involved but I’m trying to make this question as simple as possible and if I can get this question answered, I think I may be able to figure out the rest.
I would like to print a report out for each individual student that will include the school name from Table #1 and match it with the same school name in Table #2 and then extract the pertinent school information from Table #2 for that school.
I have more than one table with a list of schools. Should I name each field that pertains to the school name with a unique name?
To clarify….
Table#1 can have the same school name listed any number of times because some of the students attend the same school.
Table #2… Each unique school name will be listed only once.
These Excel files come to me regularly as they are updated and I am trying to find an easy way to extract the data that I need.
Can someone please tell me how to write this query?
Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.
i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.
I have this database that do contains duplicate record (not duplicate field).What I'm trying to do here is to extract record that is unique to a new table And those record that were duplicate into a new table.the *Distinct that we used in sql will not work because it is not what I wanted. using distinc will give me unique record of the whole database.say i have this database that contain an ID:
2525 2658 2658 2658 2525 3678
so what i want is to extract unique record to a unique table and those that contain duplicate in another table.so the 3678 will be put in a new table and the 2525 and 2658 in a new table,,That way I will see which file will have duplicate
I need to break up part of the value in one of my fields. I can do it in Excel by identifying the position of the dash with eg. =FIND("-",D13) And then using it in a LEFT function. Or using the text to Columns wizard.
How can I do it in a query, there is no find function. If there is an equivelant what is it?
I'm trying to extract invoice and payment transaction data from a very old version of quickbooks. This very old version allows for the export of customer and item lists to .iif format which I can view in Excel. However, there is no method of exporting transactions. All of my searches for how to extract the data have come up empty.
I am pretty sure that I once found a post relating to looping through the file line by line and converting the data using vba. I can't find that post anywhere either.
Any method of extracting data from a quickbook .qbw file. I'm willing to do a fair amount of manual parsing if I have to, but I need to get to the data first.
Not sure if this is possible but I am trying to extract data from a word document to set up a database.
Basically I am trying to capture data from completed forms, similar to the sample attached, the actual blank form is 20 pages long and once completed can be as many as 30 pages, or even more, although the format doesnt change (i.e. individidual cells will expand to fit the data in the cell).
I want to be able to scan through the completed forms and extract the data i.e. Full Study Title, Short Study Title, Study Type etc. into a database.
The issues: 1. Each document will have a different name but will be stored in the same location. 2. What is on page 8, for example, in one document is not necessarity going to be the same on every document (due to expanding cells) 3. Each sector is in a separate table but tables can spread across several pages. 4. Some data is stored in a checkbox format rather than text. 5. I dont want to extract all of the data, only certain sections (at least at this stage).
I am pretty new to programming with access but i am trying to create a database system with history logs and i am stuck on how to structure it.
I'll explain a little more, i am trying to setup an application so that if somebody brings an item into myself to purchase i can book it into the system so that it records the customer that it came in with and also the item details. I will then need to resell this product onto another customer, the problem i am having is that once the item has been purchased and sold on i will then need to keep a record of this so that if the customer comes back with the same item he has purchased to resell back to us we can keep all the details in history so that each product that is purchased and re-sold has all its past history from every customer that has purchased it and sold it back to me.
Where the word Author= appears to extract the data between the double quotes, so in the above case I want to extract "All Saints" excluding the double quotes.And then where Title= appears extract "On & On", again excluding the double quotes.So I would end up with data in my table looking something like this
strArtist [Author] strSong [Title] strGenre [Genre] strYear [Year] All Saints On & On Pop America Venture Highway Rock 1972 Amillionsons Misty Blue Pop 2002
I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).
Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.
I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.