From temporary table to master table and then join this in junction table

Hello,

I have imported two spreadsheets from excel to two temporary tables
called "import_tbl_contract" and "import_tbl_products". I do this on
daily basis and import many spreadsheets.

I end up with these data:

[import_tbl_contract]
contract_number
contract_title
start_date
end_date

[import_tbl_products]
product_number
product_text
price

I now append these data to the following two tables:

[tbl_contract]
contract_id (PK)
contract_title
start_date
end_date

e.g.
1; Syringes and needles; 01-01-2010; 31-12-2010

[tbl_products]
product_id
product_number
product_text
price

e.g.
1; 02030405; syringe 1; dkk 12,00
2; 05063004; syringe 2; dkk 10,00
3; 74737327; syringe 3; dkk 15,00

I now want to join the data in a junction table:

[tbl_contractdetails]
contract_detail_id
contract_id
product_id

e.g.:
1; 1; 1
1; 1; 2
1; 1; 3

How can I do this, when I on daily basis import many spreadsheets? I'm
a newbie to SQL and VBA.

Thanks in advance.

Morten
0
moso97ad
4/8/2010 1:18:21 PM
access 16762 articles. 2 followers. Follow

4 Replies
756 Views

Similar Articles

[PageSpeed] 56

I don't see how you can since there is nothing in your data that links 
product to contract.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"moso97ad" wrote:

> Hello,
> 
> I have imported two spreadsheets from excel to two temporary tables
> called "import_tbl_contract" and "import_tbl_products". I do this on
> daily basis and import many spreadsheets.
> 
> I end up with these data:
> 
> [import_tbl_contract]
> contract_number
> contract_title
> start_date
> end_date
> 
> [import_tbl_products]
> product_number
> product_text
> price
> 
> I now append these data to the following two tables:
> 
> [tbl_contract]
> contract_id (PK)
> contract_title
> start_date
> end_date
> 
> e.g.
> 1; Syringes and needles; 01-01-2010; 31-12-2010
> 
> [tbl_products]
> product_id
> product_number
> product_text
> price
> 
> e.g.
> 1; 02030405; syringe 1; dkk 12,00
> 2; 05063004; syringe 2; dkk 10,00
> 3; 74737327; syringe 3; dkk 15,00
> 
> I now want to join the data in a junction table:
> 
> [tbl_contractdetails]
> contract_detail_id
> contract_id
> product_id
> 
> e.g.:
> 1; 1; 1
> 1; 1; 2
> 1; 1; 3
> 
> How can I do this, when I on daily basis import many spreadsheets? I'm
> a newbie to SQL and VBA.
> 
> Thanks in advance.
> 
> Morten
> .
> 
0
Utf
4/8/2010 3:12:11 PM
On Thu, 8 Apr 2010 06:18:21 -0700 (PDT), moso97ad <nyhusevej28@gmail.com>
wrote:

>I now want to join the data in a junction table:
>
>[tbl_contractdetails]
>contract_detail_id
>contract_id
>product_id

You can't create a (useful) junction table from *just* the contract and
product tables. The whole point of a junction table is that a record only
exists when there is in fact a real-life connection between a particular
Contract and a particular Product.

You could run a "Cartesian join" query adding every possible combination of
contracts and products, but that would be pointless, since it would imply that
every contract in fact involves every single product; if you had 1000
contracts and 500 products, you'ld get 500,000 rows.

Do any of your spreadsheets provide a logical link between products and
contracts?
-- 

             John W. Vinson [MVP]
0
John
4/8/2010 3:52:56 PM
On 8 Apr., 17:52, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
..
>
> Do any of your spreadsheets provide a logical link between products and
> contracts?
> --
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0John W. Vinson [MVP]

I have the contract_number which links the contract and products.

Morten
0
moso97ad
4/9/2010 8:13:32 AM
On Fri, 9 Apr 2010 01:13:32 -0700 (PDT), moso97ad <nyhusevej28@gmail.com>
wrote:

>On 8 Apr., 17:52, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
>wrote:
>.
>>
>> Do any of your spreadsheets provide a logical link between products and
>> contracts?
>> --
>>
>> � � � � � � �John W. Vinson [MVP]
>
>I have the contract_number which links the contract and products.
>
>Morten

