General :: Organizing Import Data For Display?
Jul 7, 2014
I am receiving this file from a supplier to import as a table. The issue is I cant figure out a good way to display the info for each invoice because there are multiple rows corresponding to each invoice number. I wanted to build some type of form where I could search an invoice number and it would pull up the standard invoice format to print such as inv.#, mailing address, linedesc amount and costs of shipping each item. I cant figure out how to get the invoices that have multiple line items to display in a nice format though.,,
View Replies
ADVERTISEMENT
May 11, 2015
I have a query that organizes a drop down A-Z. I am looking for a way to organize it A-Z AND Smallest to Largest.
Code:
SELECT tblPartNum.PartNumberID, PartNum & ' - ' & PartDesc AS Expr, tblPartNum.Deleted FROM tblPartNum WHERE (((tblPartNum.Deleted)=False)) ORDER BY PartNum & ' - ' & PartDesc;
I need it to sort it out like this
4 digit #'s Ascending
5 digit #'s Ascending
6 digit #'s Ascending
So the list would look like this
1111
1112
1113
11111
11112
11113
111112
111113
111114
View 6 Replies
View Related
Mar 19, 2013
I would like to Create a database for indexing / organizing PDF documents. My Company currently use a 3rd party accounts program and we raise sales and purchases using numerical numbers ( but we can Jump to a new range of numbers within the program) for example if the purchase order numbers become simliar to the sales order numbers. But this 3rd party software does not let us scan documents into it hence the need for an alternative database.
For the data base I was thinking if only creating a index of the sales order numbers to start with. the first few question I have are.
1.) Should I Use the Autonumber as my first index column or should I replace it with our own sales order numbers in that column.
2.) is there a way to autopopulate the sales order numbers in a table for historical sales numbers.
3.) can you scan directly into Access
How to start planning the design off the database as I know this is almost the most important aspect of a database.
View 2 Replies
View Related
Jan 11, 2012
I'm putting together a shipping database. We scan in a string of barcodes, separate out the Serial Numbers from the Barcode Data, concatenate them together as a string with asterisks as a delimiter and join them together with a date code and manufacturing line identifier. Throughout all of the data manipulations I am able to maintain the Serial Numbers in my table in the order which they were scanned in. The very last function is to concatenate the Serial Numbers and Lot information together into one long string which will then be output to a label creation software. And up to that point, the Serial Numbers are still in the correct order (based on them being linked to an autonumber field in the initial table) Upon the final concatenation, the order of the Serial Numbers is being changed, sometimes 17 out of 18 Serial Numbers will be correctly ordered with just one being randomly placed in the string out of order. Here is the code in SQL that I use for concatenating.
INSERT INTO tempMotorPalletLabel ( PalletLabel )
SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber&"*"&tempMotor s_to_Warehouse.Count&"*"&tempMotors_to_Warehouse.P artNumber + '*' + Replace(Replace(ConcatRelated("SerialNumber","temp Motors_to_Warehouse","PalletNumber='"&PalletNumber &"'"),' ',''),',','*')
FROM tempMotors_to_Warehouse;
And this SQL Query transfers the concatenated string to a table that my Label Software queries for data to create the label with.
INSERT INTO AccessMotorLabelData ( PalletNumber, [Count], PartNumber, PalletLabel )
SELECT DISTINCT tempMotors_to_Warehouse.PalletNumber, tempMotors_to_Warehouse.[Count], tempMotors_to_Warehouse.PartNumber, [tempMotorPalletLabel].PalletLabel
FROM tempMotors_to_Warehouse INNER JOIN tempMotorPalletLabel ON tempMotors_to_Warehouse.PalletLabel=[tempMotorPalletLabel].PalletLabel;
View 14 Replies
View Related
Feb 6, 2013
I am trying to import my data from a csv file, but I don't need all the data and the format of the csv is not very straightforward.
For some background information, the csv file contains bucket weights from a mining loader which are recorded with the corresponding timestamp, which is in a binary format. The file also contains a marker to tell me which loading unit it came from as the name of the file is the same regardless of which machine it comes from. The format of the file is out of my control. The file is a standard weekly download and I would expect to see the same type of file from at least another 5 machines.
If you look at the attached example csv in Excel, I need to pull out the information in columns B and C but only from row 29 down. The rest of the information above this is machine information only. What I also need to do is associate these records so I know that they came from the unit number which is shown in cell B5.
Also, how can I then convert the binary time to standard date/time format? This is the formula I use through Excel to convert it "= ((LEFT (D29, 10)/86400) +25569+8/24)"
My end result is to be able to run a report which shows the bucket weights for a particular unit in a user selectable time frame.
View 1 Replies
View Related
May 2, 2014
I am trying to automatically import student data from excel into an access relational database structure to use the data to report progress in an ongoing manner.I have managed to import an excel sheet with the raw data and I analysed it through the wizard and have produced a clean relational database with the data.
I was wondering, now that I have the access database structure defined, is there a way to now import new data from another excel file (new data with same headers) to the newly created relational database? I was hoping to append to the existing data with only new data from the excel sheet.
I have an excel file with Student names and what units they are enrolled in. I also have fields where results are shown with the date. So the data looks like:
Joe Bloggs Unit1 PP 1-01-2013
Joe Bloggs Unit2 PP 1-01-2013
Joe Bloggs Unit3 PP 1-01-2013
I have attached a picture showing the structure of the relational database that works.
View 2 Replies
View Related
Sep 10, 2014
I created a form for adding new data to an existing database and it worked just fine.
Today the form has decided to add time to the date field and this clashes with the table it import to.
Why/how it's changed the date format and how to change it back?
View 2 Replies
View Related
May 11, 2015
Is there a way to import data to Excel from Access without retaining the link ?
I have a table and two queries (from that table) that I wish to export to a specific (Templated) Excel file.
I want to send the data to the Excel file then be able to subsequently copy and paste and email the file without any data connections etc.
Alternatively : to export from Access to the templated excel file.
View 2 Replies
View Related
Sep 9, 2013
How to Export and Import data into and from Access using a Quicken Interchangeable Format (QIF) ...
View 4 Replies
View Related
Jun 30, 2015
I just created a database and need to connect it to the data source. The data comes from a http website (intranet from work). When I open the link using firefox, I can view the website with the data in it, but when I open it from Internet Explorer, I get a save as pop-up message to save a csv file which contains all the data. The extension of the http website ends with csv. So it is something like http (slash slash...) Intranetname/referral_dbase.csv
Currently, I am opening the file using firefox, copying all the data manually, and pasting it in a text file using notepad. After that, I import the file into access. The delimiter of the data is this symbol: |
I am trying to find a way to link my database to the website where the data is located so that I can skip the manual process of opening the website and copying the data and saving it into a text file and then importing that file into access. I was thinking to have like a form in access with a bottom that will automatically import that data from this link and paste it into a table in access using the delimiter symbol mentioned above.
Is this too complicated? Is it even possible in access 2010?
View 1 Replies
View Related
Sep 23, 2013
I have written a query where it displays sorted data for two columns, but problem is its sorting on one column but not on another.
When checking the query separately its giving proper output, but in form view its sorting only on one column not on another.
View 1 Replies
View Related
Sep 17, 2013
I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference).
Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.
View 3 Replies
View Related
Jul 10, 2013
I have a cross tab query that displays data by customer (rows) and MONTHS (columns).
However I need the columns to be the 12 months of the year 1 to 12.
However, if the selected data for a particular customer does not have any records in a month then I get an error in the report as the cross tab query only selects the months with data.
How do I get the report to show 0 or a blank in these columns
View 2 Replies
View Related
Feb 4, 2005
Hi,
I work in a library, and it's my job to keep track of information on thousands of journals.
I have seven sources of information--some of them were already on my computer in word or excel form and I converted them for access, and some of them I downloaded from various places we have subscriptions.
My approach so far has been to add the necessary fields to my SuperHoldings table and run updates and appends from the other tables to update and append that one SuperHoldings table. I chose the SuperHoldings table because, true to its name, it was the one with the most records. All of the tables can be related using the Title field.
I'm wondering if perhaps my approach is wrong. Making just one table to use seems like a waste of a database, but I'm not sure how to organize it otherwise. What I want is to be able to go to one central place and see all of this information for each record at once. (That makes 19 total unique fields, plus an optional "Subscription Info" link for EBSCO and Direct titles). (The Notes fields I would want to append into one field together, even though they all have different information--I asked how to do this is a previous post, so I can do it now.)
My problem is that often there are titles in one table that aren't in another table. Let's say that I use SuperHoldings as my primary table, and information about each record is filled in from the other six tables--well, the Project Muse table has some records that aren't in the SuperHoldings table--so if SuperHoldings is the control, then the Project Muse records won't come up unless they're in common with the SuperHoldings records.
I've looked at the way to relate tables, and there isn't an option for Show ALL records of BOTH Tables--combining like records when applicable.
So, that's why I've been adding all of the fields to SuperHoldings, appending whatever titles/records aren't in the Superholdings list, and updating information one table and one field at a time from the other six tables.
Actually, all of that work is pretty much done--but information is going to change on those six tables. I can either update my superholdings table from changes each source sends out and just forget about downloading new versions of the other 6 forever, or I can download new versions periodically and have it updated like that.
If you're confused... well, I'm sorry. I'm confused to.
I'm putting a list of my tables at the end of this. If you have suggestions on how I can organize this, please let me know.
I am on the lookout for workshops or training sessions that I can sign up for to be trained in Microsoft Access, but so far I haven't found any. I think I would prefer an actual classroom setting so I can ask these insane questions and use my hands to make gestures.
Thanks,
Siena
My Tables:
SuperHoldings
Title (Primary Key)
ZP#
Former Titles
Title Changes
Paper Holdings
Microform Holdings
Closed (Yes/No Box)
Actives
TITLE (Primary Key)
FUND
NOTES
EBSCO
Title (Primary Key)
ISSN
Notes 1
Notes 2
Notes 3
Vendor
Subscription Info (Group of Fields Only Applicable to EBSCO Titles and not repeated elsewhere.)
Directs
Title (Primary Key)
Fund
Notes
Vendor
Subscription Info (Group of Fields Only Applicable to Direct Titles and not repeated elsewhere.)
JSTOR
Title (Primary Key)
ISSN
JSTOR Holdings
JSTOR URL
Vendor
Project Muse
Title (Primary Key)
ISSN
E-ISSN
Notes
PM Holdings
PM URL
Vendor
EJS
Title (Primary Key)
ISSN
E-ISSN
Notes
EJS Holdings
EJS URL
Vendor
View 3 Replies
View Related
Oct 30, 2014
If my database gets quite large is there a way to create various groups under the Queries object? What about Reports, etc?
For example, within Queries, have qryGroup1, qryGroup2, etc.
What about color coding queries?
View 1 Replies
View Related
Aug 1, 2007
I have a database that I import data from an excel spreadsheet into multiple times daily. The table that this data is imported into has several key fields that if the data already exisits in the table, and I attempt to import data that is the same except for one or more of the key fields is different. At this time the database it creates a different record. I am trying to get the database to overwrite the data in the database.
View 1 Replies
View Related
Jan 5, 2015
I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.
We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.
My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.
View 3 Replies
View Related
Apr 15, 2007
Hi, i am a new comer. i have a table for students' comment for the first semester. however, in the 2nd semester i want to replace them with import data from excel to this table, but on the same student there are 2 rows of records (1 is 1st semester and 1 is 2nd semester).
how can i replace the new data to the old data? or how can i delete all the old data in the exsiting table so that i can import a new excel file?
thx
View 1 Replies
View Related
Mar 13, 2014
I want my access database to store information about "tests" and "products" that are in each "test". So "tests" is the "one" and "products" is the "many" (each test has many products).My client needs to routinely send me lists of tests and the products that are in each test. I then need to put this information in to my database
I don't want to type out the information they send me, instead I want to devise a simple form for them to complete and email to me (perhaps in excel) that they populate, and I then import/paste in to my database.How should the excel form be structured so that it is both easy for my client to populate and easy for me to import/paste in to my database. e.g. importing lots of test and product information, rather than type it in. Bearing in mind the 1:M structure.
View 3 Replies
View Related
May 21, 2013
Is there any way to view a Saved Import to see what it is doing?
I just inherited a database from someone that has several saved imports. I need to be able to see where the data is coming from and where it is going to so that I can recreate it using VBA.
View 3 Replies
View Related
Dec 10, 2012
I'm trying to import an XML file into my access database using an XSL Transform (I only need certain bits of data), however when I import the tables and field names come through fine now but the only values that come through are the last values listed in the XML file... for instance when i import high there would be 10 high values but only the last one comes through.I would obviously like the "Day" field to be a date/time field in access, and the high and low to be a number field in access... but this is back-burner to solving the real issue listed up top.This is my XSL file:
Code:
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="Transform Link (I don't have 10 posts yet and can't post links)">
<xsl:template match="/">
<Weather>
<xsl:for-each select="response/forecast/simpleforecast/forecastdays/forecastday">
[code]...
View 2 Replies
View Related
Jan 22, 2013
Is there a way to import a PST outlook file into Access 2010 or 2013. Or is there a Plug-in needed that can do it.
View 3 Replies
View Related
Jul 28, 2014
I am trying to load a csv file by coding, which is DoCmd.TransferText acImportDelim, "", "tblTempPayments", newFilename, False. One field which I have declared as Text, some rows for the fields are numeric, some rows are text, and some are alphanumeric.of the fields being declared as Text, it is throwing an error by creating an additional table naming temp_importerrors, and having 3 fields for Error, Field and Row, so in my view it is taking the majority of the rows, thinking a data type on the majority of the data type, and others being error out.The only work around that was successful for me, is placing that relevant values wit double quotes.
View 3 Replies
View Related
Oct 4, 2012
I had created a database a number of months back and received.After getting halfway through and seeing some requests come through from management on what they want to see or improvements, it looks as though there will have to be some retooling done to the database like moving some fields to different tables as there is duplicate entry fields.Would you import all the tables into a new database and then append the data to where it needs to go or keep working within the one I have?
View 3 Replies
View Related
Nov 1, 2012
I have a database for when our people are out in the field. The people will fill out large questionnaires that I want to import into our main database. How can I automate this?
View 10 Replies
View Related
Nov 11, 2013
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
View 14 Replies
View Related
