Build Query Headrs based on Table Cells (1 Viewer)

[Ossama22]

Hello Guys ,
I have a question and i hope to find a solution here ,
iam working to create a database to help my in my work ,
all i need is
i have a lot of companies codes in a table
each company has a specific items for example (Xerox : Basic Salary & Meal Allowance &Incentive)
if i create a table consists of all companies codes as headers and below every code the items which belong to this company ,
is there anyway to generate a separate query with specific items(as a query headers) for every company which i assigned it before based on a choose from text box .. i attach photos to be more clear.

Hope to help me guys as i tried to search for the last 3 hours and it seems that its a hopeless case .

 

[Mark_]

From the posted image, you would be looking for a crosstab query.

 

[pbaldy]

I'm not clear on the goal, but the table is not normalized. I'd expect fields for company and items:

Xerox Basic
Xerox Incentive

Then it's easy to query the items associated with a company. You don't want a field for every company, it would be a nightmare to maintain.

 

[Ossama22]

I'm not clear on the goal, but the table is not normalized. I'd expect fields for company and items:

Xerox Basic
Xerox Incentive

Then it's easy to query the items associated with a company. You don't want a field for every company, it would be a nightmare to maintain.

Iam sorry for not being clear, all i need is a tool to generate a query with items for every company,
For example : when i choose XE, it generates query with salary items i assigned to XE only
Is that possible

 

[Galaxiom]

Iam sorry for not being clear, all i need is a tool to generate a query with items for every company,
For example : when i choose XE, it generates query with salary items i assigned to XE only
Is that possible

That is definitely not "all you need". You have built a spreadsheet in Access.

You need to either restructure the data as a database or put your spreadsheet in Excel. There is no point using Access with data like that.

 

[Minty]

What Paul is saying is that you haven't stored your data correctly, and therefore your task / query is being made very difficult.

You have stored your data as if access was a spreadsheet, not in correctly designed tables.

Whenever you see a field name that has effectively been created as an actual data point, you will have a problem. So as Paul described you should be storing your data "vertically" not "horizontally" , have a read here http://www.niftyaccess.com/normalization-tool/ and the link in my signature.

Edit: Damn those Galaxiom faster fingers....

 

[Ossama22]

What Paul is saying is that you haven't stored your data correctly, and therefore your task / query is being made very difficult.

You have stored your data as if access was a spreadsheet, not in correctly designed tables.

Whenever you see a field name that has effectively been created as an actual data point, you will have a problem. So as Paul described you should be storing your data "vertically" not "horizontally" , have a read here http://www.niftyaccess.com/normalization-tool/ and the link in my signature.

Edit: Damn those Galaxiom faster fingers....

hello , thanks for your answer ,

i bought that tool and iwatched the video , but i think that it wont help me to do what i need . , i think that its a hopeless case ,
iam so beginner in access , if you can guide me how to create what i need using transpose tool plz tell me

 

[Minty]

Post up some sample data as you have it in an excel spreadsheet. I would zip it.
Uncle Gizmo may be able to better steer you through the process as he wrote the guide.

 

[Ossama22]

Post up some sample data as you have it in an excel spreadsheet. I would zip it.
Uncle Gizmo may be able to better steer you through the process as he wrote the guide.

I Create it in both ways , Vertical and horizontal , i know that its kind of annoying but iam seeking your help guys to be able to finalize this issue

 

[Minty]

Please see the attached.

3 Tables - Companies, ExpenseItems, CompanyExpenses.

You can easily add more companies and more expenses without redesigning anything.

Your query becomes a simple cross tab.

 

[Uncle Gizmo]

I think Minty has hit the Nail on the head! ... If that's the way you want to go with it, then I can do a demo video showing you how to get there. Otherwise (and I think you should probably do this) you need to give a good explanation of what you are doing.

 

[Uncle Gizmo]

Re-reading the thread, I think it's worth emphasising what we've identified between us as the issue. In essence you have suffered the Fate of most people moving from Excel into Access, in that what appears like a totally acceptable way to proceed and that is to copy your Excel data straight into Access, (it seems the right thing to do). However it is a significant trap which can cause you a lot of grief if you don't catch it early enough. It looks like you're in the early stages, and if you follow the advice on how to bring your data into Microsoft Access then you should be able to move on without losing too much ground.

I have blogged about the issue here:- http://www.niftyaccess.com/excel-in-access/

The other thing I recommend is search on "Normalisation", which is the technical term for restructuring your data to make it suitable for the MS Access. As I explain in my Blog, you can continue with the structure you have, but the difficulties in manipulating and extracting data will just multiply at an exponential rate!

 

[Ossama22]

Please see the attached.

3 Tables - Companies, ExpenseItems, CompanyExpenses.

You can easily add more companies and more expenses without redesigning anything.

Your query becomes a simple cross tab.

i Wanna to thank you for ur great efforts with me ,
Thx

 

[Ossama22]

I think Minty has hit the Nail on the head! ... If that's the way you want to go with it, then I can do a demo video showing you how to get there. Otherwise (and I think you should probably do this) you need to give a good explanation of what you are doing.

Thanks Uncle for your support and collaboration , it will be a great favor if you create that video ,

Thanks again bro

 

[Uncle Gizmo]

It will be a great favour if you create that video

I'll get to it this evening...

 

[Uncle Gizmo]

I'll get to it this evening...

Well it's taking me a bit longer than I thought it would to get around to doing this. Here's the first video:-

Build Query Heads - Nifty Access
https://youtu.be/0w_uqBpA9gw

Which explains the question and the reason for taking the original data and normalising it to make it suitable for use in MS Access. I think this was an excellent example because, for a start it wasn't clear (To Me) how to normalise the data, in fact I was struggling a bit with that until Minty came up with the solution, so thank you Minty!

Unfortunately the next video is probably the one you really want, and I wont be able to get to that one till this evening! There is a video which demonstrates using the normalisation tool on my website here:-

http://www.niftyaccess.com/normalization-tool/

Unfortunately, due to the nature of the original data it's not quite obvious how to go about the transposition of the data. So it might be better to wait for the next video.

 

[mike60smart]

Hi

Can you upload a zipped copy of the Database?

 

[Uncle Gizmo]

Hi

Can you upload a zipped copy of the Database?

See Attached:-

I think I've got the cross-tab Query about right, but it definitely needs an explanation!

 

[Uncle Gizmo]

In this video I demonstrate how to normalise some data imported from Excel. This consists of breaking it up into 3 separate tables. I then demonstrate how this normalised data can be drawn back together again into a useful and usable table. I then demonstrate how to use a cross-tab query to produce a particular output required.

Build Query Heads 2 - Nifty Access
https://youtu.be/y-EQ2JgLki8

The form "frmTranspose" (shown in the video) is available from my website here along with further information on its use. It is very handy for turning denormalized data into normalised data for use in MS Access.

Normalisation tool in Action