No, it does not, not based on what I've seen.

The contract number doesn't "link" anything in the example you posted. If I
asked you "What products are related to contract 123?" where would you look
for the answer?

Please explain where in your data there is some connection between the table
of contracts and the table of products. If it exists you haven't posted it.
-- 

             John W. Vinson [MVP]
0
John
4/9/2010 4:52:21 PM
Reply:

Similar Artilces:

Unit of Measure Detail table
Hi! I am trying to modify the GP sales invoice to add line item weight but for some reason I cannot pull the IV UOM Detail table on the report. This report is linked to the IV UOM HDR table which I was able to pull on the SOP Sales report but if I try to open this, I cannot see the IV UOM Detail table. Any ideas? Thanks -- Marisol Mortera ...

matching data from different tables with similar columns
HI, I am having some trouble with matching some data and I was wonderin if anybody could help me? I have two tables with the columns "Name" an "Amount", the first table has the column "Names" field but the colum "Amount" empty. I wanted to fill the "Amount" column in the firs table based on the second table, the problem is that the second tabl has many more records than the first. Is there a way to create formula to look up the name on second table and "amount" data on th first table. Thanks for the help. Regards, Marco -- Mes...

Pivot Table Problem
I am working in an excel spreadsheet which another person created a pivot table in. I have to do a lot of cutting and pasting into another report. When I scroll my mouse over any cell in the sheet with data in it a bluebox appears with the information in that in cell. How do I turn that off? It is very annoying as it causes a delay in my trying to copy that cell. Hi, While in the pivot table choose PivotTable Tools, Options, Options, Display and turn off Show contextual tooltips. Cheers, Shane -- If this helps, please click the Yes button. Cheers, Shane Devenshire ...

Excel 2002 only pivot table feature?
Hi All, I have a pivot table that was created in Excel 2002. My co-workers who view the same pivot table in Excel 2000 don't get a "Show All" selection to "select all"/"deselect all" row field data when they click on the row field item down-arrow. I hope someone can help me with this question...Is the "Show All" selection an Excel 2002 only feature or is there a setting/feature in Excel 2000 that can produce the same result as the Excel 2002 "Show All" selection? Keith Excel 2000 doesn't have this feature, but you can show ...

Cross table
Hi everybody !! i come again here with a problem i can seem to solve :s i hope someone will be able to give me a hand on this... here it is : i have a sheet with 2 rows: location and activity. several activities can be found in one location, even several of the same activities. schematic exemple location activity 1 A 1 B 2 A 3 A 3 B 3 C 4 B 4 C I would like to create something that give me as a result a sheet where we can see the spatial relations between the activities. so i would have all...

Excel Table Question
I have a table of data, 3 columns Date Type Business Unit 01Jul 06 FAC NP 05 Jul 06 FAC BU5 11 Jul 06 MTC BU3 14 Aug 06 FAC BU3 16 Aug 06 MTC BU1 22 Aug 06 NWR NP 23 Aug 06 NWR NP 25 Aug 06 FAC BU1 etc. The output I want is a table for each Business Unit, as below BU1 FAC MTC NWR RWC LTC Total Jul 06 2 1 0 0 0 3 Aug 06 2 1 2 ...

pivot tables #28
Hi, I need you help, please, because I don´t find any solution to this: I have 5 columns. Column A has company names. Column B has months. C and D have items. A B C D Company Month Item1 Item2 Microsoft 1 2 0 4 2 3 1 4 3 I would like to get in cells C2 the sum of item1 along the year. And in C3 the sum of the item in the first month of the year. I don´t know how to do it. any...

temporary documents
How do I find something saved in temporary documents by accident? If you mean the Temp folder on your system, you can press Windows key + R to display the Run dialog box. Type %tmp% and press Enter. Locate the file and move it to a proper location. On the other hand, if you have edited an e-mail attachment without saving it to the hard drive first, see http://www.gmayor.com/outlook_attachments.htm. -- Stefan Blom Microsoft Word MVP "Lisa M" <Lisa M@discussions.microsoft.com> wrote in message news:009F3D91-414E-4213-AA89-B4CA5939BE8B@microsoft.com.....

