UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Post Tabular Data?    
 
   
JenniferMurphy
post Sep 24 2019, 12:37 AM
Post#1



Posts: 153
Joined: 1-May 14
From: Silicon Valley


Many, if not most, discussion forums have a way to enter tabular data. I find it odd that a database discussion forum that is all about tables does not. iconfused.gif

What is the best way for me to post an example of a table that I would like help with?

--------------------
Using Office 365 on Win 10 & Office 2007 on Win XP
Go to the top of the page
 
nvogel
post Sep 24 2019, 12:47 AM
Post#2



Posts: 1,042
Joined: 26-January 14
From: London, UK


You can use SQL DDL and DML to post table structures and data. This is better than just sketching data in tabular form because anyone can run it and then test out possible solutions in a working database.

CREATE TABLE table1 (x INT NOT NULL PRIMARY KEY, z VARCHAR(10) NOT NULL);

INSERT INTO table1 (x,z) VALUES (1,'AAA');
INSERT INTO table1 (x,z) VALUES (2,'BBB');
INSERT INTO table1 (x,z) VALUES (3,'CCC');
Go to the top of the page
 
JenniferMurphy
post Sep 24 2019, 01:06 AM
Post#3



Posts: 153
Joined: 1-May 14
From: Silicon Valley


QUOTE
You can use SQL DDL and DML to post table structures and data. This is better than just sketching data in tabular form because anyone can run it and then test out possible solutions in a working database.

OK, but that assumes that I have the tables defined and the database created, right? What if I want feedback on a rough design or just post some sample data? It would be handy to have a table tool. The one on Mr. Excel is excellent.

Let me ask again. What is the best way to post tabular data?

The only way I have found is to paste the table into Notepad, replace tabs with spaces, line up the columns, then paste that between code tags, like this:

CODE
         A   B   C   D
     1   10  22   7  11
     2    6  15   0   7
     3   33  24  46  22

--------------------
Using Office 365 on Win 10 & Office 2007 on Win XP
Go to the top of the page
 
nvogel
post Sep 24 2019, 02:25 AM
Post#4



Posts: 1,042
Joined: 26-January 14
From: London, UK


You may also find this site useful: http://www.convertcsv.com/csv-to-SQL.htm

I pasted your example data into it (without the 1,2,3 row numbers) and it generated the following result, which works fine in Access and other SQL DBMSs. One of the problems with sample data in the form you posted it is that there are no data types, keys or other constraints specified. Keys and data types can be particularly important for people to understand and answer a question, which is why DDL really helps.

CREATE TABLE mytable(
A INTEGER NOT NULL PRIMARY KEY
,B INTEGER NOT NULL
,C INTEGER NOT NULL
,D INTEGER NOT NULL
);
INSERT INTO mytable(A,B,C,D) VALUES (10,22,7,11);
INSERT INTO mytable(A,B,C,D) VALUES (6,15,0,7);
INSERT INTO mytable(A,B,C,D) VALUES (33,24,46,22);


This post has been edited by nvogel: Sep 24 2019, 02:31 AM
Go to the top of the page
 
MadPiet
post Sep 24 2019, 02:43 AM
Post#5



Posts: 3,367
Joined: 27-February 09



You could always use Excel for the rough outline part. I'm not sure how popular it is on here. On SQLServerCentral, folks are a lot more cautious about other people's files. If you post something that's not basically text, it won't be opened.

I really like NVogel's answer. Didn't know about that site. Looks really handy!
Go to the top of the page
 
GroverParkGeorge
post Sep 24 2019, 10:01 AM
Post#6


UA Admin
Posts: 36,199
Joined: 20-June 02
From: Newcastle, WA


