repetitious entries in a table?

W

W E B GU R L

I am a beginner reading the Dummies book!
...............................................................

I convert an Excel spreadsheet to Access

I have one column that has product codes

I have another column where I have to add the same product codes plus a reference to
images that match the product codes

How can I automate this so I do not have to physically type in the path to the images?


e.g

ProductCode - 12345 ----> already in database
ImageSmall - ../products/images/12345.jpg ---------> I need to type this


I thought I would be clever and in design mode give a default value, but that only works
for new entries. Is there a way to tell Access to populate the ImageSmall column with at
least the path to the images and then I just cut and paste the product code.

I am thinking this maybe something I need to do in Excel before converting the spreadsheet
to Access.


I thank anyone willing to help me!
 
D

Douglas J. Steele

If ImageSmall is always based on ProductCode, there's no reason to store it
in a table. (In fact, it's technically a violation of database normalization
principles to store it)

Instead, add a computed field to a query. In the query grid, the field would
look something like:

ImageSmall: "../products/images/" & [ProductCode] & ".jpg"

Use the query wherever you would otherwise have used the table.
 
W

W E B GU R L

I appreciate this help Douglas. I am only new with all this and have not created a query
before so please be gentle with me!

1. Opened create query in design mode
2. Added my table and then clicked close
3. Chose the field - ImagesSmall
4. Went to update query ---> help files said I should do this
4.Typed in the update to section - "../products/images/" & [ProductCode] & ".jpg"
5. Show is ticked, sort is blank

Should the criteria be set the same as the update?
After I save and I run, no idea on what happens next as I did not see anything update.
Thought I would see "../../for_sale/listings/images/" & [ListingNumber] & ".jpg" in every
row appear


I am really stuck!


If ImageSmall is always based on ProductCode, there's no reason to store it
in a table. (In fact, it's technically a violation of database normalization
principles to store it)

Instead, add a computed field to a query. In the query grid, the field would
look something like:

ImageSmall: "../products/images/" & [ProductCode] & ".jpg"

Use the query wherever you would otherwise have used the table.
 
W

W E B GU R L

I should have said the when I clicked the run button I am asked for a parameter value -
what?






I appreciate this help Douglas. I am only new with all this and have not created a query
before so please be gentle with me!

1. Opened create query in design mode
2. Added my table and then clicked close
3. Chose the field - ImagesSmall
4. Went to update query ---> help files said I should do this
4.Typed in the update to section - "../products/images/" & [ProductCode] & ".jpg"
5. Show is ticked, sort is blank

Should the criteria be set the same as the update?
After I save and I run, no idea on what happens next as I did not see anything update.
Thought I would see "../../for_sale/listings/images/" & [ListingNumber] & ".jpg" in every
row appear


I am really stuck!


If ImageSmall is always based on ProductCode, there's no reason to store it
in a table. (In fact, it's technically a violation of database normalization
principles to store it)

Instead, add a computed field to a query. In the query grid, the field would
look something like:

ImageSmall: "../products/images/" & [ProductCode] & ".jpg"

Use the query wherever you would otherwise have used the table.
 
A

Arvin Meyer

Douglas was saying that you didn't need to actually store this in a field
since the query can produce the same result. He is correct. You do not need
to use an update query as I had suggested. Instead make what is called an
alias column in a select query, like this:

SmallImages: "C:\ThePath\ToFile\" & [ProductID] & ".jpg"

I'm not sure that the "..\ " syntax will work in your path. You may need to
type the fyll qualified path to the file.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

W E B GU R L said:
I should have said the when I clicked the run button I am asked for a parameter value -
what?






I appreciate this help Douglas. I am only new with all this and have not created a query
before so please be gentle with me!

1. Opened create query in design mode
2. Added my table and then clicked close
3. Chose the field - ImagesSmall
4. Went to update query ---> help files said I should do this
4.Typed in the update to section - "../products/images/" & [ProductCode] & ".jpg"
5. Show is ticked, sort is blank

Should the criteria be set the same as the update?
After I save and I run, no idea on what happens next as I did not see anything update.
Thought I would see "../../for_sale/listings/images/" & [ListingNumber] & ".jpg" in every
row appear


