Extract contents of table columns

Hi Alan, thanks for the reply. I'll create a copy of the database and put some dummy data in there.
I'm actually still developing it as we speak with "live" data from previous customer orders so I may need to do some more data input to the tables that I think you'll be looking at.
At the moment the db is around 16mb; I don't know what it will be when I take most of the data out (I suspect not much smaller as the structure is fairly large and still growing) - I don't want to start deleting queries or other objects unless necessary, as it may make the db unstable or stop some processes running correctly. Is there a max size that I can send?
Rgds
Mike

Originally Posted by alansidman

There is a size limitation set by the board. Before you try to upload the db, make sure that you compact and repair. That will reduce the size. Also, you have to zip any db posted to the board.

Hi Alan, I've copied the database and populated it with some test data. The tables that contain the relevant info are as follows:-
"Import data From Ebay - MIDS TOOLS". This is the table that is imported and overwritten every day and contains details of that day's despatches.

"MUK part consists" This is a permanent table that holds the consist parts of the kits.

"Products Main Import Table" This a permanent table that holds details of our products.

The following queries draw info from those tables:-
"Toolstream "MUK" PRODUCT CONSISTS" This is a dynaset of all the MUK kits sold that day and includes the consists. The Consist qtys aren't summed (i.e. they only show the consist qty for each single kit, not the total consists sold)

"Toolstream Product order with prices" This query produces the re-order report that shows what items we have sold that day. This is where the problem lies. You will see the reorder list shows the "MUK" numbers at the kit level; what I need is for the consist parts to be on that list as these are the products that we order from the supplier.

If on the same list there are already some of the consists part #s by virtue of the fact that we have also sold these as single items, the product number should only appear once but the qty should be the sum of that product sold in the kits plus those sold as a single product. Eg, product # 427655 - 1 sold as part of kit MUK04 and 2 sold as individual items so the re-order qty for that should be 3.

"PRODUCT REORDER csv file for Toolstream EDI" This is essentially the same as the above qery, but this is the file that we send to the supplier's EDI system. Ditto the above re the consist parts being on this report and not the MUK kit numbers.

I've also entered some products in the test data that have been sold in their own right as well as appearing as part of the MUK kits that have been sold. If you need me to send a breakdown of what the final re-order reports should contain based on the day's sales, I can do this, but I don't want to clog up this post with too much info.
Thanks & rgds
Mike

Originally Posted by alansidman

Mike;
You forgot to upload the file.

Alan

Hi Alan, I did the attachment, but don't know why it didn't go with the reply. I tried again just now and I kept getting a "Excel Forum" error so I stripped all objects out of the db that aren't relevant to this topic. It seems to have worked now.

Thanks for letting me know.
Rgds
Mike

Hi Alan, thanks for your continued help. I know how to physically make the relationships, but this aspect is still somewhat of a dark art to my level of knowledge. I'm still getting my head around this...

I'll set the table up as you suggest; on the first 2 field names you have put PK and FK respectively - what do these refer to?
Sorry for the extra questions....
Kind rgds
Mike

Hi Alan, thanks for the further info.
Did you modify the query to get it to work? I've just looked at the database but the query results appear the same.

But now I have another issue - I can't open the db as I'm getting a security error. I've tried copying the db to my C drive as instructed in the error but still no good.

On your recommendation re setting the table up, do I need to make the PID field an Autonumber? I looked at the links you provided and it seems that this is the best way to get a PK. I thought the actual productnumber field could be used for this, but you suggest this as the FK.

Again, thank you for your continued support.
Rgds
Mike

Hi Alan, thanks for the further help. Sorry for the delay in responding but I've been away for a few days.

I'll try your suggestions, but please excuse me re implementing it; I'm like a piano player that can't read music - I can usually get soemthing done by example but reading it from text doesn't always sink in...
Kind regards
Mike

Originally Posted by alansidman

Mike:

Yes, I changed the structure slightly to the query. I added the MUK Parts consist table to the query and did a left join on the "import data from ebay...." table.

Try a right click on the data base before opening it and selecting properties. There may be a check box that needs to be unchecked. Can't recall the name, but I have had this issue with db downloaded from forums in the past.

I always make it a practice to make the id for the table an autonumber. I then use any other identifier as a secondary key that is matched to a FK in other tables. It is a good practice.

Alan

Hi Alan, I just tried the query and it's nearly there; however what I need for the EDI to work is to have the consist code numbers of the respective "MUK" code appear in the "Reorder Product Code" field of the query - at the moment the "MUK" numbers are still showing instead.