Historically speaking (UA has been around for nearly 20 years now under its current name and ownership), we have had some good features (we encourage civil conversations and discourage trolling) and some that are not so good (the bulletin board software licensed by the site didn't include the ability to create online tables).

Unfortunately, the bulletin board software which supports UA doesn't come with a way to create "online" tables. I guess that might reflect the fact that the bulletin board software was originally not written specifically to support discussions of "tables of data". I'm not sure how much customization it would take, nor who on the admin team might be willing and able to undertake that prospect, or even whether the license under which UA operates would allow for customization of the original product that way. Still, it's worth looking into. So thanks for raising the question. I'll pass it on to the admin team for evaluation.

In the meantime, there are workarounds. My favorite is either an upload of the actual accdb or a csv. Both make it possible to pass along structure AND data samples for consideration.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
JenniferMurphy
post Sep 25 2019, 02:12 AM
Post#7



Posts: 153
Joined: 1-May 14
From: Silicon Valley


QUOTE
So thanks for raising the question. I'll pass it on to the admin team for evaluation.

OK, thanks

QUOTE
In the meantime, there are workarounds. My favorite is either an upload of the actual accdb or a csv. Both make it possible to pass along structure AND data samples for consideration.

If you mean an attachment, yes, I'd do that if it's more than a simple table. But that a lot of extra work just to show a simple table of data both for me and for readers.

If the Code tags worked properly, the problem would be 99% solved. In the following example, I entered code tags then converted the tags and everything in between to Courier New (monospaced). I then carefully lined up the data in columns.

CODE
      Col1   Col2   Col3
       A1     A2     A3
       B1     B2     B3
       C1     C2     C3


As you can see, this works. But if I try to paste tabular data from Word or even Notepad, it gets all messed up and if the font is different, I get a bunch of what I assume is HTML code.


--------------------
Using Office 365 on Win 10 & Office 2007 on Win XP
Go to the top of the page
 
cheekybuddha
post Sep 26 2019, 03:01 PM
Post#8


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


Here's a toy for you all!

It's a db that lets you select am Access database, and will extract the DLL, and optionally INSERT statements as well, from selected tables.

I haven't really tested whether the DDL is any good! blush.gif

It's based on a SO post here

I also used a module from devhut.com to enable scrolling of a textbox. @Daniel thanks.gif

And also an Access Web module to copy to clipboard

The db is very basic at the moment and only really does what I've said above. If I get time I might add to it to create the tables in a new or existing db, and add some bells and whistles to have progress indicators etc, or extract SQL from queries.

Also. I thought it might be handy to choose the flavour of SQL output - it's only for Access at the moment. Beware! Some comments are added into the DDL, and I guess these will need to be removed before execution in Access.

The SO post mentioned that creating relationships with CASCADE needs to be done via ADO rather than DAO, so bear that in mind.

If anyone tries this and it's useful then I might also try and add extracting data in a tabular form to paste here, and then put it in the code archive, so we can point users to it to extract their DDL to post when asking questions.

Have fun!

d

[EDIT: Attachment deleted - I've re-posted it in a new thread to prevent hijacking this thread]

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Sep 26 2019, 05:50 PM
Post#9


UtterAccess VIP
Posts: 1,897
Joined: 4-June 18
From: Somerset, UK


Thanks David
I've had a quick look at your new 'toy' and I think its already very useful.
Definitely worth developing further.

You can certainly create/delete relationships with DAO.
IIRC you can also impose RI & cascades using DAO but its been a while since I did anything like that and I would need to check whether my memory is correct

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Sep 26 2019, 05:55 PM
Post#10


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


I haven't had a chance to try properly yet Colin, but my understanding from the SO thread is that executing DDL to create a table via CurrentDb.Execute doesn't create the CASCADES properly. Using CurrentProject.Connection.Execute would.

I know you can use DAO to create relationships using its object model (the code in the demo uses DAO to extract that info for the DDL).

If you have successfully recreated relationships from DDL using CurrentDb.Execute "CREATE TABLE ..." that is great news!

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Sep 26 2019, 05:57 PM
Post#11


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


@nvogel - I'm going a bit dizzy with your recent avatar changes! I like them all (including your old one) so I get that it must be difficult to decide!! dazed.gif

ohyeah.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Sep 26 2019, 07:03 PM
Post#12


UtterAccess VIP
Posts: 1,897
Joined: 4-June 18
From: Somerset, UK


To create relationships, I normally use the method explained by Allen Browne at http://allenbrowne.com/func-DAO.html#CreateRelationDAO

However you can certainly create relationships using DDL - adapted from Create FK relationship

CODE
Sub CreateRelTest()

CurrentDb.Execute "CREATE TABLE Artists ( id    INTEGER PRIMARY KEY,  name  TEXT);"

CurrentDb.Execute "CREATE TABLE Tracks (  traid     INTEGER,  title   TEXT,  artist INTEGER,  FOREIGN KEY(artist) REFERENCES Artists(id));"
CurrentDb.Execute "INSERT INTO Artists VALUES (1, 'Tom Chapin');"
CurrentDb.Execute "INSERT INTO Artists VALUES (2, 'Harry Chapin');"

'* Tom's songs */
CurrentDb.Execute "INSERT INTO Tracks VALUES (1, 'Great Big Words', 1);"
CurrentDb.Execute "INSERT INTO Tracks VALUES (1, 'This Pretty Planet', 1);"
'/* Harry's songs */
CurrentDb.Execute "INSERT INTO Tracks VALUES (2, 'Cats in the Cradle', 2);"

End Sub


The above code doesn't include cascades
However this link suggests you can also use DDL for cascades (not tested) Constraint Clause - Access SQL

EDIT:
After further testing I can confirm that error 3289 occurs if you try to use DAO to add cascade update/delete to a join with RI
However using ADO works fine

CODE
Public Sub CreateTableWithCascadeConstraint()

On Error GoTo Err_Handler
        
    'DAO connection throws error 3289 - syntax error in CONSTRAINT clause
   'CurrentDb.Execute _
        "CREATE TABLE tblChild (keyID Long, CONSTRAINT FK_keyID FOREIGN KEY (keyID)" & _
        " REFERENCES tblParent(keyID) ON DELETE CASCADE);"
        
      'ADO connection performs operation flawlessly
    CurrentProject.Connection.Execute _
        "CREATE TABLE tblChild (keyID Long, CONSTRAINT FK_keyID FOREIGN KEY (keyID)" & _
        " REFERENCES tblParent(keyID) ON DELETE CASCADE);"
        
Exit_Handler:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in CreateTableWithDRIConstraint procedure : " & Err.Description
    Resume Exit_Handler
End Sub

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Sep 26 2019, 08:22 PM
Post#13


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


OK, a quick test with the DDL output by the toy revealed that executing via DAO was a no-go, but ADO worked without hitch.

I'll post an updated version tomorrow.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Sep 28 2019, 08:29 AM
Post#14


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


I've moved the toy into a new separate thread

--------------------


Regards,

David Marten
Go to the top of the page
 
jmkeuning
post Dec 10 2019, 03:50 PM
Post#15



Posts: 178
Joined: 20-October 15



Here is how I make a table like this:

PersonNameBegDateEndDate
Mike1/1/20121/15/2012
Sue1/14/20121/16/2012
Joan2/1/20124/12/2012


This uses the website https://theenemy.dk/table/ to make the bbcode table and notepad++ to clean it for pasting into this site.

Copy your table from Excel and paste it into https://theenemy.dk/table/
Click the BBCode button
Copy the output

Now, that text output will not work, so you need to clean the text by replacing:
CODE
[tr] -> [row]
[/tr] -> [/row]
[td] -> [cell]
[/td] -> [/cell]
[table] -> [table=border=1]


I recorded a macro to do that for me. This is more steps than I prefer to take, but it's the best I have found. It actually only takes a minute. Literally.


CODE
<Macro name="UtterAccess table fix" Ctrl="no" Alt="no" Shift="no" Key="0">
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="[tr]" />
            <Action type="3" message="1625" wParam="0" lParam="1" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="[row]" />
            <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="[/tr]" />
            <Action type="3" message="1625" wParam="0" lParam="1" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="[/row]" />
            <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="[td]" />
            <Action type="3" message="1625" wParam="0" lParam="1" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="[cell]" />
            <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="[/td]" />
            <Action type="3" message="1625" wParam="0" lParam="1" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="[/cell]" />
            <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="[table]" />
            <Action type="3" message="1625" wParam="0" lParam="1" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="[table=border=1]" />
            <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
        <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="\r\n" />
            <Action type="3" message="1625" wParam="0" lParam="1" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
        </Macro>


Edit to add this workflow image:

This post has been edited by jmkeuning: Dec 10 2019, 04:11 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 10:49 AM