Recommended method for splitting records

[Tokyo Alex]

Dear all,

I have a project to create an Access tool to convert our internal quote data
into our client's format for upload into an EDI system. Most of this is not
problematic. However, there is one issue that I can foresee having major
headaches over.

Our quote is (actually, can be made into by a couple of queries, but for
practical purposes is) a single record per quote, which will contain (amongst
others) a unique ID (Quote#), a brief description, a price, and maybe a
shipping charge.

The client's system, however, requires this to be loaded as _two_ separate
quotes, one for the quote itself and a separate one for the shipping charge,
if any.

So, I need a tool to convert:
OurID(Unique), Description, Price, ShipCharge, ...

into:
OurID(NotUnique), Description, Price, ...
OurID(NotUnique), "SHIP_CHARGE" AS Description, ShipCharge, ...

And I need it to generate only 1 target record if there's no shipping charge
(Which I'm fairly sure is represented by a 0, not NULL in my source data.

Other constraints/concerns:
The target format does not need a unique ID, as it will be given one when
uploaded into the client's system.
I use Access 2007, but my coworker who will also use the tool once finished
uses 2003, so the tool will be developed as a 2003 MDB not a 2007 ACCDB.
Both using Windows XP Pro.
I have precisely no ability to modify the target data structure whatsoever,
but I can modify the source data structure (as this tool will be running off
a query or set of queries that I create)
The tool will be used to process about 500~750 source records a hit in a
daily batch, so performance is not a critical issue (I'm happy to set it to
run and go make coffee if need be.)

Does anyone have any brillliant ideas about what would be a good way to go
about this? Alternatively any advice about what I should NOT do under any
circumstances would be just as appreciated.

Thanks very much in advance,
Alex.

 

[Paul Shapiro]

If I understand correctly, a union query should do this.

Select OurID, Description, Price, 0 as sortOrder From MyTable
Union All
Select OurID, "SHIP_CHARGE", ShipCharge, 1 as sortOrder From MyTable
Where ShipCharge>0
Order By OurID, sortOrder

You didn't describe any other included fields, but I assume there are
others. For the union query, both subqueries must have the same number of
fields, with the same datatypes. If there are fields that are not applicable
for the shipping charge row, you can use nulls for those fields.

 

[Tokyo Alex]

Hi Paul,

That is so simple it's brilliant, I would never have thought of it. Thank
you very much.

If you'll permit me a followup (or two):

You've written the UNION query on a table. I'll be using a query to find
the appropriate data for the final export. Is it better to make a make table
query and use it as the data source for your query, or is Access smart enough
that there's no problem using my original query as the source? If either is
OK, is one option considered 'better' for some reason?

Also, can a UNION query in Access be a maketable query also (I want the
final data in a table to export and potentially run other queries on), or
will I have to create a maketable query on your query to achieve this?

Thanks very much,
Alex.

If I understand correctly, a union query should do this.

Select OurID, Description, Price, 0 as sortOrder From MyTable
Union All
Select OurID, "SHIP_CHARGE", ShipCharge, 1 as sortOrder From MyTable
Where ShipCharge>0
Order By OurID, sortOrder
[Snip]

Dear all,

I have a project to create an Access tool to convert our internal quote
data
into our client's format for upload into an EDI system. Most of this is
not
problematic. However, there is one issue that I can foresee having major
headaches over.
[Snip]

So, I need a tool to convert:
OurID(Unique), Description, Price, ShipCharge, ...

into:
OurID(NotUnique), Description, Price, ...
OurID(NotUnique), "SHIP_CHARGE" AS Description, ShipCharge, ...
[Snip]

Does anyone have any brillliant ideas about what would be a good way to go
about this? Alternatively any advice about what I should NOT do under any
circumstances would be just as appreciated.

.

 

[Tokyo Alex]

Hi Paul,

That is so simple it's brilliant. I would never have thought of it. Thank
you very much.

If you will permit me a follow up (or two)...

You have witten your query on a table. As I will need to use a query to
assemble the required fields (they're all over the shop), I would like to ask
whether it's better to replace "MyTable" in your query or to make a maketable
query to create MyTable, and run your query on that. Related to this, my
query will have to query three or four ODBC-linked tables that can be very
slow to return results sometimes, so if the UNION query on a query would run
the subquery twice then a maketable is decidedly the better option.

Also, can a UNION query in Access also be a maketable query? I would like
to have the target data in a table for export and potentially running
analytical tools on; would I have to create a maketable query on your UNION
query to achieve this?

Thanks again,
Alex.

If I understand correctly, a union query should do this.

Select OurID, Description, Price, 0 as sortOrder From MyTable
Union All
Select OurID, "SHIP_CHARGE", ShipCharge, 1 as sortOrder From MyTable
Where ShipCharge>0
Order By OurID, sortOrder
[Snip]

Dear all,

I have a project to create an Access tool to convert our internal quote
data
into our client's format for upload into an EDI system. Most of this is
not
problematic. However, there is one issue that I can foresee having major
headaches over.
[Snip]

So, I need a tool to convert:
OurID(Unique), Description, Price, ShipCharge, ...

into:
OurID(NotUnique), Description, Price, ...
OurID(NotUnique), "SHIP_CHARGE" AS Description, ShipCharge, ...

And I need it to generate only 1 target record if there's no shipping
charge
(Which I'm fairly sure is represented by a 0, not NULL in my source data.
[Snip]
Does anyone have any brillliant ideas about what would be a good way to go
about this? Alternatively any advice about what I should NOT do under any
circumstances would be just as appreciated.

.

 

[Paul Shapiro]

No problem using a query instead of a table in the select clauses of the
union query. The only reasons to make a table out of a query are: a) You
need to keep the data as it exists at this moment, without future changes or
b) Access query processing has more limitations than higher-end products
like SQL Server, and sometimes Access refuses to use a query instead of a
table. But the union query is not one of those limitations. I doubt if you
can set a union query to be a maketable query, but you can probably create a
maketable query using "Select * From MyUnionQuery Order By ...". Try it and
see.

Hi Paul,

That is so simple it's brilliant, I would never have thought of it. Thank
you very much.

If you'll permit me a followup (or two):

You've written the UNION query on a table. I'll be using a query to find
the appropriate data for the final export. Is it better to make a make
table
query and use it as the data source for your query, or is Access smart
enough
that there's no problem using my original query as the source? If either
is
OK, is one option considered 'better' for some reason?

Also, can a UNION query in Access be a maketable query also (I want the
final data in a table to export and potentially run other queries on), or
will I have to create a maketable query on your query to achieve this?

Thanks very much,
Alex.

If I understand correctly, a union query should do this.

Select OurID, Description, Price, 0 as sortOrder From MyTable
Union All
Select OurID, "SHIP_CHARGE", ShipCharge, 1 as sortOrder From MyTable
Where ShipCharge>0
Order By OurID, sortOrder
[Snip]

Dear all,

I have a project to create an Access tool to convert our internal quote
data
into our client's format for upload into an EDI system. Most of this
is
not
problematic. However, there is one issue that I can foresee having
major
headaches over.
[Snip]

So, I need a tool to convert:
OurID(Unique), Description, Price, ShipCharge, ...

into:
OurID(NotUnique), Description, Price, ...
OurID(NotUnique), "SHIP_CHARGE" AS Description, ShipCharge, ...
[Snip]

Does anyone have any brillliant ideas about what would be a good way to
go
about this? Alternatively any advice about what I should NOT do under
any
circumstances would be just as appreciated.

.