Franchise Opportunity, Join A Winning Team
BEAR TRANSMISSIONS Automotive Franchise Opportunity Join A Winning Team Bear Transmission Center, a Leading Transmission & Complete Car Care Franchise Company is expanding in the following areas: Throughout the state of Florida 273 cities. No Automotive or Mechanical Experience is Required. . Why Bear Transmission? Over 15 years solid automotive transmission business and trade experience. The best and most lucrative of transmission repair specialists in the world. Most of our franchisees do not have automotive experience We offer a Proven System with the Best Training & Support in ...

Temporary files
I was trying to burn a movie when a message came up to say that there was no room in C:\documents~\Bonnie~|local~\tmp~ Files like DF19C2.tmp, DF9E43.tmp, DF5OF6.tmp what are these files? =?Utf-8?B?cGVha3MgYXo=?= <peaks az@discussions.microsoft.com> wrote in news:F6B93E64-C922-4085-8031-8C0508086FF3@microsoft.com: > I was trying to burn a movie when a message came up to say > that there was no room in C:\documents~\Bonnie~|local~\tmp~ > Files like DF19C2.tmp, DF9E43.tmp, DF5OF6.tmp what are > these files? Generally, most *.tmp files (along with *.~, ~8.*...

excel table
how can i set the "name" and "range" in the "Excel table"?? -- lsy ------------------------------------------------------------------------ lsy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23917 View this thread: http://www.excelforum.com/showthread.php?threadid=375910 If you are talking about creating a new table or range, highlight the area you wish to make a range, then do Insert > Name > Define, and type the name you wish to give the range in the small window at the top of the pop-up.........the "Refers to:&quo...

Export Tables to SQL Server
I have a database already in Access 2003, and want to export the structure (just the tables, not the data) into SQL Server Express 2005 (there are no stored queries, macros, etc... to worry about). What's the easiest way to do this? I've tried using the Upsizing Wizard, but it doesn't seem to want to talk to SQL Server Express. Thanks Phil. you shoudl get a copy of SLQ Server Developers Edition and learn how to use DTS or if you go file, new, project (existing data) you should be able to import SOME tables into SQL Server just using MS Access "Phil" <N/...

Disable a table in CTabCtrl.
How to disable a table in CTabCtrl. I cannot do this, anyone could give a solution? Thanks! ...

Update table with date and number
I'm trying to update a table with the current date and the current user ID but I keep getting a syntax error. Could somebody check my code and tell me what I'm doing wrong? Dim strUserID As Integer strUserID = DLookup("UserID", "q_GetUser") CurrentDb.Execute "UPDATE (pt_PurchaseOrders) SET pt_PurchaseOrders.CancelledDate = Date(), pt_PurchaseOrders.CancelledBy = strUserID WHERE pt_PurchaseOrders.PONumber = " & Me.PONumber JB - Two things - you want the RunSQL statement (not the Execute), and also the strUserID needs to have ...

SAving temporary files
So I opened a document file from my email and worked on it for awhile. I pressed the save button and the bar in the lower right hand corner saig word is saving wo.doc. But now I cannot find said word document. I have looked in my temporary internet files and cannot find it there. help! Locations shown here; http://www.howto-outlook.com/faq/securetemp.htm "emily" <emily@discussions.microsoft.com> wrote in message news:0B09317D-9AA3-4583-B701-0A0ECC04A430@microsoft.com... > So I opened a document file from my email and worked on it for awhile. I > pre...

to show only last date from a table
I have a table for employee vacation dates. How can i get a text box in a form to show only the last vacation date? -- Mr. G. On Wed, 21 Nov 2007 14:37:01 -0800, Mr. G. <MrG@discussions.microsoft.com> wrote: >I have a table for employee vacation dates. How can i get a text box in a >form to show only the last vacation date? Set its control source to =DMax("[vacationdate]", "[tablename]", "[employeeID] = " & [EmployeeID]) using your own field and table names of course. John W. Vinson [MVP] Thanks -- Mr. G. "John W. V...

Best approach? Native Excel pivot for a SQL table
We've researched how to have an Excel pivot table use as its source a table generated by a SQL-based program. There are a number of ways mentioned in the Excel documentation. At least one seem complicated to even test--like having to create a data definition. We're happy to do the work but are unclear which method is best, and whether one or more methods is risky. We first probably will have to create the pivot table using a sample table with the right field names, and then ideally will point it somehow at the real table, which will be on a different system and at a different path....

Word cannot read temporary file
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Over the last two months I have experienced a major problem with Word (12.2.4) when dealing with Word attachments opened from Lotus Notes (8.5.1). After opening Word attachments at some indeterminate time afterwards I will receive the message 'Word encountered an error reading xxxxx.doc. The file may be corrupt'. Clicking OK results in 'Word failed reading from this file'. The retry button here just loops back to the same message, pressing Cancel results on 'Word encountered an error readi...

Pivot Table - Group by Month
Greetings My data is in financial year, July to June. The date field is in the format DD/MM/YYYY which is in the column area of the pivot table. I have this grouped by month and year, so it displays Jul-Dec 2003, followed by Jan-Jun 2004. Some items in the "Row" area of the table do not have entries for all 12 months, but I still want to display the month for that item (with no data). In the "field settings" of month I have selected "show items with no data". Unfortunately this then displays Jan-Jun for 2003 and Jul-Dec for 2004, all blank because those d...

Pivot Table configuration
I have got a query looking at Sales History against Forecast and want to see the accuracy to forecast as a percentage in a pivot table. The fields I have are Customer Date ActualSales ForecastQty Var: IIf([ForecastQty] Is Null,[ActualSales],([ActualSales]-[ForecastQty])) Perc: [Var]/[ActualSales] This all works fine if I run the query but when I try and put it into a pivot Table it doesn't like it and crashes. Is there a different way to handle this in Pivot tables. Thank you in Advance ...

working with temporary working files.. #2
I keep getting the message that I cannot save a temp. working file. This work has been save on a cd and transfered back on to my desk top. Help Right-click the file, properties, see if "read only" is checked. This happens when a file is saved to a CD. Save the file on your hard drive and then copy it to the CD. Publisher does not like saving to removable media. Delete all the temporary (.tmp) files that are located in the Windows\Temp folder. For information about how to delete files, click Start, click Help, click the Index tab, type deleting files, and then click Display. --...

Time Table
hi members i want to develop a timetable for teachers and their periods. i want there should not be timeclash -- b166e ----------------------------------------------------------------------- b166er's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3491 View this thread: http://www.excelforum.com/showthread.php?threadid=57376 What have you done so far? Where are you getting stuck? If you provide some details, someone may be able to help. b166er wrote: > hi members > > i want to develop a timetable for teachers and their periods. > i want there sh...

Decimal places in a temporary table
I am using a temporary table to perform calculations before appending the resultant invoice transaction records to my main table. The temporary table is created in code like this: strSQL = "CREATE TABLE tblInvoiceTransactionsTemp (fldInvoiceTransactionID Double,fldProductID Double, fldIFAID Double,fldQuantity Integer, fldFreeOfCharge YesNo," strSQL = strSQL & "fldEnqID Double, fldInvoiceHeaderID Double, fldAgreedPrice Currency, fldVATRate Double)" DoCmd.RunSQL strSQL This all works fine but the problem is the VAT (tax) rate - the current rate is 17.5% but wh...

Mass Data Entry for a Junction Table
On my Junction table we track the employee and what education offerings they have attended. We have found that we have multiple employees attending the same offerings on the same day. (makes sense, if a class is offered one day you will about 20-30 people in that class) Is there any way to simplify the data entry process. Right now on the form the person doing data entry selects the employee, the education offering and the date. This has to be done for each attendee. What would be great would be to create a form that allows you to select a course and a date, and then select mult...

Outlook attachment temporary folder
In outlook 2000, the attachment will be saved to a fixed location, defined under the key HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Outlook\Security\OutlookSecu reTempFolder (from KB KB249793) The temporary file will be removed from those temporary location usually, but not always. It may leave some files behind in this folder. How to make sure that Outlook will clear up the temporary file inside that folder? The only way to insure that outlook deletes the temps is to close the attachment before moving to a new message. "Samuel" <hi@yahoo.com> wrote in message n...