Making a query refrence a table to return to a new field

[Caleb]

Hello, I have three tables, an Orders table and a Next Buy table. The Next
Buy table is two columns, the first is a list of products, and the second is
a list of products to recommend to anyone who buys a porduct in the first
column.

I need to make a query that will creates two new fields [Purchased] and
[Next Purchase]. The [Purchased] field needs to take whats entered in the
[ProductsOrdered] field for a record and refrence the Next Buy table, putting
whats in the first column under [Purchased] and putting whats in the second
column under [Next Purchase].

The real problem here is that the [ProductsOrdered] field contains many
variations of the products, making it difficult to refrence to the first
column in the Next Buy table. I know this is confusing but I made it as clear
as I could.

Any help would be appreciated. Thanks

 

[Jeff Boyce]

Caleb

Which one do you want returned?

You mention "many variations", so you'll need to decide which one is
appropriate.

(by the way, we can't see what you're working with, so we have no idea what
YOU mean by "many variations" -- an example would help)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Caleb]

Sorry about that, here is an example of one of our products:

Loan Mod Magic Gold $27.97, $97 in 30 days, 365 day MBG
Loan Mod Magic Gold Best Value $97 now, 365 day MBG
Loan Mod Magic Platinum $17.97, $147 in 30 days, 365 day MBG
Loan Mod Magic Platinum $97 + $17.97
Loan Mod Magic Platinum Best Value $147 now, 365 day MBG

Its like that for almost all our products, the problem is, in the Next Buy
table its simply reffered to as Loan Mod Magic.

Caleb

Which one do you want returned?

You mention "many variations", so you'll need to decide which one is
appropriate.

(by the way, we can't see what you're working with, so we have no idea what
YOU mean by "many variations" -- an example would help)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I have three tables, an Orders table and a Next Buy table. The Next
Buy table is two columns, the first is a list of products, and the second
is
a list of products to recommend to anyone who buys a porduct in the first
column.

I need to make a query that will creates two new fields [Purchased] and
[Next Purchase]. The [Purchased] field needs to take whats entered in the
[ProductsOrdered] field for a record and refrence the Next Buy table,
putting
whats in the first column under [Purchased] and putting whats in the
second
column under [Next Purchase].

The real problem here is that the [ProductsOrdered] field contains many
variations of the products, making it difficult to refrence to the first
column in the Next Buy table. I know this is confusing but I made it as
clear
as I could.

Any help would be appreciated. Thanks

 

[Jeff Boyce]

Caleb

I'm not familiar with your line of business, so 365 MBG means nothing to me.

Are you saying that you are keeping the name of the product (?Loan Mod
Magic?) AND the "level" (?e.g., Gold Best Value?) AND a price (?$97?) AND a
time frame (?"now"?) AND whatever "365 MBG" means ALL in a single field?!

If so, stop now! Basic database design calls for "one fact, one field". Use
separate fields for each of those attributes.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry about that, here is an example of one of our products:

Loan Mod Magic Gold $27.97, $97 in 30 days, 365 day MBG
Loan Mod Magic Gold Best Value $97 now, 365 day MBG
Loan Mod Magic Platinum $17.97, $147 in 30 days, 365 day MBG
Loan Mod Magic Platinum $97 + $17.97
Loan Mod Magic Platinum Best Value $147 now, 365 day MBG

Its like that for almost all our products, the problem is, in the Next Buy
table its simply reffered to as Loan Mod Magic.

Caleb

Which one do you want returned?

You mention "many variations", so you'll need to decide which one is
appropriate.

(by the way, we can't see what you're working with, so we have no idea
what
YOU mean by "many variations" -- an example would help)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I have three tables, an Orders table and a Next Buy table. The
Next
Buy table is two columns, the first is a list of products, and the
second
is
a list of products to recommend to anyone who buys a porduct in the
first
column.

I need to make a query that will creates two new fields [Purchased] and
[Next Purchase]. The [Purchased] field needs to take whats entered in
the
[ProductsOrdered] field for a record and refrence the Next Buy table,
putting
whats in the first column under [Purchased] and putting whats in the
second
column under [Next Purchase].

The real problem here is that the [ProductsOrdered] field contains many
variations of the products, making it difficult to refrence to the
first
column in the Next Buy table. I know this is confusing but I made it as
clear
as I could.

Any help would be appreciated. Thanks

 

[Caleb]

Yes they are all in the same field, that is the way its exported from the
shopping cart website we use. I know its stupid, the orders are exported to a
..csv file and then needs to be imported into this database im designing. Is
there any way to automate splitting these fields up?

Caleb

I'm not familiar with your line of business, so 365 MBG means nothing to me.

Are you saying that you are keeping the name of the product (?Loan Mod
Magic?) AND the "level" (?e.g., Gold Best Value?) AND a price (?$97?) AND a
time frame (?"now"?) AND whatever "365 MBG" means ALL in a single field?!

If so, stop now! Basic database design calls for "one fact, one field". Use
separate fields for each of those attributes.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry about that, here is an example of one of our products:

Loan Mod Magic Gold $27.97, $97 in 30 days, 365 day MBG
Loan Mod Magic Gold Best Value $97 now, 365 day MBG
Loan Mod Magic Platinum $17.97, $147 in 30 days, 365 day MBG
Loan Mod Magic Platinum $97 + $17.97
Loan Mod Magic Platinum Best Value $147 now, 365 day MBG

Its like that for almost all our products, the problem is, in the Next Buy
table its simply reffered to as Loan Mod Magic.

Caleb

Which one do you want returned?

You mention "many variations", so you'll need to decide which one is
appropriate.

