Import to many to many table

[Dave]

Hi

I have a junction table in a many-to-many relationship to 2 tables to
which I want to import excel data. The junction Table basically looks
like this: (Not exactly, this is just an example)

Order_FK
Product_FK
Quantity

The spreadsheet I can import into the junction table has both the
Order_FK and Quantity. No problem. But I can't figure out how to make
sure the correct Product_FK is included?
The spreadsheet will actually be created from other software as an
export function. The user will export from the other program and
import into access at the click of a button. (I need to auomate the
process)

Thanks very much,

Dave B

 

[John W. Vinson]

Hi

I have a junction table in a many-to-many relationship to 2 tables to
which I want to import excel data. The junction Table basically looks
like this: (Not exactly, this is just an example)

Order_FK
Product_FK
Quantity

The spreadsheet I can import into the junction table has both the
Order_FK and Quantity. No problem. But I can't figure out how to make
sure the correct Product_FK is included?
The spreadsheet will actually be created from other software as an
export function. The user will export from the other program and
import into access at the click of a button. (I need to auomate the
process)

Thanks very much,

Dave B

Unless there is SOMETHING in the spreadsheet to indicate what product is
involved, you can't. If you were handed a printout of the spreadsheet, would
you be able to tell whether

Order_FK 241; Quantity 100

referred to 1/4" galvanized washers or Boeing 777 airliners?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Access Developer]

So tell us how you know what Product goes with each of the Records. Alas, we
only know what you tell us about it. There must be _some_ way to determine,
or you can't do what you want.

Larry Linson
Microsoft Office Access MVP

 

[Dave]

So tell us how you know what Product goes with each of the Records. Alas,we
only know what you tell us about it. There must be _some_ way to determine,
or you can't do what you want.

 Larry Linson
 Microsoft Office Access MVP









- Show quoted text -

Of course. Sorry for the missing bits there.

Each spreadsheet exported out of the software will be specific to one
product. Therefore we know before each import what the product is. In
fact, the filename may be something like "widgets.xls"

So, yes we do know what the product will be for each record. I just
don't know how to tell access what it will be on import.

Thanks,

Dave

 

[John W. Vinson]

Each spreadsheet exported out of the software will be specific to one
product. Therefore we know before each import what the product is. In
fact, the filename may be something like "widgets.xls"

So, yes we do know what the product will be for each record. I just
don't know how to tell access what it will be on import.

You can include a text literal, or a DLookUp into the products table, in your
append query; e.g.

INSERT INTO junctiontable(ORDER_FK, PRODUCT_FK, Quantity)
SELECT spreadsheet.ORDER_FK, DLookUp("Product_PK", "Products", "ProductName =
'Widget'"), spreadsheet.Quantity
FROM spreadsheet;

where spreadsheet is the linked Widgets sheet. To automate this you may need
to use VBA code to select the desired spreadsheet from the disk and construct
this SQL, just splicing in the appropriate productname (or, if you're doing it
in code, looking up the ID and splicing in that numeric value in place of the
DLookUp).
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Dave]

You can include a text literal, or a DLookUp into the products table, in your
append query; e.g.

INSERT INTO junctiontable(ORDER_FK, PRODUCT_FK, Quantity)
SELECT spreadsheet.ORDER_FK, DLookUp("Product_PK", "Products", "ProductName =
'Widget'"), spreadsheet.Quantity
FROM spreadsheet;

where spreadsheet is the linked Widgets sheet. To automate this you may need
to use VBA code to select the desired spreadsheet from the disk and construct
this SQL, just splicing in the appropriate productname (or, if you're doing it
in code, looking up the ID and splicing in that numeric value in place ofthe
DLookUp).
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thank you. I don't know how you guys do it so easily. Is SQL a second
language for you?

The help is much appreciated.

Dave

 

[John W. Vinson]

Thank you. I don't know how you guys do it so easily. Is SQL a second
language for you?

sixth, approximately... <bg>
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Access Developer]

Thank you. I don't know how you guys do it so
easily. Is SQL a second language for you?

Access' dialect of SQL is the 'native language' of Access Queries... the
nice user interface you see is just a tool to make creating Access Queries
easier. If you look at an Access query that you created with the Query
Builder, you can choose SQL view and see what "your query-writing assistant"
(Access) actually generated for you. (And, there are some types of query,
e.g., Union Queries, that you can represent with SQL, but not in the Query
Builder.) So, if you are going to go very far with Access, you'll need to
learn some SQL.

Larry Linson
Microsoft Office Access MVP