HELP! I've tried for hours.. Desperate need of help for modifying tables...

[cindyem]

Hi everyone,

I am running into trouble combining some things into one table.

I have the following table:

Notice that I have several seasons under every customer that makes order. I want to add two more columns to this table so that I can add last year's amount of that particular customer. So, I want the final table to look like the following:

Can you PLEASE tell me how to write a query for this? I have tried hours to do this..

Thanks very much in advance!

 

[boblarson]

What is up with these posts:

Hi people,

Thank you very much!!


( i have to do this so I can post a new thread.. sorry guys for the spam...)

What in the heck do you mean? You don't need to do that in order to post a new thread. You don't need 10 posts to post a new thread. You need 10 posts to be able to modify parts of your profile, to have a signature, to post visitor messages or to use the Private Messaging.

Please do not do that again or you will get the punishment of a spammer - which is immediate and permanent banning.

I have removed all but this thread. (and sorry but I don't have time to try to deal with this question, hopefully someone else will).

Bob Larson
Super Moderator
Access World Forums

 

[John Big Booty]

I think you will need to create two queries on to collect this years info the other to collect the previous years info. You would then combine these in a third query that would bring the two years together in the same row.

 

[John Big Booty]

What is up with these posts:

What in the heck do you mean? You don't need to do that in order to post a new thread. You don't need 10 posts to post a new thread. You need 10 posts to be able to modify parts of your profile, to have a signature, to post visitor messages or to use the Private Messaging.

Please do not do that again or you will get the punishment of a spammer - which is immediate and permanent banning.

I have removed all but this thread. (and sorry but I don't have time to try to deal with this question, hopefully someone else will).

Bob Larson
Super Moderator
Access World Forums

I was somewhat incredulous of that claim myself, and was going to raise it elsewhere, however it would seem you where on top of things

 

[cindyem]

Actually the seasons go all the way down to 2001, so i am stuck on how you would create the second query that you suggested, on creating a query on just last year's info.. Do you know how the second query will look like?

Sorry for the spam earlier i really thought i saw that i needed to post 10 threads to be able to post my first thread, I'm a new member so I don't really know.. But yeah considering i have been stuck on this query problem all day I must have read the error message wrongly when i tred to post this thread the first time. Sorry again.

 

[John Big Booty]

Here's a small sample of how I might approach this task. Check query QRY_TransComp

 

[cindyem]

Thank you so much for your reply!!

I've studied your queries and the result is exactly how i wanted it.. but my order table already has ten thousands of order data inside and each doesn't have an orderID like how you set it up..

Any idea?

Thanks a lot!

 

[vbaInet]

Attach a stripped down version of your db and we'll have a look.

 

[cindyem]

I've attached the file below. Thanks so much in advance!

 

[vbaInet]

It might also be useful to know which tables and/or queries to look at?

 

[John Big Booty]

Have a look at query QRY_CompYear. Note however that this method will only compare departments that have data both years and both seasons.

 

[vbaInet]

I think John might be onto something. In any case, have a look at the attached, qryCurrentAndLast. It will take about 4 to 6 secs to load because your table is not normalized. You should have a Customer table at least with a numeric CustomerID.

 

[cindyem]

The database file only has my order table in it. I am sure all my previous queries are not useful anyway..

I really appreciate your help.

Thanks a lot.

 

[vbaInet]

Glad we could help.

You really need to start thinging about normalizing your data or else you face bigger problems in the future. Here are some good links re this:

http://support.microsoft.com/kb/100139
http://r937.com/relational.html

Happy reading!

 

[cindyem]

Hi guys,
I'm so sorry that I'm very slow at this..
I've tried incorporating both of your queries into my table for the whole day now... but I have errors like: subquery can only return one value... , I then tried to run the subquery on its own but I get not only duplicate values but also some rows with real orders are missing.

What I want to get out of this table is that I have a total of 6,388 rows with orders details. Every customer has different locations and each location has different orders: (snacks, beverages, etc). I want to be able to still track these details. In other words, the result that I want to get in the end is still a table with still 6,388 rows but with the addition of order from the previous year... (SS 2009 paired with SS 2008, etc)

I attached the original table that has not been broken down because that's the one that I am working on, I can't work around it and kept getting errors no matter what..

I really appreciate your generous help.

 

[cindyem]

Hi,

I've made this query :

SELECT [Orders].ACCOUNTTYPE, [Orders].CUSTOMER, [Orders].REGION, [Orders].LocationCode, [Orders].CITY, [Orders].LocationName, [Orders].DEPT, [Orders].BRANDAB, [Orders].SEASON, [Orders].[Book$],

(SELECT O.[Book$] FROM [Orders] as [O] where O.customer=[Orders].customer and O.locationname=[Orders].LocationName and O.region=[Orders].region and O.coll = [Orders].coll and O.dept=[Orders].dept and O.brandab=[Orders].brandab and O.season=left([Orders].season,2)&" "&(right([Orders].season,4)-1)) AS [LYBook$]
FROM [Orders];


Now it's giving me an error : "no current record found".

Please help..

Cindy

 

[John Big Booty]

Is query 2 what you are looking for?

 

[cindyem]

Hi,

I want a query which returns me the same 6,388 rows that I have in the orders table, except that now, I want an additional column showing the order of last year. For example, for SS 2008 I need SS 2007 order amount next to it.

The query that you gave me are like a summary of the orders, but I want to have a table that still looks like my original table with all 6,388 data still there, not summed up..

Do you think this is possible?

Thanks so much!!

 

[John Big Booty]

... your table is not normalized. You should have a Customer table at least with a numeric CustomerID.

Until you normalise your data this is all going to be far more tricky than it need be.

 

[cindyem]

I see..

I'll keep trying, thanks so much.