(by the way, we can't see what you're working with, so we have no idea
what
YOU mean by "many variations" -- an example would help)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello, I have three tables, an Orders table and a Next Buy table. The
Next
Buy table is two columns, the first is a list of products, and the
second
is
a list of products to recommend to anyone who buys a porduct in the
first
column.

I need to make a query that will creates two new fields [Purchased] and
[Next Purchase]. The [Purchased] field needs to take whats entered in
the
[ProductsOrdered] field for a record and refrence the Next Buy table,
putting
whats in the first column under [Purchased] and putting whats in the
second
column under [Next Purchase].

The real problem here is that the [ProductsOrdered] field contains many
variations of the products, making it difficult to refrence to the
first
column in the Next Buy table. I know this is confusing but I made it as
clear
as I could.

Any help would be appreciated. Thanks

 

[Jeff Boyce]

Caleb

You can import the "raw" data, then use queries to parse it into a more
permanent and well-normalized table structure. You are not limited to the
structure you get on import.

You will come up with ways (look into the Left(), Right(), Mid(), and
Instr() functions) in queries to parse this raw data.

If you need something "automatic" and don't have VBA experience, you could
use a macro to "play" those queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes they are all in the same field, that is the way its exported from the
shopping cart website we use. I know its stupid, the orders are exported
to a
.csv file and then needs to be imported into this database im designing.
Is
there any way to automate splitting these fields up?

Caleb

I'm not familiar with your line of business, so 365 MBG means nothing to
me.

Are you saying that you are keeping the name of the product (?Loan Mod
Magic?) AND the "level" (?e.g., Gold Best Value?) AND a price (?$97?) AND
a
time frame (?"now"?) AND whatever "365 MBG" means ALL in a single field?!

If so, stop now! Basic database design calls for "one fact, one field".
Use
separate fields for each of those attributes.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry about that, here is an example of one of our products:

Loan Mod Magic Gold $27.97, $97 in 30 days, 365 day MBG
Loan Mod Magic Gold Best Value $97 now, 365 day MBG
Loan Mod Magic Platinum $17.97, $147 in 30 days, 365 day MBG
Loan Mod Magic Platinum $97 + $17.97
Loan Mod Magic Platinum Best Value $147 now, 365 day MBG

Its like that for almost all our products, the problem is, in the Next
Buy
table its simply reffered to as Loan Mod Magic.


:

Caleb

Which one do you want returned?

You mention "many variations", so you'll need to decide which one is
appropriate.

(by the way, we can't see what you're working with, so we have no idea
what
YOU mean by "many variations" -- an example would help)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello, I have three tables, an Orders table and a Next Buy table.
The
Next
Buy table is two columns, the first is a list of products, and the
second
is
a list of products to recommend to anyone who buys a porduct in the
first
column.

I need to make a query that will creates two new fields [Purchased]
and
[Next Purchase]. The [Purchased] field needs to take whats entered
in
the
[ProductsOrdered] field for a record and refrence the Next Buy
table,
putting
whats in the first column under [Purchased] and putting whats in the
second
column under [Next Purchase].

The real problem here is that the [ProductsOrdered] field contains
many
variations of the products, making it difficult to refrence to the
first
column in the Next Buy table. I know this is confusing but I made it
as
clear
as I could.

Any help would be appreciated. Thanks

 

[Caleb]

Thanks I'll look into those, I have very little VBA experience and I've been
trying to mess with AnySQL Maestro but Im having dificulties understanding
everything

Caleb

You can import the "raw" data, then use queries to parse it into a more
permanent and well-normalized table structure. You are not limited to the
structure you get on import.

You will come up with ways (look into the Left(), Right(), Mid(), and
Instr() functions) in queries to parse this raw data.

If you need something "automatic" and don't have VBA experience, you could
use a macro to "play" those queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes they are all in the same field, that is the way its exported from the
shopping cart website we use. I know its stupid, the orders are exported
to a
.csv file and then needs to be imported into this database im designing.
Is
there any way to automate splitting these fields up?

Caleb

I'm not familiar with your line of business, so 365 MBG means nothing to
me.

Are you saying that you are keeping the name of the product (?Loan Mod
Magic?) AND the "level" (?e.g., Gold Best Value?) AND a price (?$97?) AND
a
time frame (?"now"?) AND whatever "365 MBG" means ALL in a single field?!

If so, stop now! Basic database design calls for "one fact, one field".
Use
separate fields for each of those attributes.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Sorry about that, here is an example of one of our products:

Loan Mod Magic Gold $27.97, $97 in 30 days, 365 day MBG
Loan Mod Magic Gold Best Value $97 now, 365 day MBG
Loan Mod Magic Platinum $17.97, $147 in 30 days, 365 day MBG
Loan Mod Magic Platinum $97 + $17.97
Loan Mod Magic Platinum Best Value $147 now, 365 day MBG

Its like that for almost all our products, the problem is, in the Next
Buy
table its simply reffered to as Loan Mod Magic.


:

Caleb

Which one do you want returned?

You mention "many variations", so you'll need to decide which one is
appropriate.

(by the way, we can't see what you're working with, so we have no idea
what
YOU mean by "many variations" -- an example would help)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello, I have three tables, an Orders table and a Next Buy table.
The
Next
Buy table is two columns, the first is a list of products, and the
second
is
a list of products to recommend to anyone who buys a porduct in the
first
column.

I need to make a query that will creates two new fields [Purchased]
and
[Next Purchase]. The [Purchased] field needs to take whats entered
in
the
[ProductsOrdered] field for a record and refrence the Next Buy
table,
putting
whats in the first column under [Purchased] and putting whats in the
second
column under [Next Purchase].

The real problem here is that the [ProductsOrdered] field contains
many
variations of the products, making it difficult to refrence to the
first
column in the Next Buy table. I know this is confusing but I made it
as
clear
as I could.

Any help would be appreciated. Thanks