The Supplier EDI output csv file will only have 2 columns - the "Reorder Product Code" and the "sumof quantity" As the "MUK" numbers are our creation, the supplier doesn't recognise these, only the consist parts.

Is it possible to do what I'm asking?
Kind rgds
Mike

Hi Alan,

Sorry, I don't know what normalized means? I have an excel table that has months across the top as column headers, and style numbers down the left side as row headers, and then the inside is all the units we are forecasting for sales.

I need to combine multiple excel tables and do various calculations, like calculate dollar values using wholesale prices for each style.

My approach is to import that excel tables into Access where I can combine them easier. I also need to do a bunch of calculations. Like add some forecasts together,but subtract forecasts for units that are returned in each style/month. So I thought if I can get a spreadsheet that has two years of forecast months (24 columns) into a list that is very long but just has 3 columns (style, monthyear, units) it will be easier to do my calculations.

Otherwise I have to create multiple columns to calculate the dollar value of each month's units. So a column for January dollars (Jan units x price), then a column for Feb dollars (Feb units x price)....... If I have this simple list, it's just one column (monthyear units x price). Then I can pivot table to get things back with months across the top row.

Does this help you understand what I am trying to do?

It seems from my research so far it will be a complicated program to convert the tables to this 3 column format. Maybe I should just give up and import all my forecast tables into Access the way they are and figure out how to work with it there?

I'm a basic Excel user, good with pivot tables and can write very simple macros, but that's it so far.

Thanks, Amy

Hi Alan, I just found that you had already replied with a solution. I will try it and see how it works. Somehow I had read a post where you had asked me a question before I found this response with your file. Sorry, I'm very new to threads and posts and things, but kind of fun and MANY THANKS for such a prompt response! Amy

Hi Alan,

I am so excited! Thank you so much for your help. I downloaded your access file and it worked perfectly. I have never used the Excel Help Forum (or any help forum) before and can't believe I was able to find a solution to my problem between when I left work last night and this morning when I arrived. So awesome!

Thank you! Amy

Alan,

I've worked with you access solution and figured out how to add a query for another month (April). I can even make the data in the month column say "1/1/2011" instead of Jan (so I can sort better later).

What I can't figure out is how to adapt the solution to my real database which actually has field headers that are dates in stead of Jan, Feb, Mar. My field headers are 1/1/2011, 2/1/2011, 3/1/2011. I can create the individual queries, but I can't modify the UNION code.

SELECT Table1.Style, "1/1/2010" AS [Month], Table1.Jan AS Units
FROM Table1;

I'm having trouble with the last half of it - when I change Table1.Jan to Table1.1/1/2010 then I get an error message about having incorrect symbols when I try to run it.

Would you mind helping me adapt the UNION query to work with a table that has dates for field headers? I created one and tried to upload it but it said invalid file, and believe it or not, I don't know how to zip it to be smaller if that is the problem.

Anyway, this would be very helpful. I've spent about 45 minutes so far and just can't figure it out.

Thanks, Amy

Actually I think I have successfully zipped the file, where I added Table2 with my dates as field headers. I hope this makes it easier for you to respond. THANKS! Amy

Okay, thanks. What is your recommendation for the format of the field names so they can be sorted? I have two years of data, so I can't just use 1, 2,3, it needs to be 1_2010, 2_2011, etc or something. My goal is for the fields to be sortable in pivot tables so the reports come out with the proper month order.

I just discovered that my 1/1/10 and 2/1/10 field names don't sort properly for some reason, so I definitely need an alternative.

Thanks!

Hi Alan,

I finally figured out that my field headings can be text names like Jan10, Feb10 and when I do the query that combines the monthly files, I can make the data in the month column be whatever I want. When I use 01-11 and 02-11 there, the result will sort properly in a pivot table.

So I'm down to what I hope is a final question. You mentioned I should take the query that combines the months and make a table. If this query was a regular query, I know how to do it. But it seems that this query is a SQL query, and I don't know how to make it generate a table. Any clues? Thanks, Amy

Hey again. I think I'm in good shape. I just don't know how to make a table out of the results of the UNION query. I can export it to excel and then import it again, but I'm sure there is a better way.

I used the help function and it explained how to do it for a regular query, but I think this UNION query is a SQL thing, so when I open in design view I do not see an option to make table....

Thanks for all of your help. I am well on my way with my project thanks to you. This final thing would be awesome. Amy

Thanks, that worked perfectly! I'm all set. Have a great day. Amy