Two auto Number fields in the One Table 2

[pmo]

I have a make table query that has only 2 fields. Unfortunately the fields are both autonumber fields from seperate tables. I am unable to run the query as I will be creating a table with 2 autonumber fields. These fields do not have to be autonumber fields in the new table.

Can anyone help please.

Wayne

 

[jrbarnett]

Autonumber fields can be converted to (and joined to) Long Integer types with no problem.

Does this help?

John

 

[BillPower]

Shame you didn't post your SQL, you can try something like this in your Make Table Query.

To see how this works create 2 Tables, Table1 and Table2 add to both an Auto Number Field and name both AutoNumberField . In Table1 also add 2 Fields and name them Field1 and Field2.

Paste this SQL into a new Query, Run the query. You should now have a new Table Table3 with both Auto Number Fields named AutoNumberField1 and AutoNumberField2.

SELECT [Table1].[AutoNumberField]+0 AS AutoNumberField1, [Table2].[AutoNumberField]+0 AS AutoNumberField2, Table1.Field1, Table1.Field2 INTO Table3
FROM Table1 INNER JOIN Table2 ON Table1.AutoNumberField = Table2.AutoNumberField;
Table2.AutoNumberField;

Now open the new Query in Design View and follow the method I've used in your own query.

Hope this makes sense.

Bill

 

[pmo]

Thanks for the help. I am still a little lost as my understanding of code is only in the early stages. Below is what is behind my make table query. Is there some way of changing the autonumber fields to number fields during the make table process?

SELECT VendorT.[Vendor Id], ProductsT.[Products Id] INTO Vendors_Products IN 'c:\LData\AgInt.mdb'
FROM VendorT INNER JOIN ProductsT ON VendorT.[Vendor Id] = ProductsT.[Vendor Id];



Wayne

 

[BillPower]

Looking at your profile, apparently you don't find us very helpful. 29 questions 1 marked as helpful. Someone just pointed this out tonight. Anyway, I already did this one so what the hell.

SELECT [Vendor Id]+0 AS VendorID, [Products Id]+0 AS ProductsId INTO Vendors_Products IN 'c:\LData\AgInt.mdb'
FROM VendorT, ProductsT;

Bill

 

[pmo]

Bill I will give it a go thanks.
How do I enter if advice is helpful or not. There has been no intention to state otherwise. Without the help of people like you I would be truly lost. Maybe you could point this out to your friends also.

Wayne

 

[BillPower]

Wayne,

That is what you always say "I'll give it a go", but you don't follow it up by saying whether it worked or not.

Last week I spent a couple of hours trying to come up with a solution to your question in thread702-557120 I and 2 or 3 other contributors don't know if we helped or not. We obviously helped other members because of the stars we all got. But they weren't from you.

I've said this before in other threads and I'm going to try not to say it again. (I get stirred up when I see other disgruntled contributors). Us contributors need a thankyou or at least an acknowledgement for our suggestions and most importantly of all we need to know if our suggestion worked, otherwise what's the point of it all.

Bill

 

[pmo]

Thanks Bill,
I understand what you are saying. It has certainly been an oversight from my perspective. I spent a few moments last night checking through the process of how I enter that the advice was helpful. I will ensure this feedback is provided. As you will see by the questions that I have asked, I am not an advanced user. I am increasing my knowledge with the help of people like you. I scan the questions hoping that one day someone will ask something that I am able to help with. It doesn't happen often.

Be assured I do not take what this facility or you offer for granted. (The fact that I have not stated that the advice was helpful may make it look that way).

Your advice has again worked. I do sincerely thankyou for your time. I will mark this as helpful.

Wayne