G
Guest
I have a need to export the data in one of my tables to another DB format where the field mapping is very manual and not one-to-one at all. How do I do this?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Nurick
Hi Brad,
Create a query on your table that returns the field names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
Create a query on your table that returns the field names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
G
Guest
Thanks John,
I use access programmatically all the time, but cannot
figure out how to do what you are saying in an actual
access query. It seems to be limiting in the design view.
THanks!
and not one-to-one at all. How do I do this?
I use access programmatically all the time, but cannot
figure out how to do what you are saying in an actual
access query. It seems to be limiting in the design view.
THanks!
another DB format where the field mapping is very manual-----Original Message-----
Hi Brad,
Create a query on your table that returns the field names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
I have a need to export the data in one of my tables to
and not one-to-one at all. How do I do this?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
G
Guest
John, I was a little early on that last one. I reread
your message and better understand. I now have a query
with the table results, but in exporting them I cannot
figure out how to customize the export as you mention.
Help?
and not one-to-one at all. How do I do this?
your message and better understand. I now have a query
with the table results, but in exporting them I cannot
figure out how to customize the export as you mention.
Help?
another DB format where the field mapping is very manual-----Original Message-----
Hi Brad,
Create a query on your table that returns the field names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
I have a need to export the data in one of my tables to
and not one-to-one at all. How do I do this?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
G
Guest
I AM SOOO CONFUSED! :->
OK, back to response #1, customizing the query. I am
missing how to do that - I see the SQL view, but just
need a little guidance on setting it up. Actually, I'd
like to map the fields to another table but since the
names are different I cannot seem to figure out how.
Thanks again for any help you can provide.
and not one-to-one at all. How do I do this?
OK, back to response #1, customizing the query. I am
missing how to do that - I see the SQL view, but just
need a little guidance on setting it up. Actually, I'd
like to map the fields to another table but since the
names are different I cannot seem to figure out how.
Thanks again for any help you can provide.
another DB format where the field mapping is very manual-----Original Message-----
Hi Brad,
Create a query on your table that returns the field names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
I have a need to export the data in one of my tables to
and not one-to-one at all. How do I do this?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
B
Brad Eck
SELECT Products.StockNumber FROM Products;
This returns all my stocknumbers as expected. However,
the CSV file needs to have the column name as ItemNumber.
Is there a way to automate this? How do I add column
names which I know are not in my DB, i.e.
ItemNumber SomeOtherName Price
from something line:
SELECT Products.StockNumber Products.RetailPrice FROM
Products;
and not one-to-one at all. How do I do this?
This returns all my stocknumbers as expected. However,
the CSV file needs to have the column name as ItemNumber.
Is there a way to automate this? How do I add column
names which I know are not in my DB, i.e.
ItemNumber SomeOtherName Price
from something line:
SELECT Products.StockNumber Products.RetailPrice FROM
Products;
another DB format where the field mapping is very manual-----Original Message-----
Hi Brad,
Create a query on your table that returns the field names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
I have a need to export the data in one of my tables to
and not one-to-one at all. How do I do this?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
J
John Nurick
If you're working in SQL view, use constructions like these to change
the field names and generate fields that don't exist in the source
table:
SELECT
Products.StockNumber AS ReferenceNumber,
"CASH" As CustomerReference,
Date() As TransactionDate,
Products.UnitPrice As Price_Net
Products.UnitPrice * 1.175 As Price_IncTax
FROM Products;
the field names and generate fields that don't exist in the source
table:
SELECT
Products.StockNumber AS ReferenceNumber,
"CASH" As CustomerReference,
Date() As TransactionDate,
Products.UnitPrice As Price_Net
Products.UnitPrice * 1.175 As Price_IncTax
FROM Products;
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
SELECT Products.StockNumber FROM Products;
This returns all my stocknumbers as expected. However,
the CSV file needs to have the column name as ItemNumber.
Is there a way to automate this? How do I add column
names which I know are not in my DB, i.e.
ItemNumber SomeOtherName Price
from something line:
SELECT Products.StockNumber Products.RetailPrice FROM
Products;
another DB format where the field mapping is very manual-----Original Message-----
Hi Brad,
Create a query on your table that returns the field names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
I have a need to export the data in one of my tables to
and not one-to-one at all. How do I do this?--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
G
Guest
Perfect - how do you add columns for items that will not
exist (i knopw it sounds weird but the output needs to
match another databasa's format...
exist (i knopw it sounds weird but the output needs to
match another databasa's format...
-----Original Message-----
If you're working in SQL view, use constructions like these to change
the field names and generate fields that don't exist in the source
table:
SELECT
Products.StockNumber AS ReferenceNumber,
"CASH" As CustomerReference,
Date() As TransactionDate,
Products.UnitPrice As Price_Net
Products.UnitPrice * 1.175 As Price_IncTax
FROM Products;
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
SELECT Products.StockNumber FROM Products;
This returns all my stocknumbers as expected. However,
the CSV file needs to have the column name as ItemNumber.
Is there a way to automate this? How do I add column
names which I know are not in my DB, i.e.
ItemNumber SomeOtherName Price
from something line:
SELECT Products.StockNumber Products.RetailPrice FROM
Products;
to-----Original Message-----
Hi Brad,
Create a query on your table that returns the field names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
On Mon, 22 Dec 2003 13:01:15 -0800, "Brad Eck"
I have a need to export the data in one of my tables
another DB format where the field mapping is very manual
and not one-to-one at all. How do I do this?--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
J
John Nurick
Could be
Null As ColumnName
or
"" As ColumnName
Null As ColumnName
or
"" As ColumnName
Perfect - how do you add columns for items that will not
exist (i knopw it sounds weird but the output needs to
match another databasa's format...-----Original Message-----
If you're working in SQL view, use constructions like these to change
the field names and generate fields that don't exist in the source
table:
SELECT
Products.StockNumber AS ReferenceNumber,
"CASH" As CustomerReference,
Date() As TransactionDate,
Products.UnitPrice As Price_Net
Products.UnitPrice * 1.175 As Price_IncTax
FROM Products;
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
SELECT Products.StockNumber FROM Products;
This returns all my stocknumbers as expected. However,
the CSV file needs to have the column name as ItemNumber.
Is there a way to automate this? How do I add column
names which I know are not in my DB, i.e.
ItemNumber SomeOtherName Price
from something line:
SELECT Products.StockNumber Products.RetailPrice FROM
Products;
-----Original Message-----
Hi Brad,
Create a query on your table that returns the field
names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
On Mon, 22 Dec 2003 13:01:15 -0800, "Brad Eck"
I have a need to export the data in one of my tables to
another DB format where the field mapping is very manual
and not one-to-one at all. How do I do this?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
B
Brad Eck
OK - almost through this... YOU ROCK!!!
When I export if a '#' is in the column name it changes
it to a '.'. Help?
Also, can you point me to some string functions? I need
to peel off an extension and readd it. Thanks.
When I export if a '#' is in the column name it changes
it to a '.'. Help?
Also, can you point me to some string functions? I need
to peel off an extension and readd it. Thanks.
-----Original Message-----
Could be
Null As ColumnName
or
"" As ColumnName
Perfect - how do you add columns for items that will not
exist (i knopw it sounds weird but the output needs to
match another databasa's format...in-----Original Message-----
If you're working in SQL view, use constructions like these to change
the field names and generate fields that don't exist
the sourcetablestable:
SELECT
Products.StockNumber AS ReferenceNumber,
"CASH" As CustomerReference,
Date() As TransactionDate,
Products.UnitPrice As Price_Net
Products.UnitPrice * 1.175 As Price_IncTax
FROM Products;
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
On Wed, 24 Dec 2003 08:11:44 -0800, "Brad Eck"
SELECT Products.StockNumber FROM Products;
This returns all my stocknumbers as expected. However,
the CSV file needs to have the column name as ItemNumber.
Is there a way to automate this? How do I add column
names which I know are not in my DB, i.e.
ItemNumber SomeOtherName Price
from something line:
SELECT Products.StockNumber Products.RetailPrice FROM
Products;
-----Original Message-----
Hi Brad,
Create a query on your table that returns the field
names and data you
want, then export the query.
Use calculated fields in the query where necessary, e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
On Mon, 22 Dec 2003 13:01:15 -0800, "Brad Eck"
I have a need to export the data in one of my
toanother DB format where the field mapping is very manual
and not one-to-one at all. How do I do this?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
J
John Nurick
If you use funny characters in field names the names need to be enclosed
in [ ]. Access does this automatically if you are using the query design
grid. Remember that many database engines won't accept field names
containing spaces or other funny characters.
In Access/Jet (i.e. an ordinary .mdb file), you can use just about any
VBA function in a query (including functions you write yourself). To get
help on functions, hit Alt+F11 or Ctrl+G to get to the VBE and search
there, not in the help file you get from the main Access window.
With a SQL back end, what's available is (as far as I know) determined
by the particular database engine involved.
in [ ]. Access does this automatically if you are using the query design
grid. Remember that many database engines won't accept field names
containing spaces or other funny characters.
In Access/Jet (i.e. an ordinary .mdb file), you can use just about any
VBA function in a query (including functions you write yourself). To get
help on functions, hit Alt+F11 or Ctrl+G to get to the VBE and search
there, not in the help file you get from the main Access window.
With a SQL back end, what's available is (as far as I know) determined
by the particular database engine involved.
OK - almost through this... YOU ROCK!!!
When I export if a '#' is in the column name it changes
it to a '.'. Help?
Also, can you point me to some string functions? I need
to peel off an extension and readd it. Thanks.
-----Original Message-----
Could be
Null As ColumnName
or
"" As ColumnName
Perfect - how do you add columns for items that will not
exist (i knopw it sounds weird but the output needs to
match another databasa's format...
-----Original Message-----
If you're working in SQL view, use constructions like
these to change
the field names and generate fields that don't exist in
the source
table:
SELECT
Products.StockNumber AS ReferenceNumber,
"CASH" As CustomerReference,
Date() As TransactionDate,
Products.UnitPrice As Price_Net
Products.UnitPrice * 1.175 As Price_IncTax
FROM Products;
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
On Wed, 24 Dec 2003 08:11:44 -0800, "Brad Eck"
SELECT Products.StockNumber FROM Products;
This returns all my stocknumbers as expected. However,
the CSV file needs to have the column name as
ItemNumber.
Is there a way to automate this? How do I add column
names which I know are not in my DB, i.e.
ItemNumber SomeOtherName Price
from something line:
SELECT Products.StockNumber Products.RetailPrice FROM
Products;
-----Original Message-----
Hi Brad,
Create a query on your table that returns the field
names and data you
want, then export the query.
Use calculated fields in the query where necessary,
e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
On Mon, 22 Dec 2003 13:01:15 -0800, "Brad Eck"
I have a need to export the data in one of my tables
to
another DB format where the field mapping is very
manual
and not one-to-one at all. How do I do this?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
G
Guest
Thanks again for all your help.
-----Original Message-----
If you use funny characters in field names the names need to be enclosed
in [ ]. Access does this automatically if you are using the query design
grid. Remember that many database engines won't accept field names
containing spaces or other funny characters.
In Access/Jet (i.e. an ordinary .mdb file), you can use just about any
VBA function in a query (including functions you write yourself). To get
help on functions, hit Alt+F11 or Ctrl+G to get to the VBE and search
there, not in the help file you get from the main Access window.
With a SQL back end, what's available is (as far as I know) determined
by the particular database engine involved.
OK - almost through this... YOU ROCK!!!
When I export if a '#' is in the column name it changes
it to a '.'. Help?
Also, can you point me to some string functions? I need
to peel off an extension and readd it. Thanks.
-----Original Message-----
Could be
Null As ColumnName
or
"" As ColumnName
On Wed, 24 Dec 2003 09:36:22 -0800,
Perfect - how do you add columns for items that will not
exist (i knopw it sounds weird but the output needs to
match another databasa's format...
-----Original Message-----
If you're working in SQL view, use constructions like
these to change
the field names and generate fields that don't exist in
the source
table:
SELECT
Products.StockNumber AS ReferenceNumber,
"CASH" As CustomerReference,
Date() As TransactionDate,
Products.UnitPrice As Price_Net
Products.UnitPrice * 1.175 As Price_IncTax
FROM Products;
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
On Wed, 24 Dec 2003 08:11:44 -0800, "Brad Eck"
SELECT Products.StockNumber FROM Products;
This returns all my stocknumbers as expected. However,
the CSV file needs to have the column name as
ItemNumber.
Is there a way to automate this? How do I add column
names which I know are not in my DB, i.e.
ItemNumber SomeOtherName Price
from something line:
SELECT Products.StockNumber Products.RetailPrice FROM
Products;
-----Original Message-----
Hi Brad,
Create a query on your table that returns the field
names and data you
want, then export the query.
Use calculated fields in the query where necessary,
e.g.
PersonName: [LastName] & ", " & [FirstName]
Town: [City]
Millions: [Dollars] / 1000000
SalesTax: IIf([Taxable],[Amount] * [TaxRate], 0)
On Mon, 22 Dec 2003 13:01:15 -0800, "Brad Eck"
I have a need to export the data in one of my tables
to
another DB format where the field mapping is very
manual
and not one-to-one at all. How do I do this?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.