I am really stuck!


If ImageSmall is always based on ProductCode, there's no reason to store it
in a table. (In fact, it's technically a violation of database normalization
principles to store it)

Instead, add a computed field to a query. In the query grid, the field would
look something like:

ImageSmall: "../products/images/" & [ProductCode] & ".jpg"

Use the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



W E B GU R L said:
I am a beginner reading the Dummies book!
..............................................................

I convert an Excel spreadsheet to Access

I have one column that has product codes

I have another column where I have to add the same product codes plus a
reference to
images that match the product codes

How can I automate this so I do not have to physically type in the path to
the images?


e.g

ProductCode - 12345 ----> already in database
ImageSmall - ../products/images/12345.jpg ---------> I need to type this


I thought I would be clever and in design mode give a default value, but
that only works
for new entries. Is there a way to tell Access to populate the ImageSmall
column with at
least the path to the images and then I just cut and paste the product
code.

I am thinking this maybe something I need to do in Excel before converting
the spreadsheet
to Access.


I thank anyone willing to help me!
Click to expand...
Click to expand...
 
W

W E B GU R L

Thanks for your help today. Excel NG guy gave me something easier to do.

..........................................................................................

Assuming Product Codes in column A with Product Code as title.

In B2 enter ="../product/image/" & A2 & ".jpg"

Copy down column B




Douglas was saying that you didn't need to actually store this in a field
since the query can produce the same result. He is correct. You do not need
to use an update query as I had suggested. Instead make what is called an
alias column in a select query, like this:

SmallImages: "C:\ThePath\ToFile\" & [ProductID] & ".jpg"

I'm not sure that the "..\ " syntax will work in your path. You may need to
type the fyll qualified path to the file.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

W E B GU R L said:
I should have said the when I clicked the run button I am asked for a parameter value -
what?






I appreciate this help Douglas. I am only new with all this and have not created a query
before so please be gentle with me!

1. Opened create query in design mode
2. Added my table and then clicked close
3. Chose the field - ImagesSmall
4. Went to update query ---> help files said I should do this
4.Typed in the update to section - "../products/images/" & [ProductCode] & ".jpg"
5. Show is ticked, sort is blank

Should the criteria be set the same as the update?
After I save and I run, no idea on what happens next as I did not see anything update.
Thought I would see "../../for_sale/listings/images/" & [ListingNumber] & ".jpg" in every
row appear


I am really stuck!


If ImageSmall is always based on ProductCode, there's no reason to store it
in a table. (In fact, it's technically a violation of database normalization
principles to store it)

Instead, add a computed field to a query. In the query grid, the field would
look something like:

ImageSmall: "../products/images/" & [ProductCode] & ".jpg"

Use the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



W E B GU R L said:
I am a beginner reading the Dummies book!
..............................................................

I convert an Excel spreadsheet to Access

I have one column that has product codes

I have another column where I have to add the same product codes plus a
reference to
images that match the product codes

How can I automate this so I do not have to physically type in the path to
the images?


e.g

ProductCode - 12345 ----> already in database
ImageSmall - ../products/images/12345.jpg ---------> I need to type this


I thought I would be clever and in design mode give a default value, but
that only works
for new entries. Is there a way to tell Access to populate the ImageSmall
column with at
least the path to the images and then I just cut and paste the product
code.

I am thinking this maybe something I need to do in Excel before converting
the spreadsheet
to Access.


I thank anyone willing to help me!
Click to expand...
Click to expand...
 
A

Arvin Meyer

W E B GU R L said:
Thanks for your help today. Excel NG guy gave me something easier to do.

.............................................................................
..............

Assuming Product Codes in column A with Product Code as title.

In B2 enter ="../product/image/" & A2 & ".jpg"

Copy down column B
Click to expand...

Which was one of the choices in my post. The point is, that I like your
Excel guy (in my first post), was answering your question, not solving your
problem. Douglas's answer solved your problem. You do not need another field
when an alias column in a query solves the problem in a way that follows
relational database theory and rules. If you do things the "right" way,
you'll be rewarded with an efficient trouble-free application.

In this case, either method will give you the result you desire, but only
the alias column will allow you to easily add data directly into the
database.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top